# Count And Large In One Fuction

Jul 14, 2009i'd like to get larget repeated number in one cell

not to make column for count and then take largest number, i need this in one cell only

i'd like to get larget repeated number in one cell

not to make column for count and then take largest number, i need this in one cell only

ADVERTISEMENT

I am getting the "#NAME?" in the sell containing the formula.

=IF(AND(B5="standard",B6="standard",B7="standard"),"standard",IF(AND(B5="Level 1",B6="Level 1",B7="Level 1"),"Level 1",IF(AND(B5="Level 2",B6="Level 2",B7="Level 2"),"Level 2",IF(AND(B5="Level 3",B6="Level 3",B7="Level 3"), "Level 3", IF(AND(B5="Level 4",B6="Level 4",B7="Level 4"),"Level 4", Custom)))))

Cell B5 contains "standard"

Cell B6 contains "Level 1"

Cell B7 contains "standard"

Cell B8 contains "standard"

I am trying to get the following macro to run every half hour from 9am until 4pm.

I did try entering the following statement within the macro but I get the error message "compile error - arguement not optional"

Application.OnTime

TimeValue ("16:31:00"), "Mail_Selection_Range_Outlook_Body"

So here is my full macro. Thanks in advance for the help. (Using excel 2003)

Sub Mail_Selection_Range_Outlook_Body()

Dim rng As Range

Dim OutApp As Object

Dim OutMail As Object

Set rng = Nothing

On Error Resume Next

'Only the visible cells in the selection

Set rng = Sheets("Sheet1").Range("A1:C41").SpecialCells(xlCellTypeVisible)

'You can also use a range if you want

'Set rng = Sheets("YourSheet").Range("D4:D12").SpecialCells(xlCellTypeVisible)

On Error GoTo 0

If rng Is Nothing Then

MsgBox "The selection is not a range or the sheet is protected" & _

vbNewLine & "please correct and try again.", vbOKOnly

Exit Sub

If I have the numbers 1-50 repeating in columns, 1-50, 1-50 etc is there a way to determine that number "1" falls 1st, 51st etc in the sequence without doing a manual count? Or that the number 2 falls 2nd, 52nd etc?

I have a sumproduct function picking up data from a different worksheet. Because i have to much data it takes too much memory and it doesnt run that smoothly. Therefore i have hardcoded the function and tried to evaluate it using the EVAL() function from the morefunc add-in. However i don't get it to work. It continuously returns #VALUE.

This is the code i am trying to evaluate

SUMPRODUCT(--('Retrieve Depletions'!$A$4:$A$5000=$D4),--('Retrieve Depletions'!$B$4:$B$5000=$E4),--('Retrieve Depletions'!$C$4:$C$5000=$F4),--('Retrieve Depletions'!$D$4:$D$5000=$G4),--('Retrieve Depletions'!$E$4:$E$5000=$I4),--('Retrieve Depletions'!$F$4:$F$5000=$J4),'Retrieve Depletions'!H$4:H$5000)

When I use Excel's UI to create a function, the fn is being called several times. If I enter manually there is no problem.

Add this code in any module and goto menu, Insert, Function and then select

dosomething from the list and click OK. you will the msgbox is being displayed several times.

Public Function dosomething(aa As String, bb As String, cc As String, dd As String, ee As String, ff As String, gg As String, hh As String)

MsgBox "a"

End Function

I am trying to use the Left function in my VBA code but continue to receive run time error 1004 - Method 'range' of object '_global' failed. Not sure if the error originates for code being written incorrectly or if the left function is not available in VBA. I am basically trying to take the first 9-characters for each cell in column K and copy the values to column G.

View 3 Replies View RelatedI'm trying to count how many production orders i have per week. However, there are duplicated production orders per week. I only want to count how many unique orders there are for each week. I only see the ability to "Count", which counts my duplicates as well so it over inflates my true quantity.

View 3 Replies View RelatedHow do I enter a formula in excel 2010 that will give the total amount of times each number is in this group. Example : how many times (total) the number 12 showed up , how many times the number 27 showed, and so on for each number that is in the entire group of numbers, from 1 to 80 .

Here is the page I will copy and paste into a workbook sheet from the internet that i want to evaluate the times each number was called.

Very new to all this , I am a bit aged and need not to learn excel A to Z, just need to know what correct statements /formulas have to be entered to do what i desire.

I am only interested in the total count of the small bold numbers 1 to 80, each single digit 1 to 9 and double digits 10 to 80 will be in their separate cells. The large bold three digit numbers and dates/times will have to be erased (manually) before the calculation takes place.

