Data association

[Guest]

Is it possible to do data association in excel?
Here is a copy of my data extracted from 2 tables:

Order No Order QTY DO QTY
CO1 1000 200
CO1 1000 100
CO2 300 10
CO2 300 5
CO2 300 20

The actual order qty for CO1 =1000 and CO2=300. I would like to associate
order quantity to order number so it is not duplicate in total. Thanks.

 

[Guest]

Here's one guess ..

Assume this table is in Sheet1, cols A to C, data from row2 down

Order No Order QTY DO QTY
CO1 1000 200
CO1 1000 100
CO2 300 10
CO2 300 5
CO2 300 20

Using 2 empty cols to the right, say cols E and F

Put in E2: =A2&B2
Put in F2: =IF(E2="","",IF(COUNTIF($E$2:E2,E2)>1,"",ROW()))

Select E2:F2, fill down to say F1000 to cover the max expected number of
rows that data is expected in the table

Cols E and F are helper columns: col E will create concat strings to
identify the Order No and Qty as one entity, col F will tag and assign
arbitrary row numbers to unique items in col E. These 2 cols will be read by
formulas we're going to put in Sheet2.

In Sheet2
---------
Paste the same headers into A1:C1

Order No Order QTY DO QTY

Put in A2:
=IF(ISERROR(SMALL(Sheet1!$F:$F,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$F:$F,ROWS($A$1:A1)),Sheet1!$F:$F,0)))

Copy A2 to B2

Put in C2:
=IF(OR(A2="",B2=""),"",SUMIF(Sheet1!E:E,A2&B2,Sheet1!C:C))

Select A2:C2, fill down to C1000
(fill down by the same number of rows that was catered for in Sheet1)

For the sample data in Sheet1, you'll get the results:

Order No Order QTY DO QTY
CO1 1000 300
CO2 300 35

( rest are blanks: "" )

Cols A and B will extract the only the unique "Order No - Order Qty"
associations / entities and col C will compute the total for each of these
unique "Order No - Order Qty" entities from Sheet1

Hope the above is what you're after ..

 

[Herbert Seidenberg]

Here is a way to get the same results as Max without VBA
1. Tools | Options | General | R1C1 ref style
2. Insert a column between Order QTY and DO QTY. Label it Accum.
3. Enter this formula into Accum:
IF(AND(RC1=R[1]C1,RC1=R[-1]C1),TEXT(R[-1]C+RC[1],0),
IF(RC1=R[1]C1,TEXT(RC[1],0),RC[1]+R[-1]C))
and fill down.
4. Select Accum column | Copy | Paste Special | Values
5. Delete DO QTY column (optional)
6. Select entire data array | Sort/by Accum |OK | Sort numbers and text
separately | OK
7. Delete lower portion of data array that has text numbers in Accum
(optional)

 

[Max]

An interesting suggestion for the OP, Herbert ..

6. Select entire data array | Sort/by Accum |OK |
Sort numbers and text separately | OK

Think this option below (of step 6) isn't found in xl97 (my ver):
... | Sort numbers and text separately | OK

Guess it's something available only in higher versions ?
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Here is a way to get the same results as Max without VBA
1. Tools | Options | General | R1C1 ref style
2. Insert a column between Order QTY and DO QTY. Label it Accum.
3. Enter this formula into Accum:
IF(AND(RC1=R[1]C1,RC1=R[-1]C1),TEXT(R[-1]C+RC[1],0),
IF(RC1=R[1]C1,TEXT(RC[1],0),RC[1]+R[-1]C))
and fill down.
4. Select Accum column | Copy | Paste Special | Values
5. Delete DO QTY column (optional)
7. Delete lower portion of data array that has text numbers in Accum
(optional)

 

[Guest]

Hi Max and Herbert,

Thank you so much for the advise. I tried and it works but I was wondering
wheather it is possible to have the end result like below:

Order No Order QTY DO QTY
CO1 1000 200
CO1 0 100
CO2 300 10
CO2 0 5
CO2 0 20

Thks & Rgds,
Jeffrey

 

[Max]

Aha, so *that's* what you want <g>

Let's try this:

Assume this table is in Sheet1, cols A to C, data from row2 down

Order No Order QTY DO QTY
CO1 1000 200
CO1 1000 100
CO2 300 10
CO2 300 5
CO2 300 20

Using 2 empty cols to the right, say cols E and F

Put in E2: =A2&B2

Put in F2:

=IF(E2="","",IF(AND(COUNTIF($E$2:E2,E2)>1,COUNTIF($B$2:B2,B2)>1),0,ROW()))

Select E2:F2, fill down to say F1000 to cover the max number of rows that
data is expected in the table (can copy down ahead of expected data)

In Sheet2
---------
Paste the same headers into A1:C1

Order No Order QTY DO QTY

Put in A2: =IF(Sheet1!A2="","",Sheet1!A2)

Put in B2:
=IF(Sheet1!F2="","",IF(Sheet1!F2<>0,Sheet1!B2,Sheet1!F2))

Put in C2: =IF(Sheet1!C2="","",Sheet1!C2)

Select A2:C2, fill down to C1000
(fill down by the same number of rows that was catered for in Sheet1)

For the sample data in Sheet1,
you'll get the desired results:

Order No Order QTY DO QTY
CO1 1000 200
CO1 0 100
CO2 300 10
CO2 0 5
CO2 0 20

( rest are blanks: "" )

Note that the above is now *conditional* on the Order No and Order Qty being
grouped together in the source table in Sheet1 (as per the sample posted)