Count Unique Items with Multiple Criteria

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

0
rrstudio2 (8)
10/13/2007 2:22:15 AM
excel 39879 articles. 2 followers. Follow

12 Replies
830 Views

Similar Articles

[PageSpeed] 50

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 B5:D11).
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)


<rrstudio2@icqmail.com> 
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

0
jim.coneXXX (771)
10/13/2007 3:55:18 AM
=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


-- 

Regards,

Peo Sjoblom





<rrstudio2@icqmail.com> wrote in message 
news:1192242135.336414.238160@v23g2000prn.googlegroups.com...
>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
> 


0
terre081 (3244)
10/13/2007 4:23:23 AM
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
-- 
Regards
Roger Govier



<rrstudio2@icqmail.com> wrote in message 
news:1192242135.336414.238160@v23g2000prn.googlegroups.com...
>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
> 


0
Roger
10/13/2007 8:38:47 AM
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@icqmail.com" wrote:

> 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
> 
> 
0
traveller (56)
10/13/2007 2:07:01 PM
On Oct 13, 7:07 am, Traveller <Travel...@discussions.microsoft.com>
wrote:
> 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.
>
>
>
> "rrstud...@icqmail.com" wrote:
> > 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- Hide quoted text -
>
> - 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

0
rrstudio2 (8)
10/13/2007 2:34:50 PM
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 !
-----------------------------------------------------------------------------------------------

<rrstudio2@icqmail.com> wrote in message 
news:1192286090.945007.247540@k35g2000prh.googlegroups.com...
On Oct 13, 7:07 am, Traveller <Travel...@discussions.microsoft.com>
wrote:
> 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.
>
>
>
> "rrstud...@icqmail.com" wrote:
> > 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- Hide quoted text -
>
> - 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


0
ragdyer1 (4060)
10/13/2007 2:56:59 PM
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 !
-----------------------------------------------------------------------------------------------


"RagDyeR" <ragdyer@cutoutmsn.com> wrote in message 
news:e6BJYmaDIHA.4836@TK2MSFTNGP06.phx.gbl...
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 !
-----------------------------------------------------------------------------------------------

<rrstudio2@icqmail.com> wrote in message
news:1192286090.945007.247540@k35g2000prh.googlegroups.com...
On Oct 13, 7:07 am, Traveller <Travel...@discussions.microsoft.com>
wrote:
> 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.
>
>
>
> "rrstud...@icqmail.com" wrote:
> > 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- Hide quoted text -
>
> - 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



0
ragdyer1 (4060)
10/13/2007 3:01:49 PM
Apologies, I missed that you wanted unique.



-- 
Regards
Roger Govier



<rrstudio2@icqmail.com> wrote in message 
news:1192286090.945007.247540@k35g2000prh.googlegroups.com...
> On Oct 13, 7:07 am, Traveller <Travel...@discussions.microsoft.com>
> wrote:
>> 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.
>>
>>
>>
>> "rrstud...@icqmail.com" wrote:
>> > 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- Hide quoted text -
>>
>> - 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
> 


0
Roger
10/13/2007 3:14:58 PM
On Oct 13, 8:14 am, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk>
wrote:
> Apologies, I missed that you wanted unique.
>
> --
> Regards
> Roger Govier
>
> <rrstud...@icqmail.com> wrote in message
>
> news:1192286090.945007.247540@k35g2000prh.googlegroups.com...
>
>
>
> > On Oct 13, 7:07 am, Traveller <Travel...@discussions.microsoft.com>
> > wrote:
> >> 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.
>
> >> "rrstud...@icqmail.com" wrote:
> >> > 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- Hide quoted text -
>
> >> - 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- Hide quoted text -
>
> - 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

0
rrstudio2 (8)
10/13/2007 9:19:26 PM
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@icqmail.com" wrote:

> On Oct 13, 7:07 am, Traveller <Travel...@discussions.microsoft.com>
> wrote:
> > 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.
> >
> >
> >
> > "rrstud...@icqmail.com" wrote:
> > > 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- Hide quoted text -
> >
> > - 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
> 
> 
0
traveller (56)
10/14/2007 1:47:00 AM
On Oct 13, 6:47 pm, Traveller <Travel...@discussions.microsoft.com>
wrote:
> 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.
>
>
>
> "rrstud...@icqmail.com" wrote:
> > On Oct 13, 7:07 am, Traveller <Travel...@discussions.microsoft.com>
> > wrote:
> > > 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.
>
> > > "rrstud...@icqmail.com" wrote:
> > > > 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- Hide quoted text -
>
> > > - 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- Hide quoted text -
>
> - 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

