| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing cells
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Comparing cells
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" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Comparing cells
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? -- Browny "alderran" wrote: 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" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Comparing cells
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" wrote in message ... 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? -- Browny "alderran" wrote: 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" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Comparing cells
Hi Joerg
I think you'r on the right track, but it didn't work -- Browny "Joerg Mochikun" wrote: 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" wrote in message ... 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? -- Browny "alderran" wrote: 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" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Comparing cells
What exactly didn't work? I tested it and think it's OK.
JM "Browny" wrote in message ... Hi Joerg I think you'r on the right track, but it didn't work -- Browny "Joerg Mochikun" wrote: 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" wrote in message ... 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? -- Browny "alderran" wrote: 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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need Help Comparing Cells | Excel Discussion (Misc queries) | |||
| Comparing cells when they are not exactly the same | Excel Discussion (Misc queries) | |||
| comparing cells | Excel Worksheet Functions | |||
| Comparing Cells | Excel Worksheet Functions | |||
| Comparing Cells | Excel Worksheet Functions | |||
Hybrid Mode
