Trying to match data using VBA

ANAND KUMAR

New Member
Joined
Oct 20, 2016
Messages
41
Dears,

I need some help to find matched numbers. I have a sheet and there are two columns; column A has few cities name upto 2828 (with duplicates) and the next one is in column C which contain unique values of cities available in column A (495 records - suffixed with some extra alphabet).

So now I want to highlight all the cities which are available in column A by mapping column C using VBA.

For better understanding uploaded attachment at below link,

https://drive.google.com/open?id=0Bxtc6jUuxe_paEVvV2s0QVllUkU

This time I used Text to column and then did Vlookup to match them; But it would be great if any VBA would help in this.

Thanks in advance. ☺
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Try this:-
Code:
[COLOR="Navy"]Sub[/COLOR] MG05Nov40
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng: .Item(Trim(Split(Dn.Value, "(")(0))) = Empty: [COLOR="Navy"]Next[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR] Dn.Interior.Color = vbYellow
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks for your prompt response Mick.......But unfortunately this code just filter only 956 records and in actual there are 983 records are matching. Please check it once again....
 
Upvote 0
I can get 970 matching cells using the below, that's the best I can do !!
I checked the code against "Countif" function.
Code:
[COLOR="Navy"]Sub[/COLOR] MG05Nov47
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng: .Item(UCase(Trim(Split(Dn.Value, "(")(0)))) = Empty: [COLOR="Navy"]Next[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] .Exists(UCase(Trim(Dn.Value))) [COLOR="Navy"]Then[/COLOR] Dn.Interior.Color = vbYellow
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Agree.......But I am able to find 983 records by using Text to column and then Vlookup.
But your text to columns is splitting town names. For example, in cell A1508 you have "Baripada".
In column C there is no "Baripada" but there is "Baripada Town (M)" (cell C51). Because of the way you have used text to columns, cell G51 contains "Baripada" when it should contain "Baripada Town". Hence your VLOOKUP method is returning false matches.

Another example is "Bhilai" being matched to "Bhilai Nagar" when in fact they do not match.
 
Last edited:
Upvote 0
Great Peter.............Thanks for point out this.

Now I have used '=TRIM(LEFT(txt,FIND("(",txt)-1))' and then used Vlookup & finally achieved 956 records.

But could you please explain that what is the difference b/w first and second code and why they are resulting separate consequence.

Once again Thanks a ton..........
 
Upvote 0
But could you please explain that what is the difference b/w first and second code and why they are resulting separate consequence.
Because some of your data in column A has an extra space after the name (eg cell A62)
The post #2 code does not trim off this space before looking for the name in the list of available names, that have all been trimmed. Hence row 62 is not returned by post #2 code.
Post #4 code does trim the column A values as well as the split column C values. In that case row 62 is returned as a match.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

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