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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
609
Mario_R
M
C
  • Question Question
Copy Data by month to table using Inputbox with Add or Delete Row
Replies
4
Views
525
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
536
63falcondude
63falcondude

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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