Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question:

# Question:Q:Comparing values in two columns for duplicates

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.

Posted on

Question marked as Solved

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.

Posted on

Question marked as Solved

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.

Mar 17, 2018 5:34 AM

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.

Mar 17, 2018 5:57 AM

User profile for user: georgieveyccan

Question: Comparing values in two columns for duplicates