Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching Columns - very complex
Column A and column C are both 10 positions long.
I need to create list E with all entries in A that are not in C based on the following conditions: If A and C completely match then drop them - I don't want them. If they don't match then do this: If first 7 positions of A and first 7 positions C match then check corresponding date fields (date B corresponds to A and date D corresponds to C). If these dates are the same then Drop A and B - I don't want them. I need to list unique A's that don't match with C's. Sometimes they look a little different but the dates are the same - this indicates that they are not unique. A2=1041073PRS B2=01/01/2009 C2=1041073PRS D2=01/02/2009 - I don't want to list this - it's a match (A=C) A3=1041073C03 B3=01/01/2009 C3=1041073C02 D3=01/01/2009 - I don't want to list this - it's a match (7-digits A = 7-digits C and dates match) A4=1041073C04 B4=01/15/2009 C4=1041073C05 D4=01/17/2009 - I want this one - there is no complete 10 digit match, the first 7 digits match but the dates are different. The date of the same invoice is the same. However, the invoice may be considered C03 even if it was issued 1 day before C03 started (thus putting it into C02 range). Thanks to T. Valko, I have a formula to exclude a complete 10-digit match: =IF(ROWS(E$2:E2)<=SUM(--ISNA(MATCH(A$2:A$1274,C$2:C$1003,0))),INDEX(A$2:A$ 1274,SMALL(IF(ISNA(MATCH(A$2:A$1274,C$2:C$1003,0)) ,ROW(A$2:A$1274)),ROWS(E$2:E2))-MIN(ROW(A$2:A$1274))+1),"") Can some please modify this folmula? Thank you |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex Matching | Excel Discussion (Misc queries) | |||
Complex Matching | Excel Discussion (Misc queries) | |||
matching on columns | Excel Discussion (Misc queries) | |||
Hide Rows / Columns - complex question | Excel Worksheet Functions | |||
Want to combine columns and horizontal lines in complex chart - Can it be Done?! | Charts and Charting in Excel |