LOOKUP or VLOOKUP

[Guest]

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!!!

 

[Trevor Shuttleworth]

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

 

[Cesar Zapata]

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 Format>conditional 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

 

[Arvi Laanemets]

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

 

[Guest]

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!!!