Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining and merging duplicate data.
I have a list of two columns consisting of part number and quantity, some of
the lines contain duplicate part numbers. Example Part Number Qty 311 10 311 5 312 20 313 5 313 5 Is there any way I can merge the duplicate part numbers into one line whilst combining the quantites to read as follows? Part Number Qty 311 15 312 20 313 10 Have a very large database to work through and this would make my life a lot easier :0) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining and merging duplicate data.
Darren,
A couple of steps. 1. Create your unique list of parts. Select your parts list, the top row must be a header, then Data|Filter|Advanced filter Select copy to another location Check unique items only Enter a location to copy to (a single cell) I'm going to use D1 Click OK and you should now have a list of unique items. 2. This in E1 and drag down to get your totals =SUMIF($A$1:$A$5,D1,$B$1:$B$5) Mike "Darren" wrote: I have a list of two columns consisting of part number and quantity, some of the lines contain duplicate part numbers. Example Part Number Qty 311 10 311 5 312 20 313 5 313 5 Is there any way I can merge the duplicate part numbers into one line whilst combining the quantites to read as follows? Part Number Qty 311 15 312 20 313 10 Have a very large database to work through and this would make my life a lot easier :0) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining and merging duplicate data.
Cheers for the advice Mike,
I'm not too hot with using excel but whenever I try to use the advanced filter option it doesn't seem to work. I highlight the columns and go through the advance filter options but end up with the same list again minus the first row under the headers. Bit stuck at the moment. "Mike H" wrote: Darren, A couple of steps. 1. Create your unique list of parts. Select your parts list, the top row must be a header, then Data|Filter|Advanced filter Select copy to another location Check unique items only Enter a location to copy to (a single cell) I'm going to use D1 Click OK and you should now have a list of unique items. 2. This in E1 and drag down to get your totals =SUMIF($A$1:$A$5,D1,$B$1:$B$5) Mike "Darren" wrote: I have a list of two columns consisting of part number and quantity, some of the lines contain duplicate part numbers. Example Part Number Qty 311 10 311 5 312 20 313 5 313 5 Is there any way I can merge the duplicate part numbers into one line whilst combining the quantites to read as follows? Part Number Qty 311 15 312 20 313 10 Have a very large database to work through and this would make my life a lot easier :0) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging/Combining workbook data | Excel Discussion (Misc queries) | |||
Combining duplicate row data | Excel Worksheet Functions | |||
Combining and merging lines | Excel Discussion (Misc queries) | |||
Combining/Merging Rows | Excel Discussion (Misc queries) | |||
Merging data in multiple rows where the first cell has duplicate d | Excel Discussion (Misc queries) |