Highlight Duplicate Value, 3 Column check

[nazeem]

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.

Date	Bill No.	Emp Code	Emp Name	Amount	Description
1/6/2017	108824	AHP-SHJ-06-07	ABC	353.00	RESIDENCE RENEW FOR ABC
5/6/2017	99374	AHP-SHJ-06-03	DEF	353.00	RESIDENCE RENEW FOR DEF
22-6-2017	99385	AHP-DXB-16-01	GHI	453.00	RESIDENCE RENEW FOR GHI
23-6-2017	48313	AHP-DXB-BR-02-17	JKL	453.00	NEW RESIDENCE FOR JKL
5/6/2017	99384	AHP-SHJ-06-03	DEF	353.00	RESIDENCE RENEW FOR DEF
4/6/2017	29296	AHP-SHJ-06	PQR	90.00	APPLIED PROFESSION CHANGE IN PQR
1/6/2017	31427	AHP-SHJ-06-07	ABC	353.00	ID FOR MOHAMMED ABC

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[jarjarbingie]

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.

Date	Bill No.	Emp Code	Emp Name	Amount	Description
1/6/2017	108824	AHP-SHJ-06-07	ABC	353.00	RESIDENCE RENEW FOR ABC
5/6/2017	99374	AHP-SHJ-06-03	DEF	353.00	RESIDENCE RENEW FOR DEF
22-6-2017	99385	AHP-DXB-16-01	GHI	453.00	RESIDENCE RENEW FOR GHI
23-6-2017	48313	AHP-DXB-BR-02-17	JKL	453.00	NEW RESIDENCE FOR JKL
5/6/2017	99384	AHP-SHJ-06-03	DEF	353.00	RESIDENCE RENEW FOR DEF
4/6/2017	29296	AHP-SHJ-06	PQR	90.00	APPLIED PROFESSION CHANGE IN PQR
1/6/2017	31427	AHP-SHJ-06-07	ABC	353.00	ID FOR MOHAMMED ABC

<tbody>
</tbody>

G2 = A2&C2&E2
H2 = IF(MATCH(G2,$G$2:$G$8,0)=ROW()-1,"","Duplicate")

 

[DHayes]

Hi,
You can use this formula and you won't need a helper column.

=IF(COUNTIFS($D$3:$D$9,D3,$E$3:$E$9,E3,$G$3:$G$9,G3) > 1,"Duplicate","")

 

[nazeem]

G2 = A2&C2&E2
H2 = IF(MATCH(G2,$G$2:$G$8,0)=ROW()-1,"","Duplicate")

Thanks for the reply,

But there is a problem in the result.

Date	Bill No.	Emp Code	Emp Name	Amount	Description	DateEmp CodeAmount	Result
1/6/2017	108824	AHP-SHJ-06-07	ABC	353.00	RESIDENCE RENEW FOR ABC	42741AHP-SHJ-06-07353	Duplicate
5/6/2017	99374	AHP-SHJ-06-03	DEF	353.00	RESIDENCE RENEW FOR DEF	42861AHP-SHJ-06-03353	Duplicate
22-6-2017	99385	AHP-DXB-16-01	GHI	453.00	RESIDENCE RENEW FOR GHI	22-6-2017AHP-DXB-16-01453	Duplicate
23-6-2017	48313	AHP-DXB-BR-02-17	JKL	453.00	NEW RESIDENCE FOR JKL	23-6-2017AHP-DXB-BR-02-17453	Duplicate
5/6/2017	99384	AHP-SHJ-06-03	DEF	353.00	RESIDENCE RENEW FOR MNO	42861AHP-SHJ-06-03353	Duplicate
4/6/2017	29296	AHP-SHJ-06	PQR	90.00	APPLIED PROFESSION CHANGE IN PQR	42831AHP-SHJ-0690	Duplicate
1/6/2017	31427	AHP-SHJ-06-07	ABC	353.00	ID FOR MOHAMMED ABC	42741AHP-SHJ-06-07353	Duplicate

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[nazeem]

Hi,
You can use this formula and you won't need a helper column.

=IF(COUNTIFS($D$3:$D$9,D3,$E$3:$E$9,E3,$G$3:$G$9,G3) > 1,"Duplicate","")

Thanks a ton.

 

[DHayes]

You are welcome

 

[jarjarbingie]

Thanks for the reply,

But there is a problem in the result.

Date	Bill No.	Emp Code	Emp Name	Amount	Description	DateEmp CodeAmount	Result
1/6/2017	108824	AHP-SHJ-06-07	ABC	353.00	RESIDENCE RENEW FOR ABC	42741AHP-SHJ-06-07353	Duplicate
5/6/2017	99374	AHP-SHJ-06-03	DEF	353.00	RESIDENCE RENEW FOR DEF	42861AHP-SHJ-06-03353	Duplicate
22-6-2017	99385	AHP-DXB-16-01	GHI	453.00	RESIDENCE RENEW FOR GHI	22-6-2017AHP-DXB-16-01453	Duplicate
23-6-2017	48313	AHP-DXB-BR-02-17	JKL	453.00	NEW RESIDENCE FOR JKL	23-6-2017AHP-DXB-BR-02-17453	Duplicate
5/6/2017	99384	AHP-SHJ-06-03	DEF	353.00	RESIDENCE RENEW FOR MNO	42861AHP-SHJ-06-03353	Duplicate
4/6/2017	29296	AHP-SHJ-06	PQR	90.00	APPLIED PROFESSION CHANGE IN PQR	42831AHP-SHJ-0690	Duplicate
1/6/2017	31427	AHP-SHJ-06-07	ABC	353.00	ID FOR MOHAMMED ABC	42741AHP-SHJ-06-07353	Duplicate

<tbody>
</tbody>

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..