I have a data table like

Item Location Qty
A 1 3
B 2 6
C 3 7
A 4 5
A 1 3
C 5 3

I am trying to count how many locations each item is in so I would want
it to say item A is in 2 locations (locations 1 and 4), item B is in 1
location, item C is in 2 locations, etc. I thought this would be easy
but can't seem to get it to work with pivot tables or formulas. So
whats the best way to do this???

-Andrew V. Romero

Andrew

This should be really easy using a pivot table - make sure you have
Item and Location as Row fields, and then you could use either Qty (or
Item or location again) as a Data field and make sure you have the
summarisation set to Count (will be automatic if you use Item as it's
text).

Hope this helps!

Richard


(E-Mail Removed) wrote:
> I have a data table like
>
> Item Location Qty
> A 1 3
> B 2 6
> C 3 7
> A 4 5
> A 1 3
> C 5 3
>
> I am trying to count how many locations each item is in so I would want
> it to say item A is in 2 locations (locations 1 and 4), item B is in 1
> location, item C is in 2 locations, etc. I thought this would be easy
> but can't seem to get it to work with pivot tables or formulas. So
> whats the best way to do this???
>
> -Andrew V. Romero

I still can't get a normal pivot table to work because it seems like it
is counting the duplicate locations, for example item A it says 3 in
the pivot table although it is really only in 2 unique locations.

Thanks for the website link. I added the fomula
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1) to column D and this
seems to correctly tell if it is unique. By including this column in
the pivot table, I am able to tell who many locations that item is in.
I am not sure I understand what that formula is doing....could you
explain some?

Thanks,
Andrew V. Romero


Jim Thomlinson wrote:
> Give this a look...
>
> http://www.contextures.com/xlPivot07.html#Unique
> --
> HTH...
>
> Jim Thomlinson
>
>
> "(E-Mail Removed)" wrote:
>
> > I have a data table like
> >
> > Item Location Qty
> > A 1 3
> > B 2 6
> > C 3 7
> > A 4 5
> > A 1 3
> > C 5 3
> >
> > I am trying to count how many locations each item is in so I would want
> > it to say item A is in 2 locations (locations 1 and 4), item B is in 1
> > location, item C is in 2 locations, etc. I thought this would be easy
> > but can't seem to get it to work with pivot tables or formulas. So
> > whats the best way to do this???
> >
> > -Andrew V. Romero
> >
> >

Many apologies Andrew - mine was only half a solution. If you remove
sub-totalling from the pivot table (ie so you only have a Grand Total)
then the number of unique Locations will be given by = ROWS(A3:A50)
which is basically the cell address with the first location to the cell
address with the last location (from the pivot table) as the number of
rows will be representative of unique Item/Location combinations.

Sorry for not making that clear before.

Richard


RichardSchollar wrote:
> Andrew
>
> This should be really easy using a pivot table - make sure you have
> Item and Location as Row fields, and then you could use either Qty (or
> Item or location again) as a Data field and make sure you have the
> summarisation set to Count (will be automatic if you use Item as it's
> text).
>
> Hope this helps!
>
> Richard
>
>
> (E-Mail Removed) wrote:
> > I have a data table like
> >
> > Item Location Qty
> > A 1 3
> > B 2 6
> > C 3 7
> > A 4 5
> > A 1 3
> > C 5 3
> >
> > I am trying to count how many locations each item is in so I would want
> > it to say item A is in 2 locations (locations 1 and 4), item B is in 1
> > location, item C is in 2 locations, etc. I thought this would be easy
> > but can't seem to get it to work with pivot tables or formulas. So
> > whats the best way to do this???
> >
> > -Andrew V. Romero

Thanks for the help. The sumproduct is definately something I will
play around with more. The example I gave is part of a larger problem
that we are having in analyzing some data. Let me see if you would
agree with how I am going about this. We have a report that list if a
particular item has a stockout, but this report lists a stock out when
each particular location is empty as opposed to when the whole
department is out. For example

Item Loc Stockout (1=out, 0=not out)
A 1 1
B 2 1
C 3 0
D 4 1
B 5 0
A 3 1
D 6 1

So I would like to be able to generate data saying that we ran totally
out of A (both locations 1 and 3 ran out), and D but don't want to
include data like B since only one location ran out (2) but there was
another location that still has it (5). My thought was to first figure
out how many locations each item was stocked in (hence the original
post). Then sum the total number of stockouts per day and divide it by
the number of locations to get the number of total stockouts per day
per item. So item A had two stockouts, it was stocked in 2 places, so
2/2=1 total stockout. Obviously you would have to do some rounding to
account for issues like 5 stockouts but it is stocked in 6 locations,
thus we don't care because it wasn't totally out. Any ideas on a
better/quikier way to do this?

