Comparing cells

[Browny]

I have 'company name' in A1:A100 & 'invoice number' in C1:100 and ongoing.
I wish to highlite (conditional format) the company name & invoice number if
they have been duplicated. I almost paid someone twice.

Many thanks

Browny

 

[alderran]

You should put this data into an Acess database. You could make the company
name and the invoice number a key and allow no duplicates. Spreadsheets are
definitely not good databases.

 

[Browny]

Thanks Alderran
I agree with the data base. However it's a company spreadsheet. Too much
data to change now.
i have many invoices from the same companies but i want to verify they only
use the 1 invoice number. Can you help?

 

[Joerg Mochikun]

Hi Browny,

use this conditional formatting formula for C1 and then copy format down to
C100:
=SUMPRODUCT((($A$1:$A$100)&($C$1:$C$100)=A1&C1)*1)>1

This will highlight cells in column C if the invoice number and the company
name in column A appear more than once. If you also want to highlight
company names, you could apply the same conditional format for column A.

Cheers,
Joerg Mochikun

 

[Browny]

Hi Joerg

I think you'r on the right track, but it didn't work

 

[Joerg Mochikun]

What exactly didn't work? I tested it and think it's OK.
JM

 

[Browny]

I have copied another entry to test the format
I typed in as you have and the conditional format didn't highlite the cell
with the duplication.

 

[Joerg Mochikun]

OK, let's keep it simple. Check the conditional format for C2. If you copied
correctly it should read
=SUMPRODUCT((($A$1:$A$100)&($C$1:$C$100)=A2&C2)*1)>1

Now C2 should highlight (you defined a background colour for this format,
right?) if A1, C1 and A2,C2 are duplicates.

JM