excel 2007 duplicate row

How can I compare records in a table, to make sure these records are not duplicates? Using excel 2007 I don't won’t them to delete after comparison开发者_如何学C. Duplicates rows should be colored. I have a table columns are from A to P and I have 500 rows. I want to put condition on A, B, E, F, G, I.

---

If you don't want to sort your column, you can try with a matrix formula (http://www.stanford.edu/~wfsharpe/mia/mat/mia_mat4.htm).

Practically, you can compare your current row to every row above. Somtething like :

=MIN(LINE(B1)*(IF(A2=A1;1;0))*(IF(B2=B1;1;0)))*(...)

validated with CTRL-SHIFT-ENTER will check if all the conditions are true, else, will return 0.

Please send a file (with anonymous data) if you want a practical example.

Hope that helps

Edit : here is the good solution (provided you want to compare data in the Q column) :

=MIN(LIGNE($Q$5:Q6)*EQUIV(Q6;$Q$5:Q6;0))

If you want to have the first line where the value appear

=MIN(LIGNE($Q$5:Q5)*EQUIV(Q6;$Q$5:Q5;0))

If you'd rather have #N/A if there are no duplicate before that line

Still validate with CTRL-SHIFT-ENTER

---

Sort by the columns you are interested in then use a formula to compare each row with the one above. You can then use conditional formatting to colour the results.

---

I may sound stupid here, but usually the simple answers are usually the best. I did this recently, by literally using the CONCATENATE() function with the TEXT() function to combine all the columns I wanted to compare into a single cell. So in effect I am creating a cell with a unique "key" that holds all the data I want to be unique. I then sort that column and create another empty column next to it. Then us this formula to compare the row with the row above it: =IF(A2=A1,0,1) This simply puts a 0 where it's the same row and a 1 where it's different. I then filter on the '1's and there are my duplicates! It'a also usefull as an alternative way of doing a unique COUNT(DISTINCT ...) where I want to count how many unique references of my data exists. SUBTOTAL(3...) is not enough.