LOOKUP or VLOOKUP

[Bennie]

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


"Bennie" <[email protected]> wrote in message
news:[email protected]...
> 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!!!

[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


"Bennie" <[email protected]> wrote in message
news:[email protected]...
> 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]

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