-Andrew V. Romero


RichardSchollar wrote:
> Many apologies Andrew - mine was only half a solution. If you remove
> sub-totalling from the pivot table (ie so you only have a Grand Total)
> then the number of unique Locations will be given by = ROWS(A3:A50)
> which is basically the cell address with the first location to the cell
> address with the last location (from the pivot table) as the number of
> rows will be representative of unique Item/Location combinations.
>
> Sorry for not making that clear before.
>
> Richard
>
>
> RichardSchollar wrote:
> > Andrew
> >
> > This should be really easy using a pivot table - make sure you have
> > Item and Location as Row fields, and then you could use either Qty (or
> > Item or location again) as a Data field and make sure you have the
> > summarisation set to Count (will be automatic if you use Item as it's
> > text).
> >
> > Hope this helps!
> >
> > Richard
> >
> >
> > (E-Mail Removed) wrote:
> > > I have a data table like
> > >
> > > Item Location Qty
> > > A 1 3
> > > B 2 6
> > > C 3 7
> > > A 4 5
> > > A 1 3
> > > C 5 3
> > >
> > > I am trying to count how many locations each item is in so I would want
> > > it to say item A is in 2 locations (locations 1 and 4), item B is in 1
> > > location, item C is in 2 locations, etc. I thought this would be easy
> > > but can't seem to get it to work with pivot tables or formulas. So
> > > whats the best way to do this???
> > >
> > > -Andrew V. Romero

So does:

=SUMIF(A:A,"A",C:C)/COUNTIF(A:A,"A")

do what you want?

You could have a list of the items (ie no duplicates so only A,B,C,D)
somewhere on the sheet (you can get this from your Item data using
Data>Filter>Advanced Filter and selecting your column A (item) records
in the List Range, checking Copy To Another Location, providing the
cell ref to copy to eg D1 for example, and finally checking Unique
RecordsOnly).

Say this list is in D14 (I know you're likely to have far more items
- just an example) then you could use the following formula copied
down:

=SUMIF(A:A,D1,C:C)/COUNTIF(A:A,D1)

Thus, anything less than 1 shows you have at least 1 location that
hasn't suffered a stock-out of that particular item.

Does this help?

Richard


(E-Mail Removed) wrote:
> Thanks for the help. The sumproduct is definately something I will
> play around with more. The example I gave is part of a larger problem
> that we are having in analyzing some data. Let me see if you would
> agree with how I am going about this. We have a report that list if a
> particular item has a stockout, but this report lists a stock out when
> each particular location is empty as opposed to when the whole
> department is out. For example
>
> Item Loc Stockout (1=out, 0=not out)
> A 1 1
> B 2 1
> C 3 0
> D 4 1
> B 5 0
> A 3 1
> D 6 1
>
> So I would like to be able to generate data saying that we ran totally
> out of A (both locations 1 and 3 ran out), and D but don't want to
> include data like B since only one location ran out (2) but there was
> another location that still has it (5). My thought was to first figure
> out how many locations each item was stocked in (hence the original
> post). Then sum the total number of stockouts per day and divide it by
> the number of locations to get the number of total stockouts per day
> per item. So item A had two stockouts, it was stocked in 2 places, so
> 2/2=1 total stockout. Obviously you would have to do some rounding to
> account for issues like 5 stockouts but it is stocked in 6 locations,
> thus we don't care because it wasn't totally out. Any ideas on a
> better/quikier way to do this?
>
> -Andrew V. Romero
>
>
> RichardSchollar wrote:
> > Many apologies Andrew - mine was only half a solution. If you remove
> > sub-totalling from the pivot table (ie so you only have a Grand Total)
> > then the number of unique Locations will be given by = ROWS(A3:A50)
> > which is basically the cell address with the first location to the cell
> > address with the last location (from the pivot table) as the number of
> > rows will be representative of unique Item/Location combinations.
> >
> > Sorry for not making that clear before.
> >
> > Richard
> >
> >
> > RichardSchollar wrote:
> > > Andrew
> > >
> > > This should be really easy using a pivot table - make sure you have
> > > Item and Location as Row fields, and then you could use either Qty (or
> > > Item or location again) as a Data field and make sure you have the
> > > summarisation set to Count (will be automatic if you use Item as it's
> > > text).
> > >
> > > Hope this helps!
> > >
> > > Richard
> > >
> > >
> > > (E-Mail Removed) wrote:
> > > > I have a data table like
> > > >
> > > > Item Location Qty
> > > > A 1 3
> > > > B 2 6
> > > > C 3 7
> > > > A 4 5
> > > > A 1 3
> > > > C 5 3
> > > >
> > > > I am trying to count how many locations each item is in so I would want
> > > > it to say item A is in 2 locations (locations 1 and 4), item B is in 1
> > > > location, item C is in 2 locations, etc. I thought this would be easy
> > > > but can't seem to get it to work with pivot tables or formulas. So
> > > > whats the best way to do this???
> > > >
> > > > -Andrew V. Romero

