Delete Common Cell Values

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I have Excel 2003. Columns A and B have invoice numbers from different time
periods. I need a formula that will look in both columns and delete the
invoice numbers that appear in both columns A and B. Any help is appreciated.
 
Mike,

Use two columns of formulas. The first

=NOT(ISERROR(MATCH(A2,B:B,False)))

The second:

=NOT(ISERROR(MATCH(B2,A:A,False)))

Copy to match your columns, then copy both columns of formulas and paste special as values.

Then sort all columns based on the first column of formulas-converted-to-values, and delete the
values in column A where the value is TRUE.
Then sort all columns based on the second column of formulas-converted-to-values, and delete the
values in column B where that value is TRUE.

Then delete your two new columns, and resort your data table any which way you want.

HTH,
Bernie
MS Excel MVP
 
Thanks. Worked great.
--
Mike


Bernie Deitrick said:
Mike,

Use two columns of formulas. The first

=NOT(ISERROR(MATCH(A2,B:B,False)))

The second:

=NOT(ISERROR(MATCH(B2,A:A,False)))

Copy to match your columns, then copy both columns of formulas and paste special as values.

Then sort all columns based on the first column of formulas-converted-to-values, and delete the
values in column A where the value is TRUE.
Then sort all columns based on the second column of formulas-converted-to-values, and delete the
values in column B where that value is TRUE.

Then delete your two new columns, and resort your data table any which way you want.

HTH,
Bernie
MS Excel MVP
Click to expand...
 
Back
Top