Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count unique numbers
Doing a pivot table with a large amount of data. We have trip numbers
throughout the year that do unfortunately get repeated so in order to make them unique, I concatenated the trip number with the delivery date/100000 so that all my trips look similar to this: 5243.954. For one trip there can be mulitple drops and the report I have to work off (one line per drop) I have this repeat for as many drops as there are. Now I would like to count the number of trips but no matter what I do, it counts the lot (total drops and not total trips) As an example, given the following trip numbers 1.278 1.254 1.254 1.847 This should give total trips = 3 and total drops = 4. That's the intended ie my spreadsheet has over 300,000 lines (drops) but only about 60,000 of those are trips but it still tells me 300,000. Can anyone help me? I am using Excel 2007 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count unique numbers
You can have a helper column and put in =Round(trip number,1). this will
remove the added info at the end. Pivot on the hekper column instead... "markmcd" wrote: Doing a pivot table with a large amount of data. We have trip numbers throughout the year that do unfortunately get repeated so in order to make them unique, I concatenated the trip number with the delivery date/100000 so that all my trips look similar to this: 5243.954. For one trip there can be mulitple drops and the report I have to work off (one line per drop) I have this repeat for as many drops as there are. Now I would like to count the number of trips but no matter what I do, it counts the lot (total drops and not total trips) As an example, given the following trip numbers 1.278 1.254 1.254 1.847 This should give total trips = 3 and total drops = 4. That's the intended ie my spreadsheet has over 300,000 lines (drops) but only about 60,000 of those are trips but it still tells me 300,000. Can anyone help me? I am using Excel 2007 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count unique numbers
Add a helper column and put this in it
=COUNTIF(A$1:A1,A1) This assumes your first entry is in A1. If not adjust as needed. Copy down to the end of the data. In another column, put this =COUNTIF(B1:B7,1) I'm assuming the formula above was in b1:B7 only. Again, adjust as needed. -- HTH, Barb Reinhardt "markmcd" wrote: Doing a pivot table with a large amount of data. We have trip numbers throughout the year that do unfortunately get repeated so in order to make them unique, I concatenated the trip number with the delivery date/100000 so that all my trips look similar to this: 5243.954. For one trip there can be mulitple drops and the report I have to work off (one line per drop) I have this repeat for as many drops as there are. Now I would like to count the number of trips but no matter what I do, it counts the lot (total drops and not total trips) As an example, given the following trip numbers 1.278 1.254 1.254 1.847 This should give total trips = 3 and total drops = 4. That's the intended ie my spreadsheet has over 300,000 lines (drops) but only about 60,000 of those are trips but it still tells me 300,000. Can anyone help me? I am using Excel 2007 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count unique numbers
Assuming orig data as posted in A2 down
put in the top row col labels so that it looks like this Trip Uniq 1.278 1 1.254 1 1.254 1.847 1 etc Put in B2: =IF(A2="","",IF(COUNTIF(A$2:A2,A2)1,"",COUNTIF(A$ 2:A2,A2))) Copy down to the last row of data in col A. Pivot on cols A & B, place Trip in ROW area, place Trip in DATA area (set to Count), place Uniq in DATA area (set to Sum). In the pivot sheet, drag DATA and drop over "Totals" and you should get the desired results. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,100 Files:360 Subscribers:56 xdemechanik --- "markmcd" wrote: Doing a pivot table with a large amount of data. We have trip numbers throughout the year that do unfortunately get repeated so in order to make them unique, I concatenated the trip number with the delivery date/100000 so that all my trips look similar to this: 5243.954. For one trip there can be mulitple drops and the report I have to work off (one line per drop) I have this repeat for as many drops as there are. Now I would like to count the number of trips but no matter what I do, it counts the lot (total drops and not total trips) As an example, given the following trip numbers 1.278 1.254 1.254 1.847 This should give total trips = 3 and total drops = 4. That's the intended ie my spreadsheet has over 300,000 lines (drops) but only about 60,000 of those are trips but it still tells me 300,000. Can anyone help me? I am using Excel 2007 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count unique numbers
Thanks Max.....if I do a sort say by product or by rate as an example (other
fields in the table), won't this cause havoc with the trip count. The column I imagine then should have contigious data in the trip column whether newly created or otherwise. Once again thanks for your assistance. Cheers Mark. "Max" wrote: Assuming orig data as posted in A2 down put in the top row col labels so that it looks like this Trip Uniq 1.278 1 1.254 1 1.254 1.847 1 etc Put in B2: =IF(A2="","",IF(COUNTIF(A$2:A2,A2)1,"",COUNTIF(A$ 2:A2,A2))) Copy down to the last row of data in col A. Pivot on cols A & B, place Trip in ROW area, place Trip in DATA area (set to Count), place Uniq in DATA area (set to Sum). In the pivot sheet, drag DATA and drop over "Totals" and you should get the desired results. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,100 Files:360 Subscribers:56 xdemechanik --- "markmcd" wrote: Doing a pivot table with a large amount of data. We have trip numbers throughout the year that do unfortunately get repeated so in order to make them unique, I concatenated the trip number with the delivery date/100000 so that all my trips look similar to this: 5243.954. For one trip there can be mulitple drops and the report I have to work off (one line per drop) I have this repeat for as many drops as there are. Now I would like to count the number of trips but no matter what I do, it counts the lot (total drops and not total trips) As an example, given the following trip numbers 1.278 1.254 1.254 1.847 This should give total trips = 3 and total drops = 4. That's the intended ie my spreadsheet has over 300,000 lines (drops) but only about 60,000 of those are trips but it still tells me 300,000. Can anyone help me? I am using Excel 2007 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count unique numbers
On the face of it, the earlier formula for col B (Uniq) should remain
unaffected by any sorting done (on the table) in other cols - that's the key issue you raised. The Trip col's uniqueness is a presumption, taken as you had stated in your original post. You might need to re-examine whether your concat idea/formula is sufficient to render this field unique. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,100 Files:360 Subscribers:56 xdemechanik --- "markmcd" wrote: Thanks Max.....if I do a sort say by product or by rate as an example (other fields in the table), won't this cause havoc with the trip count. The column I imagine then should have contigious data in the trip column whether newly created or otherwise. Once again thanks for your assistance. Cheers Mark. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count unique numbers
ie my spreadsheet has over 300,000 lines
COUNTIF(A$2:A2,A2) Using an ever progressing range formula like that on 300k rows might be a killer! By the time you get to the last row you've referenced 45,000,150,000 cells! =SUMPRODUCT(ROW(1:300000)) 1+2+3+4+5+6+7.....+300000 Plus, you're doing it twice: IF(COUNTIF(A$2:A2,A2)1,"",COUNTIF(A$2:A2,A2))) A generic formula to count unique numbers: =COUNT(1/FREQUENCY(range,range)) This will also be a killer on 300k rows. For "just" 100,000 rows it caused my version of Excel 2007 to lock-up ("not respond"). -- Biff Microsoft Excel MVP "Max" wrote in message ... On the face of it, the earlier formula for col B (Uniq) should remain unaffected by any sorting done (on the table) in other cols - that's the key issue you raised. The Trip col's uniqueness is a presumption, taken as you had stated in your original post. You might need to re-examine whether your concat idea/formula is sufficient to render this field unique. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,100 Files:360 Subscribers:56 xdemechanik --- "markmcd" wrote: Thanks Max.....if I do a sort say by product or by rate as an example (other fields in the table), won't this cause havoc with the trip count. The column I imagine then should have contigious data in the trip column whether newly created or otherwise. Once again thanks for your assistance. Cheers Mark. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count unique numbers
Yes that's right 300k rows. It does take forever. During this time I do get
Excel not responding but have found if I leave it go rather than killing it, it eventually comes good and you can carry on. By August YTD it is 300k rows and my concern as the year goes on is just how big will this be by the end of December. I'm thinking I could be better off using MS Access but don't know the program that well. Would I be better off to use Access in this cases. (Microsoft Office 2007 in use) "T. Valko" wrote: ie my spreadsheet has over 300,000 lines COUNTIF(A$2:A2,A2) Using an ever progressing range formula like that on 300k rows might be a killer! By the time you get to the last row you've referenced 45,000,150,000 cells! =SUMPRODUCT(ROW(1:300000)) 1+2+3+4+5+6+7.....+300000 Plus, you're doing it twice: IF(COUNTIF(A$2:A2,A2)1,"",COUNTIF(A$2:A2,A2))) A generic formula to count unique numbers: =COUNT(1/FREQUENCY(range,range)) This will also be a killer on 300k rows. For "just" 100,000 rows it caused my version of Excel 2007 to lock-up ("not respond"). -- Biff Microsoft Excel MVP "Max" wrote in message ... On the face of it, the earlier formula for col B (Uniq) should remain unaffected by any sorting done (on the table) in other cols - that's the key issue you raised. The Trip col's uniqueness is a presumption, taken as you had stated in your original post. You might need to re-examine whether your concat idea/formula is sufficient to render this field unique. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,100 Files:360 Subscribers:56 xdemechanik --- "markmcd" wrote: Thanks Max.....if I do a sort say by product or by rate as an example (other fields in the table), won't this cause havoc with the trip count. The column I imagine then should have contigious data in the trip column whether newly created or otherwise. Once again thanks for your assistance. Cheers Mark. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count unique numbers
Would I be better off to use Access in this cases.
I would say yes in this case. -- Biff Microsoft Excel MVP "markmcd" wrote in message ... Yes that's right 300k rows. It does take forever. During this time I do get Excel not responding but have found if I leave it go rather than killing it, it eventually comes good and you can carry on. By August YTD it is 300k rows and my concern as the year goes on is just how big will this be by the end of December. I'm thinking I could be better off using MS Access but don't know the program that well. Would I be better off to use Access in this cases. (Microsoft Office 2007 in use) "T. Valko" wrote: ie my spreadsheet has over 300,000 lines COUNTIF(A$2:A2,A2) Using an ever progressing range formula like that on 300k rows might be a killer! By the time you get to the last row you've referenced 45,000,150,000 cells! =SUMPRODUCT(ROW(1:300000)) 1+2+3+4+5+6+7.....+300000 Plus, you're doing it twice: IF(COUNTIF(A$2:A2,A2)1,"",COUNTIF(A$2:A2,A2))) A generic formula to count unique numbers: =COUNT(1/FREQUENCY(range,range)) This will also be a killer on 300k rows. For "just" 100,000 rows it caused my version of Excel 2007 to lock-up ("not respond"). -- Biff Microsoft Excel MVP "Max" wrote in message ... On the face of it, the earlier formula for col B (Uniq) should remain unaffected by any sorting done (on the table) in other cols - that's the key issue you raised. The Trip col's uniqueness is a presumption, taken as you had stated in your original post. You might need to re-examine whether your concat idea/formula is sufficient to render this field unique. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,100 Files:360 Subscribers:56 xdemechanik --- "markmcd" wrote: Thanks Max.....if I do a sort say by product or by rate as an example (other fields in the table), won't this cause havoc with the trip count. The column I imagine then should have contigious data in the trip column whether newly created or otherwise. Once again thanks for your assistance. Cheers Mark. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUPS to Count Unique numbers | Excel Discussion (Misc queries) | |||
Count unique numbers | Excel Worksheet Functions | |||
Count Unique Numbers | Excel Worksheet Functions | |||
Count unique numbers for execs | Excel Discussion (Misc queries) | |||
summary count of unique numbers | Excel Discussion (Misc queries) |