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.

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.

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.

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.