How to merge records into one record by customer's name?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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.
 
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!
 
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 ...
 
Back
Top