Highlight Duplicate Value, 3 Column check

N

nazeem

New Member
Joined
May 26, 2016
Messages
27
hi,

I need to find duplicate in a set of data mentioned below in which there is three columns Date, Employee Code, Amount. These 3 column we need to check in each row to identify the duplicate. In the given data 2 Lines with emp code AHP-SHJ-06-07 and AHP-SHJ-06-03 is duplicate.

[TABLE="width: 749"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Bill No.[/TD]
[TD]Emp Code[/TD]
[TD]Emp Name[/TD]
[TD]Amount[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD="align: right"]1/6/2017[/TD]
[TD="align: right"]108824[/TD]
[TD]AHP-SHJ-06-07[/TD]
[TD]ABC[/TD]
[TD="align: right"]353.00[/TD]
[TD]RESIDENCE RENEW FOR ABC[/TD]
[/TR]
[TR]
[TD="align: right"]5/6/2017[/TD]
[TD="align: right"]99374[/TD]
[TD]AHP-SHJ-06-03[/TD]
[TD]DEF[/TD]
[TD="align: right"]353.00[/TD]
[TD]RESIDENCE RENEW FOR DEF[/TD]
[/TR]
[TR]
[TD]22-6-2017[/TD]
[TD="align: right"]99385[/TD]
[TD]AHP-DXB-16-01[/TD]
[TD]GHI[/TD]
[TD="align: right"]453.00[/TD]
[TD]RESIDENCE RENEW FOR GHI[/TD]
[/TR]
[TR]
[TD]23-6-2017[/TD]
[TD="align: right"]48313[/TD]
[TD]AHP-DXB-BR-02-17[/TD]
[TD]JKL[/TD]
[TD="align: right"]453.00[/TD]
[TD]NEW RESIDENCE FOR JKL[/TD]
[/TR]
[TR]
[TD="align: right"]5/6/2017[/TD]
[TD="align: right"]99384[/TD]
[TD]AHP-SHJ-06-03[/TD]
[TD]DEF[/TD]
[TD="align: right"]353.00[/TD]
[TD]RESIDENCE RENEW FOR DEF[/TD]
[/TR]
[TR]
[TD="align: right"]4/6/2017[/TD]
[TD="align: right"]29296[/TD]
[TD]AHP-SHJ-06[/TD]
[TD]PQR[/TD]
[TD="align: right"]90.00[/TD]
[TD]APPLIED PROFESSION CHANGE IN PQR[/TD]
[/TR]
[TR]
[TD="align: right"]1/6/2017[/TD]
[TD="align: right"]31427[/TD]
[TD]AHP-SHJ-06-07[/TD]
[TD]ABC[/TD]
[TD="align: right"]353.00[/TD]
[TD]ID FOR MOHAMMED ABC

[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
nazeem said:
hi,

I need to find duplicate in a set of data mentioned below in which there is three columns Date, Employee Code, Amount. These 3 column we need to check in each row to identify the duplicate. In the given data 2 Lines with emp code AHP-SHJ-06-07 and AHP-SHJ-06-03 is duplicate.

[TABLE="width: 749"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Bill No.[/TD]
[TD]Emp Code[/TD]
[TD]Emp Name[/TD]
[TD]Amount[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD="align: right"]1/6/2017[/TD]
[TD="align: right"]108824[/TD]
[TD]AHP-SHJ-06-07[/TD]
[TD]ABC[/TD]
[TD="align: right"]353.00[/TD]
[TD]RESIDENCE RENEW FOR ABC[/TD]
[/TR]
[TR]
[TD="align: right"]5/6/2017[/TD]
[TD="align: right"]99374[/TD]
[TD]AHP-SHJ-06-03[/TD]
[TD]DEF[/TD]
[TD="align: right"]353.00[/TD]
[TD]RESIDENCE RENEW FOR DEF[/TD]
[/TR]
[TR]
[TD]22-6-2017[/TD]
[TD="align: right"]99385[/TD]
[TD]AHP-DXB-16-01[/TD]
[TD]GHI[/TD]
[TD="align: right"]453.00[/TD]
[TD]RESIDENCE RENEW FOR GHI[/TD]
[/TR]
[TR]
[TD]23-6-2017[/TD]
[TD="align: right"]48313[/TD]
[TD]AHP-DXB-BR-02-17[/TD]
[TD]JKL[/TD]
[TD="align: right"]453.00[/TD]
[TD]NEW RESIDENCE FOR JKL[/TD]
[/TR]
[TR]
[TD="align: right"]5/6/2017[/TD]
[TD="align: right"]99384[/TD]
[TD]AHP-SHJ-06-03[/TD]
[TD]DEF[/TD]
[TD="align: right"]353.00[/TD]
[TD]RESIDENCE RENEW FOR DEF[/TD]
[/TR]
[TR]
[TD="align: right"]4/6/2017[/TD]
[TD="align: right"]29296[/TD]
[TD]AHP-SHJ-06[/TD]
[TD]PQR[/TD]
[TD="align: right"]90.00[/TD]
[TD]APPLIED PROFESSION CHANGE IN PQR[/TD]
[/TR]
[TR]
[TD="align: right"]1/6/2017[/TD]
[TD="align: right"]31427[/TD]
[TD]AHP-SHJ-06-07[/TD]
[TD]ABC[/TD]
[TD="align: right"]353.00[/TD]
[TD]ID FOR MOHAMMED ABC
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

G2 = A2&C2&E2
H2 = IF(MATCH(G2,$G$2:$G$8,0)=ROW()-1,"","Duplicate")
 
Upvote 0
Hi,
You can use this formula and you won't need a helper column.
Code: 
=IF(COUNTIFS($D$3:$D$9,D3,$E$3:$E$9,E3,$G$3:$G$9,G3) > 1,"Duplicate","")
 
Last edited:
Upvote 0
jarjarbingie said:
G2 = A2&C2&E2
H2 = IF(MATCH(G2,$G$2:$G$8,0)=ROW()-1,"","Duplicate")
Click to expand...


Thanks for the reply,

But there is a problem in the result.

[TABLE="width: 999"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Bill No.[/TD]
[TD]Emp Code[/TD]
[TD]Emp Name[/TD]
[TD]Amount[/TD]
[TD]Description[/TD]
[TD]DateEmp CodeAmount[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD="align: right"]1/6/2017[/TD]
[TD="align: right"]108824[/TD]
[TD]AHP-SHJ-06-07[/TD]
[TD]ABC[/TD]
[TD="align: right"]353.00[/TD]
[TD]RESIDENCE RENEW FOR ABC[/TD]
[TD]42741AHP-SHJ-06-07353[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD="align: right"]5/6/2017[/TD]
[TD="align: right"]99374[/TD]
[TD]AHP-SHJ-06-03[/TD]
[TD]DEF[/TD]
[TD="align: right"]353.00[/TD]
[TD]RESIDENCE RENEW FOR DEF[/TD]
[TD]42861AHP-SHJ-06-03353[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]22-6-2017[/TD]
[TD="align: right"]99385[/TD]
[TD]AHP-DXB-16-01[/TD]
[TD]GHI[/TD]
[TD="align: right"]453.00[/TD]
[TD]RESIDENCE RENEW FOR GHI[/TD]
[TD]22-6-2017AHP-DXB-16-01453[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]23-6-2017[/TD]
[TD="align: right"]48313[/TD]
[TD]AHP-DXB-BR-02-17[/TD]
[TD]JKL[/TD]
[TD="align: right"]453.00[/TD]
[TD]NEW RESIDENCE FOR JKL[/TD]
[TD]23-6-2017AHP-DXB-BR-02-17453[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD="align: right"]5/6/2017[/TD]
[TD="align: right"]99384[/TD]
[TD]AHP-SHJ-06-03[/TD]
[TD]DEF[/TD]
[TD="align: right"]353.00[/TD]
[TD]RESIDENCE RENEW FOR MNO[/TD]
[TD]42861AHP-SHJ-06-03353[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD="align: right"]4/6/2017[/TD]
[TD="align: right"]29296[/TD]
[TD]AHP-SHJ-06[/TD]
[TD]PQR[/TD]
[TD="align: right"]90.00[/TD]
[TD]APPLIED PROFESSION CHANGE IN PQR[/TD]
[TD]42831AHP-SHJ-0690[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD="align: right"]1/6/2017[/TD]
[TD="align: right"]31427[/TD]
[TD]AHP-SHJ-06-07[/TD]
[TD]ABC[/TD]
[TD="align: right"]353.00[/TD]
[TD]ID FOR MOHAMMED ABC[/TD]
[TD]42741AHP-SHJ-06-07353[/TD]
[TD]Duplicate[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
nazeem said:
Thanks for the reply,

But there is a problem in the result.

[TABLE="width: 999"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Bill No.[/TD]
[TD]Emp Code[/TD]
[TD]Emp Name[/TD]
[TD]Amount[/TD]
[TD]Description[/TD]
[TD]DateEmp CodeAmount[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD="align: right"]1/6/2017[/TD]
[TD="align: right"]108824[/TD]
[TD]AHP-SHJ-06-07[/TD]
[TD]ABC[/TD]
[TD="align: right"]353.00[/TD]
[TD]RESIDENCE RENEW FOR ABC[/TD]
[TD]42741AHP-SHJ-06-07353[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD="align: right"]5/6/2017[/TD]
[TD="align: right"]99374[/TD]
[TD]AHP-SHJ-06-03[/TD]
[TD]DEF[/TD]
[TD="align: right"]353.00[/TD]
[TD]RESIDENCE RENEW FOR DEF[/TD]
[TD]42861AHP-SHJ-06-03353[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]22-6-2017[/TD]
[TD="align: right"]99385[/TD]
[TD]AHP-DXB-16-01[/TD]
[TD]GHI[/TD]
[TD="align: right"]453.00[/TD]
[TD]RESIDENCE RENEW FOR GHI[/TD]
[TD]22-6-2017AHP-DXB-16-01453[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]23-6-2017[/TD]
[TD="align: right"]48313[/TD]
[TD]AHP-DXB-BR-02-17[/TD]
[TD]JKL[/TD]
[TD="align: right"]453.00[/TD]
[TD]NEW RESIDENCE FOR JKL[/TD]
[TD]23-6-2017AHP-DXB-BR-02-17453[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD="align: right"]5/6/2017[/TD]
[TD="align: right"]99384[/TD]
[TD]AHP-SHJ-06-03[/TD]
[TD]DEF[/TD]
[TD="align: right"]353.00[/TD]
[TD]RESIDENCE RENEW FOR MNO[/TD]
[TD]42861AHP-SHJ-06-03353[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD="align: right"]4/6/2017[/TD]
[TD="align: right"]29296[/TD]
[TD]AHP-SHJ-06[/TD]
[TD]PQR[/TD]
[TD="align: right"]90.00[/TD]
[TD]APPLIED PROFESSION CHANGE IN PQR[/TD]
[TD]42831AHP-SHJ-0690[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD="align: right"]1/6/2017[/TD]
[TD="align: right"]31427[/TD]
[TD]AHP-SHJ-06-07[/TD]
[TD]ABC[/TD]
[TD="align: right"]353.00[/TD]
[TD]ID FOR MOHAMMED ABC[/TD]
[TD]42741AHP-SHJ-06-07353[/TD]
[TD]Duplicate[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

maybe the cell references were not properly set, I've tested it on my end and it is working fine.. this formula marks the duplicate instances and not the first one.. anyway, I think you got the help that you needed..
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
VBA code to transpose rows to columns and copy data
Replies
4
Views
602
Mario_R
M
C
  • Question Question
Copy Data by month to table using Inputbox with Add or Delete Row
Replies
4
Views
521
CemHyur
C
E
  • Question Question
VBA to send email based on cells' value to recipients in excel sheet and include cell's value in the email's body
Replies
0
Views
2K
Eraclis
E
S
  • Question Question
Remove Duplicate Values Against same date and same name data
Replies
4
Views
7K
Yoavagabond
Y
S
  • Question Question
Multiple IF formulas totalling in one cell
Replies
3
Views
535
63falcondude
63falcondude

Forum statistics

Threads
1,223,893
Messages
6,175,239
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top