LOOKUP or VLOOKUP
 

Hello! I have a very large spreadsheet and I need to search it for a
duplicate invoice numbers. I am entering invoice numbers in the first seven
columns. Can you tell me the easiest way to check the first seven columns
for for duplicate numbers? Thanks for your help!!!

Bennie

select columns A to G. Now Select Format | Conditional formatting... and
input:

Formula is: =COUNTIF($A:$G,E4)1

Pick a suitable format, perhaps a yellow background and press OK

You might be better selecting a limited range rather than all of columns A
to G as this could be very slow.

Perhaps Formula is: =COUNTIF($A1:$G1000,E4)1

Regards

Trevor


"Bennie" wrote in message
...
Hello! I have a very large spreadsheet and I need to search it for a
duplicate invoice numbers. I am entering invoice numbers in the first
seven
columns. Can you tell me the easiest way to check the first seven columns
for for duplicate numbers? Thanks for your help!!!

I hope I understood your question. You can Highlicght duplicated items
with conditional formatting. Lets say your data is in A1:C5

Select your range then go to Formatconditional formatting then select
Value is and change it to "Formula Is".

then type this =IF(COUNTIF($A$1:$C$5,A1)1,TRUE,FALSE)

click on format button and select the colors you like to hightlight the
Dups.

You can find more info here
http://www.cpearson.com/excel/duplicat.htm

Hi

When you want to find all occurrences of entries, starting from second (i.e.
you mark out 2nd, 3rd, etc. occurrence of incoice number in table), then
select the range A1:Gx (x is at least the number of rows in your table),
from Format menu select conditional formatting, like in Cezar's response set
'Formula is', and into formula field enter
=(COUNTIF($A$1:A1,A1)1)
After that set the cell format for case the formula returns TRUE, and click
on OK.


Arvi Laanemets


"Bennie" wrote in message
...
Hello! I have a very large spreadsheet and I need to search it for a
duplicate invoice numbers. I am entering invoice numbers in the first
seven
columns. Can you tell me the easiest way to check the first seven columns
for for duplicate numbers? Thanks for your help!!!

This worked perfect...I do have another column on another worksheet within
this spreadsheet, is it possible to incoporate another worksheet column into
this formula? Thanks for everything!!!

"Cesar Zapata" wrote:

I hope I understood your question. You can Highlicght duplicated items
with conditional formatting. Lets say your data is in A1:C5

Select your range then go to Formatconditional formatting then select
Value is and change it to "Formula Is".

then type this =IF(COUNTIF($A$1:$C$5,A1)1,TRUE,FALSE)

click on format button and select the colors you like to hightlight the
Dups.

You can find more info here
http://www.cpearson.com/excel/duplicat.htm