VBA - matching two cells and returning a numerical reference number specific to that match

[ricopips]

Hi

I need help, I am still a relative newbie as far as VBA is concerned, so any advice would be well received.

I would like some code which will look at and cross check only integer values in columns A & B and where a match is found to enter a unique and incremental numerical value without subject to any limits

For eg

A B
1 ABC 285
2 445 JKH
3 FFG 445
4 285 IIO

So for row 1, A1 is ignored as it is not a number, so it would instead focus on B2, the macro would then query rows 1 to 4 in column A and search for "285", when it has found "285" it would place a unique reference number starting from 1 to infinity in column C in both rows where the values match "285". In this case row 1 & 4 have "285" so cells C1 & C4 would be marked with the same reference number, being 1.

The macro would then go to row 2, check which column has an integer value, in this case it is A2, and then proceed to check columns A and B for "445", where it finds this value it would proceed to mark in column C the value 2, with each successive match the reference value increases by a value of 1.

Illustration - when macro has completed

A B C
1 ABC 285 1
2 445 JKH 2
3 FFG 445 2
4 285 IIO 1


Many thanks


Rico

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG17Aug09
[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"]Dim[/COLOR] Q
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Rng = Rng.Resize(, 2)
    [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] IsNumeric(Left(Dn, 1)) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            n = n + 1
            .Add Dn.Value, Array(Dn, n)
        [COLOR="Navy"]Else[/COLOR]
            Range("C" & Dn.row) = .Item(Dn.Value)(1)
            Range("C" & .Item(Dn.Value)(0).row) = .Item(Dn.Value)(1)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick