Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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!!! |
#2
|
|||
|
|||
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!!! |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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!!! |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup combine vlookup with hlookup | Excel Worksheet Functions | |||
how do i get mutiple values using vlookup in excel, lookup value . | Excel Discussion (Misc queries) | |||
Vlookup & Lookup function error | Excel Worksheet Functions | |||
Problem with LOOKUP & VLOOKUP | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |