Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique (30000 rows) using criteria - Please help!
In Sheet1 I have:
ColE ColB 1/1/08 00113800 1/1/08 02559000 1/2/08 00113800 1/2/08 02559000 1/2/08 02559000 1/3/08 00113800 1/3/08 02559000 In Sheet2 I Need: ColA ColB 1/1/08 2 1/2/08 2 1/3/08 2 How can I count unique values on one sheet using criteria from another? Also, this needs to work on 30,000 rows on Sheet1. I tried a few solutions from what I found on the newsgroups (using arrays) and it locked up Excel. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique (30000 rows) using criteria - Please help!
If you sort sheet1 with Column E as 1st key and Column B as 2nd key then run
code below it will create the output you are looking for. Sub getsummary() OldDate = "" OldRowCount = 1 NewRowCount = 1 Unique = 0 With Sheets("Sheet1") Do While .Range("E" & OldRowCount) < "" CurrentDate = .Range("E" & OldRowCount) Num = .Range("B" & OldRowCount) If CurrentDate = OldDate Then If Num < OldNum Then Unique = Unique + 1 OldNum = Num End If Else Unique = 1 OldDate = CurrentDate OldNum = Num End If NewDate = .Range("E" & (OldRowCount + 1)) If CurrentDate < NewDate Then With Sheets("Sheet2") .Range("A" & NewRowCount) = OldDate .Range("B" & NewRowCount) = Unique NewRowCount = NewRowCount + 1 End With End If OldRowCount = OldRowCount + 1 Loop End With "Robert_L_Ross" wrote: In Sheet1 I have: ColE ColB 1/1/08 00113800 1/1/08 02559000 1/2/08 00113800 1/2/08 02559000 1/2/08 02559000 1/3/08 00113800 1/3/08 02559000 In Sheet2 I Need: ColA ColB 1/1/08 2 1/2/08 2 1/3/08 2 How can I count unique values on one sheet using criteria from another? Also, this needs to work on 30,000 rows on Sheet1. I tried a few solutions from what I found on the newsgroups (using arrays) and it locked up Excel. Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique (30000 rows) using criteria - Please help!
I don't need the dates on Sheet 2 created, I need it to use the date it finds
on Sheet 2 as the criteria for the count of column B on Sheet 1. And I need to try to keep it a function, not VB code (our IT group hates us using VB - they don't support it). "Joel" wrote: If you sort sheet1 with Column E as 1st key and Column B as 2nd key then run code below it will create the output you are looking for. Sub getsummary() OldDate = "" OldRowCount = 1 NewRowCount = 1 Unique = 0 With Sheets("Sheet1") Do While .Range("E" & OldRowCount) < "" CurrentDate = .Range("E" & OldRowCount) Num = .Range("B" & OldRowCount) If CurrentDate = OldDate Then If Num < OldNum Then Unique = Unique + 1 OldNum = Num End If Else Unique = 1 OldDate = CurrentDate OldNum = Num End If NewDate = .Range("E" & (OldRowCount + 1)) If CurrentDate < NewDate Then With Sheets("Sheet2") .Range("A" & NewRowCount) = OldDate .Range("B" & NewRowCount) = Unique NewRowCount = NewRowCount + 1 End With End If OldRowCount = OldRowCount + 1 Loop End With "Robert_L_Ross" wrote: In Sheet1 I have: ColE ColB 1/1/08 00113800 1/1/08 02559000 1/2/08 00113800 1/2/08 02559000 1/2/08 02559000 1/3/08 00113800 1/3/08 02559000 In Sheet2 I Need: ColA ColB 1/1/08 2 1/2/08 2 1/3/08 2 How can I count unique values on one sheet using criteria from another? Also, this needs to work on 30,000 rows on Sheet1. I tried a few solutions from what I found on the newsgroups (using arrays) and it locked up Excel. Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique (30000 rows) using criteria - Please help!
With 30k rows of data just about any formula using built-in functions will
be slow to calculate. If you'd rather not use a macro are you open to using an add-in? -- Biff Microsoft Excel MVP "Robert_L_Ross" wrote in message ... I don't need the dates on Sheet 2 created, I need it to use the date it finds on Sheet 2 as the criteria for the count of column B on Sheet 1. And I need to try to keep it a function, not VB code (our IT group hates us using VB - they don't support it). "Joel" wrote: If you sort sheet1 with Column E as 1st key and Column B as 2nd key then run code below it will create the output you are looking for. Sub getsummary() OldDate = "" OldRowCount = 1 NewRowCount = 1 Unique = 0 With Sheets("Sheet1") Do While .Range("E" & OldRowCount) < "" CurrentDate = .Range("E" & OldRowCount) Num = .Range("B" & OldRowCount) If CurrentDate = OldDate Then If Num < OldNum Then Unique = Unique + 1 OldNum = Num End If Else Unique = 1 OldDate = CurrentDate OldNum = Num End If NewDate = .Range("E" & (OldRowCount + 1)) If CurrentDate < NewDate Then With Sheets("Sheet2") .Range("A" & NewRowCount) = OldDate .Range("B" & NewRowCount) = Unique NewRowCount = NewRowCount + 1 End With End If OldRowCount = OldRowCount + 1 Loop End With "Robert_L_Ross" wrote: In Sheet1 I have: ColE ColB 1/1/08 00113800 1/1/08 02559000 1/2/08 00113800 1/2/08 02559000 1/2/08 02559000 1/3/08 00113800 1/3/08 02559000 In Sheet2 I Need: ColA ColB 1/1/08 2 1/2/08 2 1/3/08 2 How can I count unique values on one sheet using criteria from another? Also, this needs to work on 30,000 rows on Sheet1. I tried a few solutions from what I found on the newsgroups (using arrays) and it locked up Excel. Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique (30000 rows) using criteria - Please help!
Robert_L_Ross wrote...
In Sheet1 I have: ColE * * *ColB 1/1/08 * 00113800 1/1/08 * 02559000 1/2/08 * 00113800 1/2/08 * 02559000 1/2/08 * 02559000 1/3/08 * 00113800 1/3/08 * 02559000 If your source data above is sorted on col E, then the most efficient way to handle this uses additional columns of formulas in Sheet1. If columns X, Y and Z are available, your first row of data is 2 and your last is 30001, enter the following formulas. X30002: =ROW() Y30002: =X30002 X2: =IF(E2<E1,ROW(),"") Y2: =IF(N(X2),IF(N(X3),X3,Y3)-1,IF(N(X3),X3,Y3)) Z2: =IF(E2<E1,SUMPRODUCT(1/COUNTIF(INDEX(B:B,X2):INDEX(B:B,Y2),INDEX (B:B,X2):INDEX(B:B,Y2))),"") Fill X2:Z2 down into X3:Z30001. In Sheet2 I Need: ColA * * *ColB 1/1/08 * 2 1/2/08 * 2 1/3/08 * 2 .... From your follow-up post it seems col A in Sheet2 is already filled. If so, the B2 formula for the date in A2 is given by the formula B2: =VLOOKUP(A2,Sheet1!E:Z,22,0) This is the most recalc efficient way to do this I've found. It'll still be quite slow with 30K records. If you need to do this a lot, you need to show your IT people this mess of formulas that you'd need to use and tell them that if they provided you a decent SQL database (so NOT Access) to use, this could be done simply as SELECT ColE, COUNT(DISTINCT ColB) FROM Table GROUP BY ColE; IOW, if they refuse to give you the best tool for the task, they should have to support what you have to hack together. They should welcome a decent VBA alternative to the mess of formulas above, but if not, make it very clear that THEY get to maintain all these formulas when you move on to greener pastures. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique (30000 rows) using criteria - Please help!
I'd suggest a pivot table on sheet 2. Set it up with dates in the rows and
use the "count" of date in the data field. I did 25,000 records in a few seconds. Good luck Mike "Harlan Grove" wrote: Robert_L_Ross wrote... In Sheet1 I have: ColE ColB 1/1/08 00113800 1/1/08 02559000 1/2/08 00113800 1/2/08 02559000 1/2/08 02559000 1/3/08 00113800 1/3/08 02559000 If your source data above is sorted on col E, then the most efficient way to handle this uses additional columns of formulas in Sheet1. If columns X, Y and Z are available, your first row of data is 2 and your last is 30001, enter the following formulas. X30002: =ROW() Y30002: =X30002 X2: =IF(E2<E1,ROW(),"") Y2: =IF(N(X2),IF(N(X3),X3,Y3)-1,IF(N(X3),X3,Y3)) Z2: =IF(E2<E1,SUMPRODUCT(1/COUNTIF(INDEX(B:B,X2):INDEX(B:B,Y2),INDEX (B:B,X2):INDEX(B:B,Y2))),"") Fill X2:Z2 down into X3:Z30001. In Sheet2 I Need: ColA ColB 1/1/08 2 1/2/08 2 1/3/08 2 .... From your follow-up post it seems col A in Sheet2 is already filled. If so, the B2 formula for the date in A2 is given by the formula B2: =VLOOKUP(A2,Sheet1!E:Z,22,0) This is the most recalc efficient way to do this I've found. It'll still be quite slow with 30K records. If you need to do this a lot, you need to show your IT people this mess of formulas that you'd need to use and tell them that if they provided you a decent SQL database (so NOT Access) to use, this could be done simply as SELECT ColE, COUNT(DISTINCT ColB) FROM Table GROUP BY ColE; IOW, if they refuse to give you the best tool for the task, they should have to support what you have to hack together. They should welcome a decent VBA alternative to the mess of formulas above, but if not, make it very clear that THEY get to maintain all these formulas when you move on to greener pastures. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique (30000 rows) using criteria - Please help!
mike in texas wrote...
I'd suggest a pivot table on sheet 2. Set it up with dates in the rows and use the "count" of date in the data field. .... Perhaps reading the OP's specs carefully would help. Robert_L_Ross wrote... In Sheet1 I have: ColE * * *ColB 1/1/08 * 00113800 1/1/08 * 02559000 1/2/08 * 00113800 1/2/08 * 02559000 1/2/08 * 02559000 1/3/08 * 00113800 1/3/08 * 02559000 .... In Sheet2 I Need: ColA * * *ColB 1/1/08 * 2 1/2/08 * 2 1/3/08 * 2 .... Create a Pivot Table from the data in Sheet1 as you propose and the result would be 1/1/08 2 1/2/08 3 1/3/08 2 Note the difference: Pivot Table would return 3 for the 1/2/08 date while the OP wants 2. If you had read the OP's specs carefully (or even the subject line), you might have noticed the bit about counting UNIQUE (meaning counting distinct). There are only 2 distinct col B values corresponding to the col E value 1/2/08 in the OP's original data. How would you use a Pivot Table to return the DISTINCT count? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique (30000 rows) using criteria - Please help!
Harlan,
In an off way it would work if I chose to display the ColB results in the Pivot Table...so I'd provide the date as the first column in the pivot table, then the Lender ID (ColB) results...the pivot table would then be able to provide a count of unique Lender ID results. The only problem with using pivot tables is that the result I need to use as a source for another sheet...a Pivot Table will vary wildly in the total number of lines I get. I'm wondering can you use a Pivot Table as a source for a VLOOKUP and have the Pivot Table act like a range or a named range? If it can, would a VLOOKUP accept a named range instead of a 'hard' range? =vlookup(a1,PivotTable1,2,false) instead of =vlookup(a1,b1:b30000,2,false) "Harlan Grove" wrote: mike in texas wrote... I'd suggest a pivot table on sheet 2. Set it up with dates in the rows and use the "count" of date in the data field. .... Perhaps reading the OP's specs carefully would help. Robert_L_Ross wrote... In Sheet1 I have: ColE ColB 1/1/08 00113800 1/1/08 02559000 1/2/08 00113800 1/2/08 02559000 1/2/08 02559000 1/3/08 00113800 1/3/08 02559000 .... In Sheet2 I Need: ColA ColB 1/1/08 2 1/2/08 2 1/3/08 2 .... Create a Pivot Table from the data in Sheet1 as you propose and the result would be 1/1/08 2 1/2/08 3 1/3/08 2 Note the difference: Pivot Table would return 3 for the 1/2/08 date while the OP wants 2. If you had read the OP's specs carefully (or even the subject line), you might have noticed the bit about counting UNIQUE (meaning counting distinct). There are only 2 distinct col B values corresponding to the col E value 1/2/08 in the OP's original data. How would you use a Pivot Table to return the DISTINCT count? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique (30000 rows) using criteria - Please help!
Robert_L_Ross wrote...
In an off way it would work if I chose to display the ColB results in the Pivot Table...so I'd provide the date as the first column in the pivot table, then the Lender ID (ColB) results...the pivot table would then be able to provide a count of unique Lender ID results. .... So where did the OP ask for counts of each distinct col B value rather than the count of distinct col B values? Why is a 2-step solution with a pivot table as the first step OK if the second step is left unmentioned? Pivot tables are answering a different question than the one the OP asked. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique (30000 rows) using criteria - Please help!
"Perhaps reading the OP's specs carefully would help."
Actually Harlan, if YOU read the OP closely, you would see that the actual question was: "How can I count unique values on one sheet using criteria from another?" On the OP I didn't specify I needed it done in one step. "Note the difference: Pivot Table would return 3 for the 1/2/08 date while the OP wants 2. If you had read the OP's specs carefully (or even the subject line), you might have noticed the bit about counting UNIQUE (meaning counting distinct). There are only 2 distinct col B values corresponding to the col E value 1/2/08 in the OP's original data. How would you use a Pivot Table to return the DISTINCT count?" Maybe you don't use Pivot Tables that much, but yes, the VALUE could return 3, but if you include Lender ID as part of the display, then it returns 2 per lender ID. THAT'S the answer to "How would you use a Pivot Table to return the DISTINCT count?" Furthermore, you are claiming that building the Pivot Table is one step and getting the Unique count is another step. If you had actually walked that answer through, you would know that you can ask for the unique values to be returned AS YOU BUILD THE PIVOT TABLE, so it's not 2 steps, it's the same step. You are tearing Mike's answer down because you don't agree with it, not because it wouldn't give me the result I want. Mike's answer does give me the result I want, but since it's in a Pivot Table it may not work for me. Before you tear someone's answer down, think it through - just because it's not your method doesn't mean it's not a valid way of doing something. "Harlan Grove" wrote: Robert_L_Ross wrote... In an off way it would work if I chose to display the ColB results in the Pivot Table...so I'd provide the date as the first column in the pivot table, then the Lender ID (ColB) results...the pivot table would then be able to provide a count of unique Lender ID results. .... So where did the OP ask for counts of each distinct col B value rather than the count of distinct col B values? Why is a 2-step solution with a pivot table as the first step OK if the second step is left unmentioned? Pivot tables are answering a different question than the one the OP asked. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique (30000 rows) using criteria - Please help!
Robert_L_Ross wrote...
. . . if YOU read the OP closely, you would see that the actual question was: "How can I count unique values on one sheet using criteria from another?" The fact that multiple worksheets may be involved is irrelevant for formula-based solutions. The only difference between source data and results in the same or different worksheets would be unnecessary vs necessary, respectively, inclusion of the worksheet name in range references. So the original question boils down to how to count unique values, which I interpreted to mean distinct values. On the OP I didn't specify I needed it done in one step. Fair enough. Would you like a solution that takes 100 steps? Maybe you don't use Pivot Tables that much, but yes, the VALUE could return 3, but if you include Lender ID as part of the display, then it returns 2 per lender ID. *THAT'S the answer to "How would you use a Pivot Table to return the DISTINCT count?" Include Col B as another row variable? You get the following result. Count of ID Date ID Total 01/01/2008 *00113800 1 *02559000 1 01/01/2008 Total 2 01/02/2008 *00113800 1 *02559000 2 01/02/2008 Total 3 01/03/2008 *00113800 1 *02559000 1 01/03/2008 Total 2 Where do you get the distinct count without additional formulas applied to the pivot table? And how complicated would those formulas be? Include col B as a column variable, and you get the following result. Count of ID ID Date *00113800 *02559000 01/01/2008 1 1 01/02/2008 1 2 01/03/2008 1 1 Maybe getting closer. This would only require counting nonblank columns corresponding to each date, but if there were 30K rows in the source data, maybe there could be more than 255 distinct col B values, in which case this could fubar in Excel 2003 and prior. Perhaps you mean some other layout I'm not figuring out. Furthermore, you are claiming that building the Pivot Table is one step and getting the Unique count is another step. *If you had actually walked that answer through, you would know that you can ask for the unique values to be returned AS YOU BUILD THE PIVOT TABLE, so it's not 2 steps, it's the same step. Leading to 2 questions. First, where do you specify unique/distinct values in the Pivot Table settings? Second, if this is so straightforward, why'd you start this thread? You are tearing Mike's answer down because you don't agree with it, not because it wouldn't give me the result I want. . . . .... Mike's response was incomplete. He didn't include the col B values. That's an immaterial omission? |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique (30000 rows) using criteria - Please help!
You are obviously not wanting to listen to what I'm trying to say, so I'm not
going to continue to clog the thread up arguing with you. "Harlan Grove" wrote: Robert_L_Ross wrote... . . . if YOU read the OP closely, you would see that the actual question was: "How can I count unique values on one sheet using criteria from another?" The fact that multiple worksheets may be involved is irrelevant for formula-based solutions. The only difference between source data and results in the same or different worksheets would be unnecessary vs necessary, respectively, inclusion of the worksheet name in range references. So the original question boils down to how to count unique values, which I interpreted to mean distinct values. On the OP I didn't specify I needed it done in one step. Fair enough. Would you like a solution that takes 100 steps? Maybe you don't use Pivot Tables that much, but yes, the VALUE could return 3, but if you include Lender ID as part of the display, then it returns 2 per lender ID. THAT'S the answer to "How would you use a Pivot Table to return the DISTINCT count?" Include Col B as another row variable? You get the following result. Count of ID Date ID Total 01/01/2008 00113800 1 02559000 1 01/01/2008 Total 2 01/02/2008 00113800 1 02559000 2 01/02/2008 Total 3 01/03/2008 00113800 1 02559000 1 01/03/2008 Total 2 Where do you get the distinct count without additional formulas applied to the pivot table? And how complicated would those formulas be? Include col B as a column variable, and you get the following result. Count of ID ID Date 00113800 02559000 01/01/2008 1 1 01/02/2008 1 2 01/03/2008 1 1 Maybe getting closer. This would only require counting nonblank columns corresponding to each date, but if there were 30K rows in the source data, maybe there could be more than 255 distinct col B values, in which case this could fubar in Excel 2003 and prior. Perhaps you mean some other layout I'm not figuring out. Furthermore, you are claiming that building the Pivot Table is one step and getting the Unique count is another step. If you had actually walked that answer through, you would know that you can ask for the unique values to be returned AS YOU BUILD THE PIVOT TABLE, so it's not 2 steps, it's the same step. Leading to 2 questions. First, where do you specify unique/distinct values in the Pivot Table settings? Second, if this is so straightforward, why'd you start this thread? You are tearing Mike's answer down because you don't agree with it, not because it wouldn't give me the result I want. . . . .... Mike's response was incomplete. He didn't include the col B values. That's an immaterial omission? |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique (30000 rows) using criteria - Please help!
Robert_L_Ross wrote...
You are obviously not wanting to listen to what I'm trying to say, so I'm not going to continue to clog the thread up arguing with you. .... Translation: you found out there's no direct way to count DISTINCT text values from a text field using a pivot table, so rather than admit you've been wrong in your last few follow-ups you'll feign a high minded disappointment. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique (30000 rows) using criteria - Please help!
Harlan Grove wrote:
Robert_L_Ross wrote... You are obviously not wanting to listen to what I'm trying to say, so I'm not going to continue to clog the thread up arguing with you. ... Translation: you found out there's no direct way to count DISTINCT text values from a text field using a pivot table, so rather than admit you've been wrong in your last few follow-ups you'll feign a high minded disappointment. Also, there are a number of people who like to point out when I'm wrong. Count the number of people other than yourself who are taking issue with what I've stated in this thread. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Unique Values with a Criteria | Excel Worksheet Functions | |||
Count unique numbers in a range with a given criteria | Excel Discussion (Misc queries) | |||
count unique with mulitple criteria | Excel Worksheet Functions | |||
Count Unique Values with Multiple Criteria | Excel Worksheet Functions | |||
how to count unique values in excel based on criteria | Excel Worksheet Functions |