Comparing values in two columns for duplicates

Hi Georgie,

I made Row 1 a Header Row (it didn't copy as a Header Row from your post). That simplifies the formula in G and ensures that the formula automatically extends as you add more rows.

Step 1, add extra columns (C and F) to combine Slip No and Amount, using the & (concatenate) operator.

Step 2. Formula in G2 (and Fill Down)

=INDEX(C,MATCH(F2,C,0))

My cheat sheet for INDEX and Match:

=INDEX(Column I want a return value from,

MATCH(My Lookup Value,

Column I want to Lookup against,

Enter “0″ ))

Where there is a duplicate, it finds it. Where no duplicate exists, it throws a red error triangle. We can develop this further to make it look more "professional".

Regards,

Ian.

Hi Georgie,

Happy to help, and thanks for the green tick and your feedback.

To make this look more professional, add another column (H)

Formula in H2 (and Fill Down)

=IFERROR(IF(LEN(G2)>1,"Duplicate",""),"")

The workhorse formula is in bold

If LEN (length) of a G cell is greater than 1, then insert "Duplicate", else insert "" (NULL)

Wrapped around that workhorse is IFERROR that inserts "" (NULL) if a G cell contains a red error triangle.

Now, to tidy up, hide columns C, F and G

Happy Numbering!

Regards,

Ian.