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.

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,Shee t1!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 ..

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"Jeffrey" wrote:

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.

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)

An interesting suggestion for the OP, Herbert ..
(you're a "rare" breed who opts for "R1C1" <bg)

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 <atyahoo<dotcom
----
"Herbert Seidenberg" wrote in message
oups.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)

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

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,Shee t1!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)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Jeffrey" wrote in message
...
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