Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Groups
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Groups
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Groups
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 " 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Groups
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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Groups
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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Groups
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 DataFilterAdvanced 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 D1:D4 (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 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting groups of words | Excel Worksheet Functions | |||
Counting within groups | Excel Worksheet Functions | |||
COUNTING GROUPS IN ONE COLUMN | Excel Discussion (Misc queries) | |||
counting age groups | Excel Discussion (Misc queries) | |||
counting age groups | Excel Worksheet Functions |