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

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Shirt Color[/TD]
[TD="class: xl63, width: 64"]Size[/TD]
[/TR]
[TR]
[TD="class: xl63"]Yellow[/TD]
[TD="class: xl63"]XL[/TD]
[/TR]
[TR]
[TD="class: xl63"]Yellow[/TD]
[TD="class: xl63"]XL[/TD]
[/TR]
[TR]
[TD="class: xl63"]Yellow[/TD]
[TD="class: xl63"]XL[/TD]
[/TR]
[TR]
[TD="class: xl63"]Yellow[/TD]
[TD="class: xl63"]XL[/TD]
[/TR]
[TR]
[TD="class: xl63"]Yellow[/TD]
[TD="class: xl63"]L[/TD]
[/TR]
[TR]
[TD="class: xl63"]Yellow[/TD]
[TD="class: xl63"]L[/TD]
[/TR]
[TR]
[TD="class: xl63"]Yellow[/TD]
[TD="class: xl63"]M[/TD]
[/TR]
[TR]
[TD="class: xl63"]Blue[/TD]
[TD="class: xl63"]L[/TD]
[/TR]
[TR]
[TD="class: xl63"]Blue[/TD]
[TD="class: xl63"]XL[/TD]
[/TR]
[TR]
[TD="class: xl63"]Blue[/TD]
[TD="class: xl63"]XL[/TD]
[/TR]
[TR]
[TD="class: xl63"]Green[/TD]
[TD="class: xl63"]M[/TD]
[/TR]
[TR]
[TD="class: xl63"]Green[/TD]
[TD="class: xl63"]M[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Shirt Color[/TD]
[TD="class: xl65, width: 64"]Size[/TD]
[TD="width: 64"]Grouped[/TD]
[/TR]
[TR]
[TD="class: xl65"]Yellow[/TD]
[TD="class: xl65"]XL[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]Yellow[/TD]
[TD="class: xl65"]XL[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]Yellow[/TD]
[TD="class: xl65"]XL[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]Yellow[/TD]
[TD="class: xl65"]XL[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]Yellow[/TD]
[TD="class: xl65"]L[/TD]
[TD="class: xl65, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]Yellow[/TD]
[TD="class: xl65"]L[/TD]
[TD="class: xl65, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]Yellow[/TD]
[TD="class: xl65"]M[/TD]
[TD="class: xl65, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65"]Blue[/TD]
[TD="class: xl65"]L[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]Blue[/TD]
[TD="class: xl65"]XL[/TD]
[TD="class: xl65, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]Blue[/TD]
[TD="class: xl65"]XL[/TD]
[TD="class: xl65, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]Green[/TD]
[TD="class: xl65"]M[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]Green[/TD]
[TD="class: xl65"]M[/TD]
[TD="class: xl65, align: right"]1



[/TD]
[/TR]
</tbody>[/TABLE]

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