Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining rows of like data?
I have a spreadsheet that contains rows with multiple entries for the same
item. How can I use a filter or a function to combine the values for the like items in to one row? Example: In the same chart, item number 40-011 appears three times with quantities of 5, 3, and 2. How can I filter the data so there is one occurence of 40-011 with a quantity of 10? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining rows of like data?
Kinda hard to say without a bit more info on wht your source data looks like
but I might be inclined to use a pivot table to combine the data... Data - Pivot table - Follow the wizard... -- HTH... Jim Thomlinson "Juggernaut" wrote: I have a spreadsheet that contains rows with multiple entries for the same item. How can I use a filter or a function to combine the values for the like items in to one row? Example: In the same chart, item number 40-011 appears three times with quantities of 5, 3, and 2. How can I filter the data so there is one occurence of 40-011 with a quantity of 10? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining rows of like data?
Thanks Jim. I will try and describe my source data better. The data looks
like this: Item Qty 40-001 4 40-011 5 40-028 4 40-011 3 10-012 4 40-011 2 So, as you can see, 40-011 appears in the list three times. How can I consolidate the data so there is one line item for 40-011 that displays a Qty of 10? My actual source data is about 15,000 rows long, so you can see how it would be tedious to try and find all the occurences of 40-011 and then doing the math to total them under one line. "Jim Thomlinson" wrote: Kinda hard to say without a bit more info on wht your source data looks like but I might be inclined to use a pivot table to combine the data... Data - Pivot table - Follow the wizard... -- HTH... Jim Thomlinson "Juggernaut" wrote: I have a spreadsheet that contains rows with multiple entries for the same item. How can I use a filter or a function to combine the values for the like items in to one row? Example: In the same chart, item number 40-011 appears three times with quantities of 5, 3, and 2. How can I filter the data so there is one occurence of 40-011 with a quantity of 10? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining rows of like data?
I feel like the answer will be something similar to the Advanced Filter under
the Data tab. I want to filter a range of cells, leaving only unique entries, but I want the values of the deleted entries to be aggregated under the one remaining unique entry. "Juggernaut" wrote: Thanks Jim. I will try and describe my source data better. The data looks like this: Item Qty 40-001 4 40-011 5 40-028 4 40-011 3 10-012 4 40-011 2 So, as you can see, 40-011 appears in the list three times. How can I consolidate the data so there is one line item for 40-011 that displays a Qty of 10? My actual source data is about 15,000 rows long, so you can see how it would be tedious to try and find all the occurences of 40-011 and then doing the math to total them under one line. "Jim Thomlinson" wrote: Kinda hard to say without a bit more info on wht your source data looks like but I might be inclined to use a pivot table to combine the data... Data - Pivot table - Follow the wizard... -- HTH... Jim Thomlinson "Juggernaut" wrote: I have a spreadsheet that contains rows with multiple entries for the same item. How can I use a filter or a function to combine the values for the like items in to one row? Example: In the same chart, item number 40-011 appears three times with quantities of 5, 3, and 2. How can I filter the data so there is one occurence of 40-011 with a quantity of 10? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining rows of like data?
Look at SUMPRODUCT: http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Dave -- Brevity is the soul of wit. "Juggernaut" wrote: I feel like the answer will be something similar to the Advanced Filter under the Data tab. I want to filter a range of cells, leaving only unique entries, but I want the values of the deleted entries to be aggregated under the one remaining unique entry. "Juggernaut" wrote: Thanks Jim. I will try and describe my source data better. The data looks like this: Item Qty 40-001 4 40-011 5 40-028 4 40-011 3 10-012 4 40-011 2 So, as you can see, 40-011 appears in the list three times. How can I consolidate the data so there is one line item for 40-011 that displays a Qty of 10? My actual source data is about 15,000 rows long, so you can see how it would be tedious to try and find all the occurences of 40-011 and then doing the math to total them under one line. "Jim Thomlinson" wrote: Kinda hard to say without a bit more info on wht your source data looks like but I might be inclined to use a pivot table to combine the data... Data - Pivot table - Follow the wizard... -- HTH... Jim Thomlinson "Juggernaut" wrote: I have a spreadsheet that contains rows with multiple entries for the same item. How can I use a filter or a function to combine the values for the like items in to one row? Example: In the same chart, item number 40-011 appears three times with quantities of 5, 3, and 2. How can I filter the data so there is one occurence of 40-011 with a quantity of 10? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining rows of like data?
See specifically the section of the paper linked to below titled "Evolving
use of SUMPRODUCT" There's your answer. -- Brevity is the soul of wit. "Dave F" wrote: Look at SUMPRODUCT: http://www.xldynamic.com/source/xld.SUMPRODUCT.html Dave -- Brevity is the soul of wit. "Juggernaut" wrote: I feel like the answer will be something similar to the Advanced Filter under the Data tab. I want to filter a range of cells, leaving only unique entries, but I want the values of the deleted entries to be aggregated under the one remaining unique entry. "Juggernaut" wrote: Thanks Jim. I will try and describe my source data better. The data looks like this: Item Qty 40-001 4 40-011 5 40-028 4 40-011 3 10-012 4 40-011 2 So, as you can see, 40-011 appears in the list three times. How can I consolidate the data so there is one line item for 40-011 that displays a Qty of 10? My actual source data is about 15,000 rows long, so you can see how it would be tedious to try and find all the occurences of 40-011 and then doing the math to total them under one line. "Jim Thomlinson" wrote: Kinda hard to say without a bit more info on wht your source data looks like but I might be inclined to use a pivot table to combine the data... Data - Pivot table - Follow the wizard... -- HTH... Jim Thomlinson "Juggernaut" wrote: I have a spreadsheet that contains rows with multiple entries for the same item. How can I use a filter or a function to combine the values for the like items in to one row? Example: In the same chart, item number 40-011 appears three times with quantities of 5, 3, and 2. How can I filter the data so there is one occurence of 40-011 with a quantity of 10? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining rows of like data?
=SUMPRODUCT(--(A2:A7="40-011"),B2:B7)
Adjuct range to suit. Note: SUMPRODUCT function does not work if you use a whole columns "Juggernaut" wrote: I have a spreadsheet that contains rows with multiple entries for the same item. How can I use a filter or a function to combine the values for the like items in to one row? Example: In the same chart, item number 40-011 appears three times with quantities of 5, 3, and 2. How can I filter the data so there is one occurence of 40-011 with a quantity of 10? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting all rows of data that have a value in a particular column | Excel Discussion (Misc queries) | |||
Data: select a cell x rows below the current, where x is designate | Excel Discussion (Misc queries) | |||
Excel: How to choose data on two separate rows in the same column | Excel Worksheet Functions | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Removing blank rows in a worksheet | Excel Worksheet Functions |