0
rrstudio2 (8)
10/15/2007 5:27:05 PM
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 !
-----------------------------------------------------------------------------------------------

<rrstudio2@icqmail.com> wrote in message 
news:1192469225.931685.11590@y27g2000pre.googlegroups.com...
On Oct 13, 6:47 pm, Traveller <Travel...@discussions.microsoft.com>
wrote:
> 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.
>
>
>
> "rrstud...@icqmail.com" wrote:
> > On Oct 13, 7:07 am, Traveller <Travel...@discussions.microsoft.com>
> > wrote:
> > > 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.
>
> > > "rrstud...@icqmail.com" wrote:
> > > > 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- Hide quoted text -
>
> > > - 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- Hide quoted text -
>
> - 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


0
ragdyer1 (4060)
10/16/2007 2:46:14 PM
Reply:

Similar Artilces:

The Item could not be deleted. It was either moved or already deleted, or access was denied.
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C42C5B.11C3D7B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I had to rebuild a corrupt database this morning. My Priv.edb and = Pub.edb were the files I worked on with the ESEUTIL. Everything is = working except users are complaining that they cannot delete e-mails = that were "zapped" during the rebuild process. The message they're = receiving is " The Item could not be deleted. It was either moved or = already deleted, or access was denied...

Multiple user names at one bank or brokerage
I have 3 accounts at one bank. I have 3 corresponding accounts set up in MS Money 2007. One of them is setup for online banking while the other two are not. I now want to set the other two up for online banking but I can't find a way to specify the unique login info for each of the other two accounts. ...

Multiple Domain e-mail addresses
We are running Exchange Server 2003 Enterprise addition and I am having a bit of a "brain-freeze" at the moment... Where in the Exchange Admin do I configure the server to accept e-mail from more than one domain. For example, we currently accept e-mail from mycurrentdomain.ca and would also like to accept email from mycurrentdomain.com. Please note that I have updated all of our DNS MX records as required, just need to make the changes on the Exchange server. Thanks in advance... Recipient polices-email address policy. "Raymond Spitzingle" <ray(nospam)@stuff...

Business Portal
BP 3.0 I am trying to set up our Business Portal to be as easy as possible for our sales team. One of the requests they have made is the ability to find out WHAT PO is COMMITTED to the SPECIFIC line item in an order. Similar to actually looking at GP Sales Transaction Entry window where it gives an icon to show that the item has been purchased, what PO it's been committed to. I have spent hours trying to make this happen. Has anyone else been able to do this? If so, I would appreciate detailed steps. Thank you ...

Multiple accounts #3
I have 2 Hotmail and 1 MSN accounts set up in Outlook 2002. It shows that it sends and receives all 3 accounts successfully, but when I go to the Inbox of the Hotmail accounts, there aren't any messages. Thanks, Rick It says it send and received fine, but problems occur when you set up more than one HTTP account, so it is recommended that you only use one per profile ...

HOWTO create a Mail Merge Template in MSCRM with multiple child records
Hi, I have created successfully a basic Mail Merge template in MS Word in Dynamics CRM 4.0 for Order Entity. My challenge now is how to design a template that retrieves the order details (product and prices) related to the order record? This sounds like a parent-child template. Any idea? On Jun 2, 1:26=A0pm, "Benjie Fallar III" <bfall...@hotmail.com> wrote: > Hi, > I have created successfully a basic Mail Merge template in MS Word in > Dynamics CRM 4.0 for Order Entity. > My challenge now is how to design a template that retrieves the order > details (prod...

Outlook/Access 2003
I've been doing a lot of research and purchased (but haven't completely read) Sue Mosher's book. However, before I spend much more time I'd like to find out if my idea is even feasible. The basic idea is to create an appointment system for multiple therapists that can be used by a receptionist and other staff. Using Outlook and Exchange is very nice but the appointment data needs to end up in an Access table immediately. We can purchase SBS 2003 with Exchange 2003 or we can buy a third party tool for calendar sharing whichever would be the easiest to use. I currently...

