Group values based on two different column values

[excel1404]

Hello Gurus,

For the following issue, I request your assistance.

I have two columns: first one has Shirt color and second one has Sizes. Within a color, the order size received is varied, just as below

Shirt Color	Size
Yellow	XL
Yellow	XL
Yellow	XL
Yellow	XL
Yellow	L
Yellow	L
Yellow	M
Blue	L
Blue	XL
Blue	XL
Green	M
Green	M

<tbody>
</tbody>

My requirement is to group the Yellow XL with Value 1, Yellow L with Value 2 and Yellow M with Value 3. when i encounter a new shirt color, i start the counter again, so therefore Blue L - Value 1, Blue XL - Value 2, and then Green M with value 1.

Shirt Color	Size	Grouped
Yellow	XL	1
Yellow	XL	1
Yellow	XL	1
Yellow	XL	1
Yellow	L	2
Yellow	L	2
Yellow	M	3
Blue	L	1
Blue	XL	2
Blue	XL	2
Green	M	1
Green	M	1

<tbody>
</tbody>

This sounds simple and i tried creating a macro, somehow couldnt manage. Please help

Regards
Excel1404

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG18Jan33
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic         [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] k           [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] p           [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
   [COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
        [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
            Dic.CompareMode = 1
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
            [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]If[/COLOR] Not Dic(Dn.Value).exists(Dn.Offset(, 1).Value) [COLOR="Navy"]Then[/COLOR]
                Dic(Dn.Value).Add (Dn.Offset(, 1).Value), Dn
            [COLOR="Navy"]Else[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value).Item(Dn.Offset(, 1).Value) _
                = Union(Dic(Dn.Value).Item(Dn.Offset(, 1).Value), Dn)
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Dn
   
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.Keys
        c = 0
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] Dic(k)
            c = c + 1
            Dic(k).Item(p).Offset(, 2).Value = c
        [COLOR="Navy"]Next[/COLOR] p
    [COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[excel1404]

Thanks Mick !! Works well !!

 

[MickG]

You're welcome