duplicate detection formula or vba macro

rn119

New Member
Joined
Feb 27, 2013
Messages
49
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?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Try:


Book1
ABCDE
1IDNameRef #Location
2407INVTF100900Paris 
3789030New York
4541001Madrid
5800KLUMM103990Hong Kong
6102030Toronto
7100900ParisDuplicate
8750JSIUI130990Montreal
9657899Miami
10
Sheet11
Cell Formulas
RangeFormula
E2=IF(COUNTIF(C$2:C2,C2)>1,"Duplicate","")
 
Upvote 0
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.
 
Upvote 0
Sure! How about:


Book1
ABCDE
1IDNameRef #Location
2407INVTF100900Paris 
3789030New York
4541001Madrid
5800KLUMM103990Hong Kong
6102030Toronto
7100900ParisDuplicate
8750JSIUI130990Montreal
9657899Miami
10789030London
11
Sheet11
Cell Formulas
RangeFormula
E2=IF(COUNTIFS(C$2:C2,C2,D$2:D2,D2)>1,"Duplicate","")
 
Upvote 0
See here
 
Upvote 0
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???
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

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