I will have to work on this. It still isn't working correctly, but I
think with this information I should be able to get it. I am suprised
at how difficult this is, I thought this would really be pretty easy...

Thanks for your help,
Andrew V. Romero

RichardSchollar wrote:
> So does:
>
> =SUMIF(A:A,"A",C:C)/COUNTIF(A:A,"A")
>
> do what you want?
>
> You could have a list of the items (ie no duplicates so only A,B,C,D)
> somewhere on the sheet (you can get this from your Item data using
> Data>Filter>Advanced Filter and selecting your column A (item) records
> in the List Range, checking Copy To Another Location, providing the
> cell ref to copy to eg D1 for example, and finally checking Unique
> RecordsOnly).
>
> Say this list is in D14 (I know you're likely to have far more items
> - just an example) then you could use the following formula copied
> down:
>
> =SUMIF(A:A,D1,C:C)/COUNTIF(A:A,D1)
>
> Thus, anything less than 1 shows you have at least 1 location that
> hasn't suffered a stock-out of that particular item.
>
> Does this help?
>
> Richard
>
>
> (E-Mail Removed) wrote:
> > Thanks for the help. The sumproduct is definately something I will
> > play around with more. The example I gave is part of a larger problem
> > that we are having in analyzing some data. Let me see if you would
> > agree with how I am going about this. We have a report that list if a
> > particular item has a stockout, but this report lists a stock out when
> > each particular location is empty as opposed to when the whole
> > department is out. For example
> >
> > Item Loc Stockout (1=out, 0=not out)
> > A 1 1
> > B 2 1
> > C 3 0
> > D 4 1
> > B 5 0
> > A 3 1
> > D 6 1
> >
> > So I would like to be able to generate data saying that we ran totally
> > out of A (both locations 1 and 3 ran out), and D but don't want to
> > include data like B since only one location ran out (2) but there was
> > another location that still has it (5). My thought was to first figure
> > out how many locations each item was stocked in (hence the original
> > post). Then sum the total number of stockouts per day and divide it by
> > the number of locations to get the number of total stockouts per day
> > per item. So item A had two stockouts, it was stocked in 2 places, so
> > 2/2=1 total stockout. Obviously you would have to do some rounding to
> > account for issues like 5 stockouts but it is stocked in 6 locations,
> > thus we don't care because it wasn't totally out. Any ideas on a
> > better/quikier way to do this?
> >
> > -Andrew V. Romero
> >
> >
> > RichardSchollar wrote:
> > > Many apologies Andrew - mine was only half a solution. If you remove
> > > sub-totalling from the pivot table (ie so you only have a Grand Total)
> > > then the number of unique Locations will be given by = ROWS(A3:A50)
> > > which is basically the cell address with the first location to the cell
> > > address with the last location (from the pivot table) as the number of
> > > rows will be representative of unique Item/Location combinations.
> > >
> > > Sorry for not making that clear before.
> > >
> > > Richard
> > >
> > >
> > > RichardSchollar wrote:
> > > > Andrew
> > > >
> > > > This should be really easy using a pivot table - make sure you have
> > > > Item and Location as Row fields, and then you could use either Qty (or
> > > > Item or location again) as a Data field and make sure you have the
> > > > summarisation set to Count (will be automatic if you use Item as it's
> > > > text).
> > > >
> > > > Hope this helps!
> > > >
> > > > Richard
> > > >
> > > >
> > > > (E-Mail Removed) wrote:
> > > > > I have a data table like
> > > > >
> > > > > Item Location Qty
> > > > > A 1 3
> > > > > B 2 6
> > > > > C 3 7
> > > > > A 4 5
> > > > > A 1 3
> > > > > C 5 3
> > > > >
> > > > > I am trying to count how many locations each item is in so I would want
> > > > > it to say item A is in 2 locations (locations 1 and 4), item B is in 1
> > > > > location, item C is in 2 locations, etc. I thought this would be easy
> > > > > but can't seem to get it to work with pivot tables or formulas. So
> > > > > whats the best way to do this???
> > > > >
> > > > > -Andrew V. Romero