I have four columns of data which I need to compare. For example:
| Slip No | Amount | Slip No | Amount | Duplicate |
|---|---|---|---|---|
| 1 | 100 | 4 | 400 | Duplicate |
| 2 | 200 | 5 | 500 | |
| 3 | 300 | 6 | 600 | |
| 4 | 400 | 1 | 100 | Duplicate |
That is if BOTH Column 1 (Slip No) and Column 2 (Amount) together is repeated in Column 3 and Column 4, the last column should show - Duplicate.
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.
There are no replies.
Comparing values in two columns for duplicates