Query Criteria issue
Access 2003: Trying to create a demographics report that will allow the following. Gender example On the form "frmStudentDemographics" is a drop down field named "cboGender" When the user makes a selection "Male" a report is generated for only Males. But in some cases the user will want to run a report for both (all) genders. So I added a check box next to the drop down named "ckAllGender" My idea is that if that box is checked then the report will show information on Both (all) genders. So in the query that generates the report, in the c...

Multiple indexes on same fields in different order
I sometimes need to find the largest or smallest value in a field. If I put two indexes on the same field, one in ascending order and one in descending order, is Jet smart enough to use the proper index if a give it a grouping query with the condition Max or Min, or am I just wasting resources? Pete -- This e-mail address is fake, to keep spammers and their address harvesters out of my hair. If you want to get in touch personally, I am 'pdanes' and I use yahoo mail. But please use the newsgroup when possible, so that all may benefit from the exchange of ideas. ...

How to assign unique number to column duplicates?
Hi All, I need to assign a unique number to a set of duplicates all in one column in Excel 2007. so columnA will has about 9000 numbers, some of them unique, and others are duplicates of 2-4 approx. I used to conditional formatting to show which are duplicates, but need to be able to assign a unique number to each set duplicates, that will be in sequential order... e.g. ColumnA ColumnB(unique ID) 01233 0001 01233 0001 01234 - 01255 0002 01255 0002 etc.... Any ideas please? I don't know how to do programming, just form...

