Count Unique Items with Multiple Criteria

[rrstudio2]

I am trying to get a list of how many lots a particular car model is
on. For example, say we have a spreadsheet that looks like:

Model License Lot
Ford xjd-394 1
Chevy gwg-394 2
Ford sdf-333 1
Ford lkj-111 3
Toyota skd-333 4
Toyota shk-584 4

I am loking for a way to get data that says how many unique lots each
car is on, so for example:
Ford: 2
Chevy: 1
Toyota: 1

I was trying to do this with Pivot tables and the count functionality,
but it isn't quite getting me the results I want. I can get the
results with a pivot table if I drag the Model followed by the lot
into the row column, but this just shows the data like:
Ford 1
3
Chevy 2
Toyota 4
So from here I can manually found that the Ford is on two lots, the
chevy on one lot, and the toyota is on one lot, but it would be great
if I could use a pivot table to get the actual number of lots instead
of having to count them. Any ideas?

-Andrew V. Romero

 

[Jim Cone]

Andrew,
One way to do it...
1. Sort the data by model and lot number.
2. In an adjacent column join the model and lot values using a formula
similar to: =B6&D6 (assumes data in B511).
3. In another adjacent column enter a formula similar to:
=IF(F6=F5,"",COUNTIF($F$6:$F$11,F6)) (assumes joined values in column F).
4. Fill all formulas down to bottom of data.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


<[email protected]>
wrote in message
I am trying to get a list of how many lots a particular car model is
on. For example, say we have a spreadsheet that looks like:

Model License Lot
Ford xjd-394 1
Chevy gwg-394 2
Ford sdf-333 1
Ford lkj-111 3
Toyota skd-333 4
Toyota shk-584 4

I am loking for a way to get data that says how many unique lots each
car is on, so for example:
Ford: 2
Chevy: 1
Toyota: 1

I was trying to do this with Pivot tables and the count functionality,
but it isn't quite getting me the results I want. I can get the
results with a pivot table if I drag the Model followed by the lot
into the row column, but this just shows the data like:
Ford 1
3
Chevy 2
Toyota 4
So from here I can manually found that the Ford is on two lots, the
chevy on one lot, and the toyota is on one lot, but it would be great
if I could use a pivot table to get the actual number of lots instead
of having to count them. Any ideas?

-Andrew V. Romero

 

[Peo Sjoblom]

=SUM(N(FREQUENCY(IF(A2:A7="Ford",MATCH(C2:C7,C2:C7,0)),MATCH(C2:C7,C2:C7,0))>0))

Adapt the cell references to fit your real data, it needs to be entered with
ctrl + shift & enter

 

[Roger Govier]

Hi

On your PT,
drag Model to the Row Area and
Lot to the Data area (selecting Count as opposed to Sum)
and you will get the result you are looking for.

You don't need Lot in the Row area

 

[Guest]

I couldn't get Roger's solution to work, but this does:

In your Pivot Table, put "Model" in the Row area.
Put "Lot" in both the Column area and the Data area.
Use the function COUNTIF across the columns of data for each model, using
the criteria >0.

 

[rrstudio2]

I couldn't get Roger's solution to work, but this does:

In your Pivot Table, put "Model" in the Row area.
Put "Lot" in both the Column area and the Data area.
Use the function COUNTIF across the columns of data for each model, using
the criteria >0.









- Show quoted text -

I tried Roger's method first, but that doesn't give the desired
results. It will for each car count how many lots it is on, not how
many unique lots it is on.I also tried cutting and pasting Peo's
formula, but excel says it contains an error. Jim had a good idea,
but I neglected to say that I can't resort the real data...well I
guess I could but I would have to first number the lines so I could
get it make to its original form.