298

2/9/2014 12:28:02 PM

37

77

[Code]....

I've got a 80 Mb CSV file and would like to open and work with it. Too many lines (90000 or so).

Is there a way to split ( ) this file so I can open two files instead?

I have 5 sheets all with different data within the H column. I want to find the top 10 highest results across all 5 sheets and then pull the worksheet name where those results come from. It would be two separate formulas, one for the LARGE value and the second for the name.

This is all I have.

O2:O11

=LARGE(Sheet1:Sheet5!$H$3:$H$102,1)

to

=LARGE(Sheet1:Sheet5!$H$3:$H$102,10)

I then have this which was the best I could find after about 30 different formulas.

=MATCH(TRUE,COUNTIF(INDIRECT("'"&{"Sheet1","Sheet2","Sheet3","Sheet4","Sheet5"}&"'!$H$3:$H$102"),O2)>0,0)

It gives me the right worksheet but it only gives me a number. I'm assuming it's because of COUNTIF but I'm not sure what else to use.

Most examples I see have the names they want right beside the values. So in my case H3 is 22 (and the largest value within H across all sheets) and if I followed their examples G3 would be Sheet1. So because it's side by side it'd be easily pulled. But the only name references I have are the worksheet name itself or A1 of Sheet1:Sheet5 so I'm not sure how to rework their formulas to suit mine.

I need to create a "Top 5" list based on two columns. The example is as attached (the real list is approx 22 000 lines):

I need to find the top 5 largest quantites (column A) for each flag catagory (column D) and include the part number and description. I have tried a pivot table and had some issues. Also tried a LARGE(IF...) type formula and still no luck. Does anyone have any ideas of what is the best way to do this?

I'm trying to create a mini-table that will give me the 3 highest and lowest values in a range (I know how to do this using LARGE and SMALL functions), but I also want to get the corresponding name (in column A) for each number. This last part I don't know how to do.

I've attached an example sheet that I hope gives a basic idea of what I'm trying to do. I'm looking for a method to fill in the data for cells A13:A15 and C13:C15 in the attached sheet.

I could modify my worksheet a bit and probably get the result I'm looking for, but I'm hoping to come up with a nicer solution, and hopefully learn Excel a bit better in the process.

I don't know if this is possible but thought i'd check. Could I put together a look up that pulls through the 5 best performing categories' so say i have 200 categories, just to pull on to a report page the top 5 and their result, and produce a report weekly so if the top 5 change it updates this?

View 2 Replies View RelatedI am having a problem with a workbook, I have copied all cells and pasted Special (Values only). But for some reason the workbook is 38 Meg's. The workbook only contains 6 worksheets with about 60 rows and 32 columns with figures and no formulas. I have tried to find formulas by Goto/Special/Formulas and Excel has found nothing. What shoud I do?

View 4 Replies View RelatedI was doing this task using sorting then adding column today morning one of my friend told i can do it with formula only, no need to using sort and adding new column

I just attached the sheet : without sorting.xlsx

I have a set of 5,800+ data points between 0 and 1 that I would like to multiply together. When I use PRODUCT for the whole set, the formula returns 0. However, I can use a smaller subset of the data to return a very small number. I'm curious if Excel has a closest-number-to-0 or number-of-cells-for-PRODUCT limitation. Is there another way to perform this calculation?

View 4 Replies View RelatedSay I wanted to concatenate a row that had 10,000 values in it. How could this be achieved without having to click 10,000 cells while typing a comma in between each one?

View 5 Replies View Relatedi have this formula below which works but if the criteria is not there it says FALSE because of the B22:C45,2,0) bit i would rather it says something like "no dissertation" is that possible? ...

View 10 Replies View RelatedI have a column of numbers in A1:A200 that I need to concatenate with a ";" in between each into one cell. Is there a way to do this without clicking in each cell individually? =concatenate(A1:A200) just returns the value in cell a1.

View 2 Replies View RelatedI'm having a difficulty using LARGE and SMALL.I want to return the 1st to 5th value of an array (each in a different row) choosing months as criteria. While the LARGE formula works fine, when I replace the LARGE() formula for the SMALL() leaving everything else unchanged, the formula returns ZERO and there are no ZEROS in the original data! Here is the function:

View 4 Replies View Relatedmy colleague did something to one of our files and it is now 845918 kb. I transferred it to a flash drive and then to my hard drive but now I cant open it to see what is wrong. Is there a trick to opening this up? When it was opened on her computer it wouldn't let me add any tabs or move tabs. I think I unprotected it but that didn't change anything.

