Help comparing data and returning information...

[Guest]

I am attempting to create a spreadsheet that can be used to show if an order
is complete. Some orders contain only one line and a simple IF statement
tells me wheter or not a each line is complete, =IF(QTY ORDERED=QTY
WRAPPED,"READY","NOT READY") but I am stumped in trying to compare multiple
lines on one order.

For example:

Order 123456 Line 1 QTY ORDERED 5 QTY WRAPPED 5 STATUS READY
Order 123456 Line 2 QTY ORDERED 4 QTY WRAPPED 3 STATUS NOT READY
Order 654321 Line 1 QTY ORDERED 2 QTY WRAPPED 2 STATUS READY
Order 654321 Line 2 QTY ORDERED 6 QTY WRAPPED 6 STATUS READY

In the above example, I would like to return the information in Order 654321
to another spreadsheet indicating that all lines on the the order are
complete and can be shipped.

Hopefully this makes sense and someone can help me out.

Thanks,

DC James

 

[Max]

Perhaps try this ..

Assuming the sample data is in Sheet1 cols A to H from row1 down,
where col A = Order #s,
i.e. in A1 and A2 are : Order 123456, in A3 & A4 are: Order 654321,
and col H contains
the evaluated statuses, i.e. either: READY or NOT READY

Use an empty col to the right, say col I ?

Put in I1:

=IF(OR(A1="",H1=""),"",IF(COUNTIF($A$1:$A$100,A1)=SUMPRODUCT(($A$1:$A$100=A1
)*($H$1:$H$100="READY")),ROW(),""))

Copy I1 down to I100 to cover the max expected range of data in cols A to H
(can copy down ahead of expected data input)

In Sheet2
-----------
Put in A1:

=IF(ISERROR(SMALL(Sheet1!$I:$I,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$I:$I,ROWS($A$1:A1)),Sheet1!$I:$I,0)))

Copy A1 across to H1, fill down to H100
(cover the same range as in Sheet1)

Sheet2 will return only the data for the rows from Sheet1 where all the
lines for a particular order # have status(es): READY in col H

For the sample data in Sheet1 (as per post), you'll get in Sheet2:

Order 654321 Line 1 QTY ORDERED 2 ... STATUS READY
Order 654321 Line 2 QTY ORDERED 6 ... STATUS READY
(Rest are blank [""] rows)

Adapt to suit ..