Complex Match

[Vic]

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.

 

[Vic]

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.

 

[T. Valko]

"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 ?

 

[Jacob Skaria]

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