Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to merge records into one record by customer's name?
How can I merge repeating records of a customer who has different oders of
products? Ist col, Company name, 2nd col cust name, 3rd-10th col Product items Thks, I had tried the help on consolidate, could not fiigure it out. Thks! |
#2
|
|||
|
|||
One way to try ..
Assume you have in Sheet1, in A1:E5, the table: Comp Cust Prd#1 Prd#2 Prd#3 ABC XXX 40 50 90 DEF YYY 30 50 20 ABC XXX 50 70 70 DEF YYY 50 30 20 In Sheet2, you have the "master" table below in A1:E2 Comp Cust Prd#1 Prd#2 Prd#3 ABC XXX DEF YYY Put in C2: =SUMPRODUCT((Sheet1!$A$2:$A$5=$A2)*(Sheet1!$B$2:$B $5=$B2),Sheet1!C$2:C$5) Copy C2 across to E2, fill down to E3 For the sample data in Sheet1, you'll get the consolidated total orders for the products: Comp Cust Prd#1 Prd#2 Prd#3 ABC XXX 90 120 160 DEF YYY 80 80 40 Adapt the ranges to suit, but note that you can't use entire col references (e.g.: A:A, B:B, etc) in SUMPRODUCT. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Newuser" wrote in message ... How can I merge repeating records of a customer who has different oders of products? Ist col, Company name, 2nd col cust name, 3rd-10th col Product items Thks, I had tried the help on consolidate, could not fiigure it out. Thks! |
#3
|
|||
|
|||
Thks Max, I have a difficult problem here as the records goes into thousands,
and I cannot afford to do it manual selection for fear of missing out one record( I had already sorted them by Company and Customer). Is there a better method using If cust=same value then add count? Thks! |
#4
|
|||
|
|||
Assuming the same set-up as outlined in my earlier response, this should
help extract all the unique Comp - Cust from Sheet1 into Sheet2, i.e. auto-produce the "master" table (I figure this is what you're after as well ?) In Sheet1 ------------ Using 2 empty cols to the right, say, cols O & P ? Put in O2: =A2&"_"&B2 Put in P2: =IF(COUNTIF($O$2:O2,O2)1,"",ROW()) Select O2:P2 and fill down to say, P2000 to cover the data in the table In Sheet2 ------------ Put in A2: =IF(ISERROR(SMALL(Sheet1!$P:$P,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA LL(Sheet1!$P:$P,ROWS($A$1:A1)),Sheet1!$P:$P,0))) Copy A2 across to B2, fill down to B2000 (cover the same range as in Sheet1) Based on the sample data in Sheet1, what you'll get in Sheet2's cols A and B would be: Comp Cust ABC XXX DEF YYY (blank rows below) Then just fill in the SUMPRODUCT formulas for the Prd#1 .. #10 into cols C to L, viz. Put in C2: =SUMPRODUCT((Sheet1!$A$2:$A$2000=$A2)*(Sheet1!$B$2 :$B$2000=$B2),Sheet1!C$2:C $2000) Copy C2 across to L2, fill down to L2000* [*or just fill down until the last row of data in cols A and B, to minimize unnecessary calcs/recalcs. SUMPRODUCT does slow things down quite a bit. But you have to remember to extend the formulas further down as may be required, should you refresh the data in Sheet1 subsequently.] Adapt to suit ... -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Newuser" wrote in message ... Thks Max, I have a difficult problem here as the records goes into thousands, and I cannot afford to do it manual selection for fear of missing out one record( I had already sorted them by Company and Customer). Is there a better method using If cust=same value then add count? Thks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Different graphic for each record in mail merge document | Charts and Charting in Excel | |||
Can I merge workbooks in Excel with fewer records in 2nd workbook? | Excel Discussion (Misc queries) | |||
deleting duplicate records in a mail merge | Excel Discussion (Misc queries) | |||
How to sort/update large excel db | Excel Discussion (Misc queries) | |||
How to delete duplicate records when I merge two lists (deleting . | Excel Worksheet Functions |