| 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Comparing cells
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. -- Browny "Joerg Mochikun" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Comparing cells
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 "Browny" wrote in message ... 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. -- Browny "Joerg Mochikun" wrote: 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 | |||
Linear Mode
