| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Condensing a list with duplicates to a list with non-duplicates
Assume I have this list
FOOD QUANTITY carrots 2 pears 1 apples 3 carrots 1 apples 2 How do I create another column that lists AUTOMATICALLY the distinct FOOD's listed and their quantities... Like so: FOOD Total Quantity carrots 3 pears 1 apples 5 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Condensing a list with duplicates to a list with non-duplicates
I'm no expert, but I would use the Sumif() function. This would depend on
how many unique food items you have. Beside your table of items (or on a separate sheet) put: D1: Food D2: Carrots D3: Pears D4: Apples Assuming your table is A1:B100 (A2 to A100 has food and B2 to B100 has count) In D2 to D3 list your unique foods (as above). In E2 put the the formula: =SUMIF($A$2:$A$100,D2,$B$2:$B$100) Then copy this down to E3. What is does is sums only values in the table that have the value in D2, D3... Hope this help... -- Thanks - John "Nuclear" wrote: Assume I have this list FOOD QUANTITY carrots 2 pears 1 apples 3 carrots 1 apples 2 How do I create another column that lists AUTOMATICALLY the distinct FOOD's listed and their quantities... Like so: FOOD Total Quantity carrots 3 pears 1 apples 5 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Condensing a list with duplicates to a list with non-duplicate
You could just use a pivot table with FOOD in the Row and SUM of QUANTITY in
the data section. If you need more analysis or manipulation and your list is long, you can still use a Pivot table on FOOD to generate a list of unique values and then use that as the key for SUMIFs, COUNTIF, etc. -- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "John" wrote: I'm no expert, but I would use the Sumif() function. This would depend on how many unique food items you have. Beside your table of items (or on a separate sheet) put: D1: Food D2: Carrots D3: Pears D4: Apples Assuming your table is A1:B100 (A2 to A100 has food and B2 to B100 has count) In D2 to D3 list your unique foods (as above). In E2 put the the formula: =SUMIF($A$2:$A$100,D2,$B$2:$B$100) Then copy this down to E3. What is does is sums only values in the table that have the value in D2, D3... Hope this help... -- Thanks - John "Nuclear" wrote: Assume I have this list FOOD QUANTITY carrots 2 pears 1 apples 3 carrots 1 apples 2 How do I create another column that lists AUTOMATICALLY the distinct FOOD's listed and their quantities... Like so: FOOD Total Quantity carrots 3 pears 1 apples 5 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| validation list with no duplicates | Excel Discussion (Misc queries) | |||
| Array Formulas - Unique List from List with Duplicates | Excel Discussion (Misc queries) | |||
| removing duplicates from a list | Excel Discussion (Misc queries) | |||
| How to remove duplicates from a list and copy new list to new colu | Excel Worksheet Functions | |||
| How do I find duplicates in a list | Excel Discussion (Misc queries) | |||
Linear Mode
