Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help comparing data and returning information...
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 |
#2
|
|||
|
|||
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 a 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 .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "dcjames" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning a blank for validation list | Excel Worksheet Functions | |||
Returning Data from a third cell in same row that meets two other | Excel Discussion (Misc queries) | |||
Will not calculate average/median formulas;acts like no data in c. | Excel Worksheet Functions | |||
Importing XML data from http request | Excel Discussion (Misc queries) | |||
Returning a Value to a Cell Based on a Range of Uncertain Size | Excel Worksheet Functions |