Count unique numbers

  • Thread starter Thread starter markmcd
  • Start date Start date
M

markmcd

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
 
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...
 
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.
 
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
 
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.
 
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
 
ie my spreadsheet has over 300,000 lines
COUNTIF(A$2:A2,A2)
Click to expand...

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").
 
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)
 
Back
Top