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!
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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Newuser" <[email protected]> wrote in message
news:[email protected]...
> 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!
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!
I think you need to creat the list per Max's formula. To ensure you have all customers, I suggest using filter, advanced filter, unique records on the column of customers and place this list somewhere, and then use Max's formula.
not a professional, just trying to assist.....
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 <at>yahoo<dot>com
----
"Newuser" <[email protected]> wrote in message
news:[email protected]...
> 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!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks