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

Question:

Question: 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

Reply
Question marked as Solved
Answer:
Answer:

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.

User uploaded file

Step 2. Formula in G2 (and Fill Down)

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

User uploaded file

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

Page content loaded

Question marked as Solved

Mar 17, 2018 5:34 AM in response to georgieveyccan In response to georgieveyccan

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.

User uploaded file

Step 2. Formula in G2 (and Fill Down)

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

User uploaded file

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

Reply Helpful (1)

Mar 17, 2018 5:57 AM in response to georgieveyccan In response to georgieveyccan

Hi Georgie,


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

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

User uploaded file

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

User uploaded file

Happy Numbering!

Regards,

Ian.

Mar 17, 2018 5:57 AM

Reply Helpful
User profile for user: georgieveyccan

Question: Comparing values in two columns for duplicates