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

Tech-Archive recommends: Fix windows errors by optimizing your registry

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 <at>yahoo<dot>com
----
"Newuser" <Newuser@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C3E01DC6-2622-4F5D-830A-F5353A7C0B22@xxxxxxxxxxxxxxxx
> 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!


.

Quantcast