duplicate detection formula or vba macro

[rn119]

I have a table in the following manner (let's say they are positioned from columns A to D)

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Ref #[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]407[/TD]
[TD]INVTF[/TD]
[TD]100900[/TD]
[TD]Paris[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]789030[/TD]
[TD]New York[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]541001[/TD]
[TD]Madrid[/TD]
[/TR]
[TR]
[TD]800[/TD]
[TD]KLUMM[/TD]
[TD]103990[/TD]
[TD]Hong Kong[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]102030[/TD]
[TD]Toronto[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]100900[/TD]
[TD]Paris[/TD]
[/TR]
[TR]
[TD]750[/TD]
[TD]JSIUI[/TD]
[TD]130990[/TD]
[TD]Montreal[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]657899[/TD]
[TD]Miami[/TD]
[/TR]
</tbody>[/TABLE]

100900 occurs twice here under two different IDs (i.e., 407 and 800). Is there a formula or a macro that I can use that will write that the Ref # exists in different ID sequences or a formula that will check for dups after the second iteration?

 

[Eric W]

Try:

Book1
	A	B	C	D	E
1	ID	Name	Ref #	Location
2	407	INVTF	100900	Paris
3			789030	New York
4			541001	Madrid
5	800	KLUMM	103990	Hong Kong
6			102030	Toronto
7			100900	Paris	Duplicate
8	750	JSIUI	130990	Montreal
9			657899	Miami
10
Sheet11

Cell Formulas
Range	Formula
E2	=IF(COUNTIF(C$2:C2,C2)>1,"Duplicate","")

 

[rn119]

Thanks Eric. This works to find the second duplicate and greater entry but I have an additional complexity. On occasion, the Ref ID might be linked to a different Location. If that's the case, it should not be marked as duplicate. It should be null. Can we tweak the formula to say if the Ref # AND Location are the same on different ID sequences....mark the second and greater as duplicate.

 

[Eric W]

Sure! How about:

Book1
	A	B	C	D	E
1	ID	Name	Ref #	Location
2	407	INVTF	100900	Paris
3			789030	New York
4			541001	Madrid
5	800	KLUMM	103990	Hong Kong
6			102030	Toronto
7			100900	Paris	Duplicate
8	750	JSIUI	130990	Montreal
9			657899	Miami
10			789030	London
11
Sheet11

Cell Formulas
Range	Formula
E2	=IF(COUNTIFS(C$2:C2,C2,D$2:D2,D2)>1,"Duplicate","")

 

[CA_Punit]

Why is solution showing in typical XML format???

 

[Fluff]

See here

Welcome to Version 4 of the New MrExcel Message Board!

Way back in 1999, I launched the first MrExcel Message Board using a WWWBoard Perl Script from Matt's Script Archive. Later, we migrated to phpBB and then to vBulletin. The vBulletin board lasted for 12 years. But with vBulletin no longer being supported, as of November 17, 2019, we have...

www.mrexcel.com

 

[CA_Punit]

Thanks Fluff,

Will it be converted to readable format in future?? Most of old post of Aladin //Eric//Peter_SS are converted to HTML and we cannot find the solution. So any trick to convert them???

 

[Fluff]

The idea is that they will all be converted in time, but as there were so many different styles used it is a slow process & will take time.
In the mean time you can use this site to convert the the HTML

Real-time HTML Editor

htmledit.squarefree.com

 

[CA_Punit]

Awesome. Can this information be shared in General Forum so that it can help many other decoding html script

Will greatly help newbie's like me who want to learn from legends

 

[CA_Punit]

Also is there any faster way to download the old post of our learned Members

If you want to see post of Aladin for the year 2015 i have to go to advanced search and then scroll through all post until i reach year 2015. Also if someway the web-page gets closed than i have to go through all the post again from today..

Is there any quick method by which if i search for post of aladin in the year 2015??

It will be quite helpful