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

Slightly different tactic than Valko, but this works for me:

=IF(ISERROR(OFFSET($A$1,LARGE(NOT(((A$2:A$1274=C$2 :C$1274)+(LEFT(A$2:A$1274,7)=LEFT(C$2:C$1274,7))*( B$2:B$1274=D$2:D$1274)))*ROW(A$2:A$1274),ROWS($E$2 :E2))-1,0)),"",OFFSET($A$1,LARGE(NOT(((A$2:A$1274=C$2:C$ 1274)+(LEFT(A$2:A$1274,7)=LEFT(C$2:C$1274,7))*(B$2 :B$1274=D$2:D$1274)))*ROW(A$2:A$1274),ROWS($E$2:E2 ))-1,0))

There may be a better way to do an error check, which would reduce the size
of the formula. Like Valko's this is an array formula.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Vic" wrote:

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

Hi Luke,

I have inserted the following formula into E2 and was holding CTL+SHFT while
I hit enter. Then I selected E2 and dragged it down to E1274. Nothing was
listed.

=IF(ISERROR(OFFSET($A$2,LARGE(NOT(((A$2:A$1274=C$2 :C$962)+(LEFT(A$2:A$1274,7)=LEFT(C$2:C$962,7))*(B$ 2:B$1274=D$2:D$962)))*ROW(A$2:A$1274),ROWS($E$2:E2 ))-1,0)),"",OFFSET($A$2,LARGE(NOT(((A$2:A$1274=C$2:C$ 962)+(LEFT(A$2:A$1274,7)=LEFT(C$2:C$962,7))*(B$2:B $1274=D$2:D$962)))*ROW(A$2:A$1274),ROWS($E$2:E2))-1,0))

"Luke M" wrote:

Slightly different tactic than Valko, but this works for me:

=IF(ISERROR(OFFSET($A$1,LARGE(NOT(((A$2:A$1274=C$2 :C$1274)+(LEFT(A$2:A$1274,7)=LEFT(C$2:C$1274,7))*( B$2:B$1274=D$2:D$1274)))*ROW(A$2:A$1274),ROWS($E$2 :E2))-1,0)),"",OFFSET($A$1,LARGE(NOT(((A$2:A$1274=C$2:C$ 1274)+(LEFT(A$2:A$1274,7)=LEFT(C$2:C$1274,7))*(B$2 :B$1274=D$2:D$1274)))*ROW(A$2:A$1274),ROWS($E$2:E2 ))-1,0))

There may be a better way to do an error check, which would reduce the size
of the formula. Like Valko's this is an array formula.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Vic" wrote:

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

Couple things:
Change the anchor point of the OFFSET function back to $A$1. Needs to be in
row 1 because you're checking for row number, not relative postion in array.

Also, your array sizes need to be equal. Either make them both go to 962, or
1274. If the A column truly is longer, than all those entries past 962 should
be considered "unique" by your deifnition, and if comparing to a
blank/different value cell in column C, the formula will still pick them up.

Sorry I wasn't clear in original post.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Vic" wrote:

Hi Luke,

I have inserted the following formula into E2 and was holding CTL+SHFT while
I hit enter. Then I selected E2 and dragged it down to E1274. Nothing was
listed.

=IF(ISERROR(OFFSET($A$2,LARGE(NOT(((A$2:A$1274=C$2 :C$962)+(LEFT(A$2:A$1274,7)=LEFT(C$2:C$962,7))*(B$ 2:B$1274=D$2:D$962)))*ROW(A$2:A$1274),ROWS($E$2:E2 ))-1,0)),"",OFFSET($A$2,LARGE(NOT(((A$2:A$1274=C$2:C$ 962)+(LEFT(A$2:A$1274,7)=LEFT(C$2:C$962,7))*(B$2:B $1274=D$2:D$962)))*ROW(A$2:A$1274),ROWS($E$2:E2))-1,0))

