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

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

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

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

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

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.

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.

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.

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.