EXCEL HELP: hightlight if they are same.?

Question

I have a spreadsheet, and it will be updated manually.

I want to make it highlight the row if the database(in column),that is already exist, are entered.

lets say I have 160 rows filled with data.
I am entering new data starting row 161.

If the value F161 is same as any of F values, I want that
row highlighted.


Please help excel experts!

Answer 1

you could do an if statement like the following :

=if(vlookup(f161,$f$1:$f$160,1,false)=f161,1,0)

which means that if the value in f161 = anything from the column f rows 1-160 then it will return the value of 1 in that cell, then you can use conditional formatting to highlight all cells in that column where cells are equal to 1.
conditional formating in found under the format tab at the top

for some reason it wont show the full value within the () so here is the last part of it ...,false)=f161,1,0)

yes, conditional formating does exist. just not for rows. but, you can conditionally format the cell so that any cell in that column that meets certain criteria will be highlighted.

Answer 2

I don't think a formula which will conditionally format a cell can be written.

160 rows is not too much so you could data, use a pivot table or search/countif/vlookup function to verify no duplicate cells exist.

If you do not get a definitive response, try this site for help.

http://www.ozgrid.com/

Its free help site.

Answer 3

I think there is no way that the value of row can be campared. But u can compare it on basic of value of coloumn(cell value). But u have to put a extra coloumn for that purpose. put formula vlookup(F161,F1:F160,1,False), Now put the conditional formatting to if cell value is=extra coloumn then put extra formatting. I know this is not easy to do as i m telling. You can also contact me. If you are really desprate to it. I can make fully functional program in Excel according to your need after giving actual data.

Answer 4

You don't want to use VLOOKUP because the source data needs to be sorted. You didn't say you were sorting the data as you enter it so I assume its not sorted. Therefore, you need to use the COUNTIF function.

Since your data is in column F, use column G for this formula:
=IF (COUNTIF (F:F,B2) >1, "Duplicated" , "Unique" )

Using this formula will tell you if the new entry is unique or duplicated. You can then use conditional formatting to change the color.