View 1 Replies View RelatedI have a list of domains (about 200,000) that looks like this:

I have a list of urls (about 1 million) that looks like this:

I want my results to only show this (since this is the only thing in the second file that does not contain anything from the first file):

I have tried programs designed to do this but they either (a) shorten everything down to the domain or (b) wont remove anything since the domains are part of a longer url in the second file.

I am using following event macro and it repeats itself more than 300 times. I am at a stage where I am getting a message "Compile Error, Procedure too large", I tried to break it into two but that's not working.

Option Explicit

Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cl As Range

Dim lng As Long

Application.EnableEvents = False

Select Case Target.Address

Case "$E$3"

If Target.Value = "Yes" Then

SelectNumber:

lng = Application.InputBox("Please enter number 0 to 100", , , , , , , 1)

If IsNumeric(lng) = False Or lng < 0 Or lng > 100 Then GoTo SelectNumber

Range("G3") = lng

Else: Range("G3") = 0

.................................................(above statements repeat more than 300 times for different cells)..............................

Application.EnableEvents = True

End Sub

I'm using Excel 2007 and the sheet has 238536 rows containing 26412 ranges.

The first 5 rows in each range contains data. I wish to input into the sixth cell of every range a formula. Not essential but I'd prefer it to be formatted blue.

I attach short s/s to illustrate my need.

I am building a database in excel where I have the number of days across the top (horizontal) and in Cell A2:A300 I list our product lines (that is repeated based on different divisions). Beside each product line in row b3:iv3 I have sales data for each working day.

In a new tab I want to formulate the total of ProductA on day 240.

A B C D E F G H

1 Region Product 235 236 237 238 239 240

2 RegionA ProductA 25 31 15 18 10 5

3 RegionB ProductA 21 13 10 5 28 10

4 RegionB ProductB 21 13 10 5 28 10

5 RegionC ProductB 21 13 10 5 28 10

6 RegionA ProductA 25 31 15 18 10 5

7 RegionA ProductC 25 31 15 18 10 5

In another Tab summary of Product Totals:

Day235 Day236 Day237

Product A 76 75 40

Product B 42 26 20

Product C 25 31 15

I tried many formulas for example:

=SUMIF('2012 Sales'!$C$3:$IS$113,$C1&$F$4)

i was wondering if there was a way i can combine these two formulas into one large one

=IF(AF8="","",INDEX($A$3:$A$102,AGGREGATE(15,6,(ROW($L$3:$L$103)-ROW($L$3)+1)/($L$3:$L$103=AF8),COUNTIF($AF$8:AF8,AF8))))

=IF(AG8="","",INDEX($A$3:$A$102,AGGREGATE(15,6,(ROW($M$3:$M$103)-ROW($M$3)+1)/($M$3:$M$103=AG8),COUNTIF($AG$8:AG8,AG8))))

I have a list of IDs that recurs over a time period. It consists of a Start Data an ID Number. These IDs recur over and over again through one month with different start dates.

See Below:

Date ID

12/1/2013 10:00:00 AM 67890

12/6/2013 12:00:30 PM 67890

12/18/2013 06:30:05 AM 67890

From Another List I'd like to pull a max enddate that is within 24 hrs of the start date. There will be multiple end dates. Here is what the other list would look like.

End Date ID

12/1/2013 1:00:30PM 67890

12/6/2013 4:00:45PM 67890

12/18/2013 9:30:00 AM 67890

Seems like using vlookup with the ID as the lookup wouldnt work because it would just pull the first date it found over and over again.

I am trying to copy part of one line of pasted information in to a certain place on the line below.

i.e I want

ABCabc

to look like

ABC

abc

This needs to happen for a large number of lines. I have already added a space in between all the original lines.

I have a database of 75000 rows in text format which has to be converted to excel, but the limitation in excel is only 65536. hence, I want to a macro that will copy the 1st 65536 rows into the 1st sheet in excel. The remaining rows should be copied into the second sheet. I know that it can be done with access. but i dont want to use access.

I have got a code to do this, but it takes much too long. if the code could be modified to make it faster,

Sub LargeFileImport()

'Dimension Variables

Dim ResultStr As String

Dim FileName As String

Dim FileNum As Integer

Dim Counter As Double

'Ask User for File's Name

FileName = InputBox("Please enter the Text File's name, e.g.

Copyrights 2005-15 www.BigResource.com, All rights reserved