Traveller, could you provide some more details? When I put it into a
Pivot table, it shows as
Ford 1 (would the countif formula go in column C, countif(a2:a:
4,b2>0)????? That doesn't seem to work.
3
Chevy 2
Toyota 4

If it was a perfect world, I would like the data to be added into a
column on the original data table, for example
Model License Lot UniqueLots
Ford xjd-394 1 2
Chevy gwg-394 2 1
Ford sdf-333 1 2
Ford lkj-111 3 2
Toyota skd-333 4 1
Toyota shk-584 4 1

Thanks for the ideas,
Andrew V. Romero

 

[RagDyeR]

Peo's formula works *exactly* as you want.

Entered in D1 -
Change the "Ford" to
A2

And don't forget the CSE!
--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.


*After* the CSE, drag down to copy to D7.


Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

I couldn't get Roger's solution to work, but this does:

In your Pivot Table, put "Model" in the Row area.
Put "Lot" in both the Column area and the Data area.
Use the function COUNTIF across the columns of data for each model, using
the criteria >0.









- Show quoted text -

I tried Roger's method first, but that doesn't give the desired
results. It will for each car count how many lots it is on, not how
many unique lots it is on.I also tried cutting and pasting Peo's
formula, but excel says it contains an error. Jim had a good idea,
but I neglected to say that I can't resort the real data...well I
guess I could but I would have to first number the lines so I could
get it make to its original form.

Traveller, could you provide some more details? When I put it into a
Pivot table, it shows as
Ford 1 (would the countif formula go in column C, countif(a2:a:
4,b2>0)????? That doesn't seem to work.
3
Chevy 2
Toyota 4

If it was a perfect world, I would like the data to be added into a
column on the original data table, for example
Model License Lot UniqueLots
Ford xjd-394 1 2
Chevy gwg-394 2 1
Ford sdf-333 1 2
Ford lkj-111 3 2
Toyota skd-333 4 1
Toyota shk-584 4 1

Thanks for the ideas,
Andrew V. Romero

 

[RagDyeR]

Since you're copying down, you'll need the absolute references:

=SUM(N(FREQUENCY(IF($A$2:$A$7=A2,MATCH($C$2:$C$7,$C$2:$C$7,0)),MATCH($C$2:$C$7,$C$2:$C$7,0))>0))

Again, don't forget the CSE!
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


Peo's formula works *exactly* as you want.

Entered in D1 -
Change the "Ford" to
A2

And don't forget the CSE!
--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.


*After* the CSE, drag down to copy to D7.


Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

I couldn't get Roger's solution to work, but this does:

In your Pivot Table, put "Model" in the Row area.
Put "Lot" in both the Column area and the Data area.
Use the function COUNTIF across the columns of data for each model, using
the criteria >0.









- Show quoted text -

I tried Roger's method first, but that doesn't give the desired
results. It will for each car count how many lots it is on, not how
many unique lots it is on.I also tried cutting and pasting Peo's
formula, but excel says it contains an error. Jim had a good idea,
but I neglected to say that I can't resort the real data...well I
guess I could but I would have to first number the lines so I could
get it make to its original form.

Traveller, could you provide some more details? When I put it into a
Pivot table, it shows as
Ford 1 (would the countif formula go in column C, countif(a2:a:
4,b2>0)????? That doesn't seem to work.
3
Chevy 2
Toyota 4

If it was a perfect world, I would like the data to be added into a
column on the original data table, for example
Model License Lot UniqueLots
Ford xjd-394 1 2
Chevy gwg-394 2 1
Ford sdf-333 1 2
Ford lkj-111 3 2
Toyota skd-333 4 1
Toyota shk-584 4 1

Thanks for the ideas,
Andrew V. Romero

 

[Roger Govier]

Apologies, I missed that you wanted unique.

 

[rrstudio2]

Apologies, I missed that you wanted unique.

--
Regards
Roger Govier










- Show quoted text -

Thanks, I copied the formula that RagDyeR had (with absolute
references) and it works great! I haven't tried using the frequency
or match formulas before, so I look forward to learning more about
them because right now I have no idea why this formula works. Any
chance you can break it down into understandable bits for me?

Thanks,
Andrew

 

[Guest]

For what it's worth, I've sent you a sample file with my solution, in case
you want to go that route. I added the VLOOKUP function to get the results
into the format you want. Hope it helps.

 

[rrstudio2]

For what it's worth, I've sent you a sample file with my solution, in case
you want to go that route. I added the VLOOKUP function to get the results
into the format you want. Hope it helps.









- Show quoted text -

Thanks, I will give it a try when I get home. After playing with the
array formula, it turns out that it takes way too long. The
spreadsheets I am working with contain between 10,000 and 20,000
rows. Excel was running for about 45 minutes, before I decided to end
the process.

-Andrew V. Romero

 

[RagDyeR]

You're copying that array formula down 20,000 rows?!?!?!
No wonder you're having this XL resource problem!

Peo's suggestion was aimed at a single row, and I suggested a revision to
enable it to go to 7 rows, to match your example.

You stated that in a "perfect world", you wanted to see *duplicate* results
per brand.
In your example, *each time* Toyota is displayed, you wanted the unique
count displayed.

How about just making a separate list of unique brands per lot?

How many brands do you have ... 10 - 20?
How many lots do you have ... 5 - 10?

That would make maybe 200 rows as opposed to 20,000 rows.

Post back if you're interested in this type of display?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

For what it's worth, I've sent you a sample file with my solution, in case
you want to go that route. I added the VLOOKUP function to get the results
into the format you want. Hope it helps.









- Show quoted text -

Thanks, I will give it a try when I get home. After playing with the
array formula, it turns out that it takes way too long. The
spreadsheets I am working with contain between 10,000 and 20,000
rows. Excel was running for about 45 minutes, before I decided to end
the process.

-Andrew V. Romero