Excel 2007 : Finding, pairing and preventing duplicates

**valentineumber** · 06-13-2012, 04:53 PM

Hello everyone,

I have a complicated excel problem that I need help with please.

I have a spreadsheet with multiple columns and rows of information. Basically what I want to do is to check that no row contains exactly the same information, so what I have done is that I created and extra column that generates a sort of unique "code" for every row by putting all the information for the other columns together in one cell. For example, if the product number is 3, the color is red, and the price is 100$, the code generated is 3red100. Then I did Conditional formatting and highlighted the duplicates. So far all is well, but I can't figure out how to do what I want next, which is:
- Have an alert window pop up and tell me on what row I can find the duplicate when a value that has been generated already exists
- Make it impossible to save a row that is a duplicate onto the spreadsheet

Thank you in advance!

**FDibbins** · 06-13-2012, 05:50 PM

you could add another helper column that uses a vlookup (with some absoluting) to check for duplicates, and then use match() in a "checking" cell that will tell you the row number of the duplicate?

if uou upload a sample, i can help set something up for you?

**valentineumber** · 06-13-2012, 07:13 PM

Thank you for the tip!

Though I'm not sure how I would go about doing that, One column with match and one with vlookup?
I have attached a sample of the spreadsheet If you don't mind helping me some more.

**FDibbins** · 06-13-2012, 07:41 PM

in T2, put this formula, and then copy down...
=IF(ISERROR(MATCH(E2,E3:$E$11,0)),"",MATCH(E2,E3:$E$11,0)+ROW(A2))

**valentineumber** · 06-14-2012, 01:44 PM

Thank you! It's working well for the top rows, but the bottom one is showing a row that doesn't have any information in it. What do you recommend I do to fix that?