Out of Office Assistant replies multiple times
The situation: The user of a fully patched Outlook 2003 SP1 sets an Out of Office reply using the Out Of Office Assistant.. The result: The first time someone sends a message while the OOA reply is turned on, they get the full text of the OOA reply as it is configured. The second and subsequent emails from that same sender gets a truncated version of the OOA message. In my testing, my second message (which isn't supposed to be replied to), got me 2 truncated replies. Examples from my testing: 1st reply: "I am out of the office and will return on Monday, February 28, 2005. At th...

Can i move multiple lines between workbooks
I have a .csv format file listing speed cameras, and the different speeds are identified by two characters at the end of each line - 30, 40, 50 etc. I would like to create separate files for each speed, but Excel won't allow me to cut/copy several lines to created a new file/workbook. Is there any way round this? Martin Ellis Sun, 16 Sep 2007 03:28:00 -0700 from Martin Ellis <MartinEllis@discussions.microsoft.com>: > I have a .csv format file listing speed cameras, and the different speeds are > identified by two characters at the end of each line - 30, 40, 50 etc...

Vlookup, multiple times in one column summing corresponding
I have a list that contains common invoice numbers that appear multipl times in column A and need to use the vlookup function to find and su the corresponding amounts in column B. As you would be aware the following vlookup functio =VLOOKUP(A3,DATA,2,FALSE) will only return the one amount once i reaches a match and I need it to continue down the column and sum al matching invoice numbers. Column A Column B 145768 356.87 145769 678.90 145880 80.87 * 145769 103.55 145770 56.90 145769 78.32 145880 54.09 * The answer I�m looking to return is 145880 134.96 It has to be a vlookup sty...

IF Multiple Conditions
A B C D E Color Total Time Total Time 2 Time Allowed Y/NO BLUE 1:22:33 2:22:33 1:00:00 BLUE 2:22:33 3:22:33 2:00:00 BLUE 3:22:33 4:22:33 3:00:00 BLUE 4:22:33 5:22:33 4:00:00 BLUE 5:22:33 6:22:33 5:00:00 RED 6:22:33 7:22:33 6:00:00 RED 7:22:33 8:22:33 7:00:00 RED 8:22:33 9:22:33 8:00:00 I need an IF formula that: - will read IF condition in the A column is Blue it will bring back...

One front-end server multiple back-end servers
Hi, I was wondering if someone could help with the following scenerio and make some suggestions as to an answer. We have 3 exchange AG groups. 1 AG group has 3 exchange servers in it, each located in it's own AD domain. 1 AG group has one exchange server in it, it's in its own AD domain. Thirdly, the last AG group has 2 exchange servers in it, both are in the same AD domain. 1 of these 2 exchange servers in the final AG mentioned is a front-end server, that should be supporting all the exchange back-end servers. The problem we are facing is that when a user from any of the ...

multiplication in an Excell Spreadsheet
I am having difficulty in multiplying 285 units by 5.960 p. per unit. Can anyone help me solve this problem please? With 285 in cell A1 and 5.96 in cell A2, in cell A3 put =A1*A2 -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "KiaOra" <KiaOra@discussions.microsoft.com...

How do I use the "Correlation" dialog box with multiple ranges?
I'm using data in an Excel 2003 in a workbook, trying to see if there is any correlation between specific offices in which people work and the length of time they have been employed, their age the amount of sickleave taken and/or the amount of sickleave balance they have. I'm attempting to use Data Analysis and the Correlation dialog box. Although the dialog description tells me that I can use multiple comparison ranges, I don't see how to do it. Thanks for any help. Paulf6 - Arrange your data in list (database) format, i.e., names in the top row (Time Employed, Age, ...)...

Counting dates
I am trying to count the number of dates in a column that appear b month and year. For instance, I some that are 11/23/2003 and 11/24/200 and 11/21/2002. How can I makew a formula that will count the instance of 11/2003 -- Message posted from http://www.ExcelForum.com =SUMPRODUCT((YEAR(A1:A100)=2004)*(MONTH(A1:A100)=11)) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "jdavis3874 >" <<jdavis3874.14770b@excelforum-nospam.com> wrote in message news:jdavis3874.14770b@excelforum-nos...

allow multiple bin locations for items
It is common to have an item in more than one location in a store, for instance on the sales floor as well as in backstock. Item properties should support multiple Bin Locations (not just the two in my example). The averaged cost should remain the same across all quantities/locations of the item. Some allowance must be made when inputting physical inventory -- we of course want the overall quantity of items from all locations. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the &q...

Programming a TOC to have page counts (not page location)
Here is what I am trying to accomplish using Word 2007 and XP: I want to create a TOC with the Title and Individual Section Page Counts using separate Individual Files to look like this: Section No. Title No. of Pages 200500 General Provisions 5 200505 Project Closeout and Start-up 10 Information: • The section number and title are the file names (i.e. 2000500 General Provisions.doc) and are also included in the beginning of each document as well as a title (with a set style). • All files are located in the same folder. Here is the current macro I am working with that was...

Counting based on Date Range
I have a an Excel spreadsheet that is linked to an Access Databas Table. Each day, the database is updated with new information including the date. I want to set up an automated Excel report tha will count the number of entries for a date range (monthly). In short I want to reference an entire column (the date column), and have th spreadsheet be able to count how many entries occurred in Jan 04, Fe 04, etc... I've played with counta and countif, but have had little success. Thoughts? Thankx, C -- Message posted from http://www.ExcelForum.com CT Here's an example using SUMIF,...

Outlook 2007 with multiple mailboxes
I have a secretary that my manager wants to be able to see his emails. I set her up with his and her own account. The problem is when she replys or forwards the email, we want it to come from her email not his. Outlook 2007 is too smart and changes accounts when she forwards account so it looks like the manager has replied when the secretary has done on his behalf. You set her up as a DELEGATE and it is doing what you told it to do. If you only wanted to give her permission to see his inbox and calendar you should have set the permissions on the folders directly, and not use the "D...

Item is available on the website
What does checking the "Item is available on the website" box do? Is there then an export of data that can be done to upload to our website? Please help as we are just re-doing our website and would ideally like to use the details from RMS to set up the items on our site. anyone with experience linking the two, please help. Chris I'm sure you'll get specific RMS functionality responses to your question, but I can tell you that I spent some time and money on a shopping card program (called Taber Pro) that use d this function. If you checked that box for items, th...

multiple charts
Hi, is it possible to create one chart per line of data? I have file with hundreds of names and their corresponding data that needs to be mail merged into a letter with their chart. Kathleen Hi Kathleen, > Hi, is it possible to create one chart per line of data? I have file with > hundreds of names and their corresponding data that needs to be mail > merged > into a letter with their chart. Try going here http://www.edferrero.com/ExcelCharts/tabid/102/Default.aspx and download the 'Multiple Chart Builder' Ed Ferrero www.edferrero.com ...

item sold/on hand report
I can't find a report that will give me qty sold and qty on hand for a specific item or supplier. Anyone know of one? Thank you have you tried the item movement report with criteria being supplier? -- Elizabeth M. "Jeff" <anonymous@discussions.microsoft.com> wrote in message news:249c01c4701e$eacd9d50$a301280a@phx.gbl... > I can't find a report that will give me qty sold and qty > on hand for a specific item or supplier. Anyone know of > one? > > Thank you > yes, unfortunately, the item movement report does not give you the option of showi...