Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex Match
I have 2 worksheets: "Main" & "Other". Both have 2 columns.
"Main" & "Other" have identical Column A - Invoice number 10 positions (positions 5, 6 and 7 will be used for the match). "Main" has column B - date (format DD-MMM-YY) "Other" has column B - date (format YYYYMMDD) I need to find "Other" records missing from "Main" based on matching Column A positions (5-6-7) from both sheets and the correspoding date in Column B. If positions 5-6-7 and the date from "Main" matches the same in "Other", I don't want such records. How can this be done? Any help will be greatly appreciated. Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex Match
Here is the example:
Main: Col-A Col-B 1041073C03 7-Sep-08 1041098PRS 20-Dec-08 1067003C04 15-Mar-06 1067003C05 18-Apr-06 1123056PRS 3-Jul-07 1123056C03 16-Sep-07 Other: Col-A Col-B 1041073C02 20080907 1041098PRS 20081220 1067003C05 20060418 1067003C07 20060625 1123056C01 20070703 1123056C02 20070916 Result Should be: Col-A Col-B 1067003C07 25-Jun-06 Thank you. "Vic" wrote: I have 2 worksheets: "Main" & "Other". Both have 2 columns. "Main" & "Other" have identical Column A - Invoice number 10 positions (positions 5, 6 and 7 will be used for the match). "Main" has column B - date (format DD-MMM-YY) "Other" has column B - date (format YYYYMMDD) I need to find "Other" records missing from "Main" based on matching Column A positions (5-6-7) from both sheets and the correspoding date in Column B. If positions 5-6-7 and the date from "Main" matches the same in "Other", I don't want such records. How can this be done? Any help will be greatly appreciated. Thank you. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex Match
"Other" has column B - date (format YYYYMMDD)
1041073C02 20080907 So 20080907 is a *true Excel date* and it's just *formatted* to display as 20080907 ? -- Biff Microsoft Excel MVP "Vic" wrote in message ... Here is the example: Main: Col-A Col-B 1041073C03 7-Sep-08 1041098PRS 20-Dec-08 1067003C04 15-Mar-06 1067003C05 18-Apr-06 1123056PRS 3-Jul-07 1123056C03 16-Sep-07 Other: Col-A Col-B 1041073C02 20080907 1041098PRS 20081220 1067003C05 20060418 1067003C07 20060625 1123056C01 20070703 1123056C02 20070916 Result Should be: Col-A Col-B 1067003C07 25-Jun-06 Thank you. "Vic" wrote: I have 2 worksheets: "Main" & "Other". Both have 2 columns. "Main" & "Other" have identical Column A - Invoice number 10 positions (positions 5, 6 and 7 will be used for the match). "Main" has column B - date (format DD-MMM-YY) "Other" has column B - date (format YYYYMMDD) I need to find "Other" records missing from "Main" based on matching Column A positions (5-6-7) from both sheets and the correspoding date in Column B. If positions 5-6-7 and the date from "Main" matches the same in "Other", I don't want such records. How can this be done? Any help will be greatly appreciated. Thank you. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex Match
Try the below formula in Other sheet.. Col C cell C1
'If the date in Other sheet colB is in text format =IF(SUMPRODUCT((ISNUMBER(SEARCH(MID(A1,5,3),Main!$ A$1:$A$10))* (Main!$B$1:$B$10=DATE(LEFT(B1,4),MID(B1,5,2),RIGHT (B1,2)))))=0, "Not found","") 'If the date in Other sheet colB is in date format =IF(SUMPRODUCT((ISNUMBER(SEARCH(MID(A1,5,3),Main!$ A$1:$A$10))* (Main!$B$1:$B$10=B1)))=0,"Not found","") If this post helps click Yes --------------- Jacob Skaria "Vic" wrote: Here is the example: Main: Col-A Col-B 1041073C03 7-Sep-08 1041098PRS 20-Dec-08 1067003C04 15-Mar-06 1067003C05 18-Apr-06 1123056PRS 3-Jul-07 1123056C03 16-Sep-07 Other: Col-A Col-B 1041073C02 20080907 1041098PRS 20081220 1067003C05 20060418 1067003C07 20060625 1123056C01 20070703 1123056C02 20070916 Result Should be: Col-A Col-B 1067003C07 25-Jun-06 Thank you. "Vic" wrote: I have 2 worksheets: "Main" & "Other". Both have 2 columns. "Main" & "Other" have identical Column A - Invoice number 10 positions (positions 5, 6 and 7 will be used for the match). "Main" has column B - date (format DD-MMM-YY) "Other" has column B - date (format YYYYMMDD) I need to find "Other" records missing from "Main" based on matching Column A positions (5-6-7) from both sheets and the correspoding date in Column B. If positions 5-6-7 and the date from "Main" matches the same in "Other", I don't want such records. How can this be done? Any help will be greatly appreciated. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP/MATCH Query - quite complex....but interesting. | Excel Discussion (Misc queries) | |||
Match with Complex Lookup_array | Excel Worksheet Functions | |||
Complex Summing probably using Match at some point... | Excel Worksheet Functions | |||
Complex Index Match Help (or at least complex to me) | Excel Discussion (Misc queries) | |||
Complex LookUp / Match Problem ?? | Excel Worksheet Functions |