"Luke M" wrote:

Slightly different tactic than Valko, but this works for me:

=IF(ISERROR(OFFSET($A$1,LARGE(NOT(((A$2:A$1274=C$2 :C$1274)+(LEFT(A$2:A$1274,7)=LEFT(C$2:C$1274,7))*( B$2:B$1274=D$2:D$1274)))*ROW(A$2:A$1274),ROWS($E$2 :E2))-1,0)),"",OFFSET($A$1,LARGE(NOT(((A$2:A$1274=C$2:C$ 1274)+(LEFT(A$2:A$1274,7)=LEFT(C$2:C$1274,7))*(B$2 :B$1274=D$2:D$1274)))*ROW(A$2:A$1274),ROWS($E$2:E2 ))-1,0))

There may be a better way to do an error check, which would reduce the size
of the formula. Like Valko's this is an array formula.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Vic" wrote:

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

I have adjusted the formula to this:
=IF(ISERROR(OFFSET($A$1,LARGE(NOT(((A$2:A$1274=C$2 :C$1274)+(LEFT(A$2:A$1274,7)=LEFT(C$2:C$1274,7))*( B$2:B$1274=D$2:D$1274)))*ROW(A$2:A$1274),ROWS($E$2 :E2))-1,0)),"",OFFSET($A$1,LARGE(NOT(((A$2:A$1274=C$2:C$ 1274)+(LEFT(A$2:A$1274,7)=LEFT(C$2:C$1274,7))*(B$2 :B$1274=D$2:D$1274)))*ROW(A$2:A$1274),ROWS($E$2:E2 ))-1,0))

As a result, column E lists all 1273 entries from Column A in reverse order.
No comparison is happening.

"Luke M" wrote:

Couple things:
Change the anchor point of the OFFSET function back to $A$1. Needs to be in
row 1 because you're checking for row number, not relative postion in array.

Also, your array sizes need to be equal. Either make them both go to 962, or
1274. If the A column truly is longer, than all those entries past 962 should
be considered "unique" by your deifnition, and if comparing to a
blank/different value cell in column C, the formula will still pick them up.

Sorry I wasn't clear in original post.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Vic" wrote:

Hi Luke,

I have inserted the following formula into E2 and was holding CTL+SHFT while
I hit enter. Then I selected E2 and dragged it down to E1274. Nothing was
listed.

=IF(ISERROR(OFFSET($A$2,LARGE(NOT(((A$2:A$1274=C$2 :C$962)+(LEFT(A$2:A$1274,7)=LEFT(C$2:C$962,7))*(B$ 2:B$1274=D$2:D$962)))*ROW(A$2:A$1274),ROWS($E$2:E2 ))-1,0)),"",OFFSET($A$2,LARGE(NOT(((A$2:A$1274=C$2:C$ 962)+(LEFT(A$2:A$1274,7)=LEFT(C$2:C$962,7))*(B$2:B $1274=D$2:D$962)))*ROW(A$2:A$1274),ROWS($E$2:E2))-1,0))

"Luke M" wrote:

Slightly different tactic than Valko, but this works for me:

=IF(ISERROR(OFFSET($A$1,LARGE(NOT(((A$2:A$1274=C$2 :C$1274)+(LEFT(A$2:A$1274,7)=LEFT(C$2:C$1274,7))*( B$2:B$1274=D$2:D$1274)))*ROW(A$2:A$1274),ROWS($E$2 :E2))-1,0)),"",OFFSET($A$1,LARGE(NOT(((A$2:A$1274=C$2:C$ 1274)+(LEFT(A$2:A$1274,7)=LEFT(C$2:C$1274,7))*(B$2 :B$1274=D$2:D$1274)))*ROW(A$2:A$1274),ROWS($E$2:E2 ))-1,0))

There may be a better way to do an error check, which would reduce the size
of the formula. Like Valko's this is an array formula.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Vic" wrote:

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