Trying to match data using VBA

[ANAND KUMAR]

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

 

[MickG]

Try this:-

[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

 

[ANAND KUMAR]

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

 

[MickG]

I can get 970 matching cells using the below, that's the best I can do !!
I checked the code against "Countif" function.

[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

 

[ANAND KUMAR]

Agree.......But I am able to find 983 records by using Text to column and then Vlookup.

For ease attached sheet with testing VBA and Formula and there is discrepancy in result to each other.

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

There are two separate sub sheets which define both terms.

Hope you won't be confused by above clarification.

Again Thanks for your efforts..........☻

 

[Peter_SSs]

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.

 

[ANAND KUMAR]

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

 

[Peter_SSs]

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.

 

[ANAND KUMAR]

Got it now..........Thanks.

@ Mick - found it now.........Thanks to you for your valuable time.

 

[MickG]

You're welcome

Thanks Peter for your timely intervention !!!