Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find double record and merge
Hi
I am using excel 2003. I have 3 columns: ProductID, ProductName , Units Ordered. I want to lookup double records in de column "Product ID". Then I want to merge the double records in the column "Product ID", but he must add the number of the record in the column Units Ordered. Looks like this Product ID Product Name UnitsOrdered 43301 Campylosel (20 boîtes gelose) 1 43301 Campylosel (20 boîtes gelose) 8 I must look like this Product ID Product Name UnitsOrdered 43301 Campylosel (20 boîtes gelose) 9 Does anyone have an idea how I can do this automatically? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find double record and merge
A Pivot Table would add up all the unique entries for each product.
Use "Product Name" as your row field and "UnitsOrdered" as your data item. HTH, JP On Mar 6, 5:22*am, Chris wrote: Hi I am using excel 2003. I have 3 columns: ProductID, ProductName , Units Ordered. I want to lookup double records in de column "Product ID". Then I want to merge the double records in the column "Product ID", but he must add the number of the record in the column Units Ordered. Looks like this Product ID * * *Product Name * * * * * * * * * UnitsOrdered 43301 * Campylosel (20 boîtes gelose) *1 43301 * Campylosel *(20 boîtes gelose) 8 I must look like this Product ID * * *Product Name * * * * * * * * * UnitsOrdered 43301 * Campylosel (20 boîtes gelose) *9 Does anyone have an idea how I can do this automatically? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find double record and merge
Chris,
Say you table was in column A, B & C and looked like this 1234 This thing 2 12324 That thing 3 1234 Another 5 43301 Campylosel (20 boîtes gelose) 1 1234 Widgets 5 43301 Campylosel (20 boîtes gelose) 8 In D1 enter wat you are looking for e.e 43301 In E1 enter the text - Campylosel (20 boîtes gelose) In F1 the formula =SUMPRODUCT((A1:A30=D1)*(B1:B30=E1)*(C1:C30)) Will return 9 Mike "Chris" wrote: Hi I am using excel 2003. I have 3 columns: ProductID, ProductName , Units Ordered. I want to lookup double records in de column "Product ID". Then I want to merge the double records in the column "Product ID", but he must add the number of the record in the column Units Ordered. Looks like this Product ID Product Name UnitsOrdered 43301 Campylosel (20 boîtes gelose) 1 43301 Campylosel (20 boîtes gelose) 8 I must look like this Product ID Product Name UnitsOrdered 43301 Campylosel (20 boîtes gelose) 9 Does anyone have an idea how I can do this automatically? Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find double record and merge
Thanks Mike, but I would have to do that for more then 400 records. I would
like it to be more simple. "Mike H" wrote: Chris, Say you table was in column A, B & C and looked like this 1234 This thing 2 12324 That thing 3 1234 Another 5 43301 Campylosel (20 boîtes gelose) 1 1234 Widgets 5 43301 Campylosel (20 boîtes gelose) 8 In D1 enter wat you are looking for e.e 43301 In E1 enter the text - Campylosel (20 boîtes gelose) In F1 the formula =SUMPRODUCT((A1:A30=D1)*(B1:B30=E1)*(C1:C30)) Will return 9 Mike "Chris" wrote: Hi I am using excel 2003. I have 3 columns: ProductID, ProductName , Units Ordered. I want to lookup double records in de column "Product ID". Then I want to merge the double records in the column "Product ID", but he must add the number of the record in the column Units Ordered. Looks like this Product ID Product Name UnitsOrdered 43301 Campylosel (20 boîtes gelose) 1 43301 Campylosel (20 boîtes gelose) 8 I must look like this Product ID Product Name UnitsOrdered 43301 Campylosel (20 boîtes gelose) 9 Does anyone have an idea how I can do this automatically? Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find double record and merge
Hi JP,
Thank you for your idea, I will try it out "JP" wrote: A Pivot Table would add up all the unique entries for each product. Use "Product Name" as your row field and "UnitsOrdered" as your data item. HTH, JP On Mar 6, 5:22 am, Chris wrote: Hi I am using excel 2003. I have 3 columns: ProductID, ProductName , Units Ordered. I want to lookup double records in de column "Product ID". Then I want to merge the double records in the column "Product ID", but he must add the number of the record in the column Units Ordered. Looks like this Product ID Product Name UnitsOrdered 43301 Campylosel (20 boîtes gelose) 1 43301 Campylosel (20 boîtes gelose) 8 I must look like this Product ID Product Name UnitsOrdered 43301 Campylosel (20 boîtes gelose) 9 Does anyone have an idea how I can do this automatically? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mail Merge <<Next Record | Excel Discussion (Misc queries) | |||
Excel hyperlink to specific word mail merge record | Links and Linking in Excel | |||
Excel hyperlink to specific word mail merge record | Excel Discussion (Misc queries) | |||
How to merge records into one record by customer's name? | New Users to Excel | |||
Different graphic for each record in mail merge document | Charts and Charting in Excel |