Trouble getting unique number for each brand each customer.

[jibu08]

ood day,
Hey everyone
i found myself in difficult situation as i am unable to get unique number of product sold for each customer each brand .. for example :
[TABLE="width: 500"]
<tbody>[TR]
[TD]Custo No[/TD]
[TD]Brand[/TD]
[TD]Product code[/TD]
[TD]Product Name[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]HEINZ[/TD]
[TD]002[/TD]
[TD]ketchup Paste[/TD]
[/TR]
[TR]
[TD]130[/TD]
[TD]Fruit[/TD]
[TD]010[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]130[/TD]
[TD]Heinz[/TD]
[TD]001[/TD]
[TD]Ketchup Mayo[/TD]
[/TR]
[TR]
[TD]115[/TD]
[TD]Fruit[/TD]
[TD]011[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]116[/TD]
[TD]Heinz[/TD]
[TD]003[/TD]
[TD]Ketchup BBQ sauce[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Fruit[/TD]
[TD]010[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]130[/TD]
[TD]HEINZ[/TD]
[TD]002[/TD]
[TD]ketchup Paste[/TD]
[/TR]
[TR]
[TD]116[/TD]
[TD]Heinz[/TD]
[TD]002[/TD]
[TD]Ketchup Paste[/TD]
[/TR]
[TR]
[TD]116[/TD]
[TD]Fruit[/TD]
[TD]011[/TD]
[TD]Apple[/TD]
[/TR]
</tbody>[/TABLE]
PivotTable will give repeated number of product sold by each customer.
I need unique product sold per customer per brand .. I need a table like this[TABLE="width: 500"]
<tbody>[TR]
[TD]Account code[/TD]
[TD]Heinz[/TD]
[TD]Fruit[/TD]
[/TR]
[TR]
[TD]130[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]116[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Re: Trouble getting unique number for each brand each customer . URGENT HELP

Try this for results starting "g1".

[COLOR="Navy"]Sub[/COLOR] MG27Jun36
[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] Q           [COLOR="Navy"]As[/COLOR] Variant
 [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
 [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
   [COLOR="Navy"]With[/COLOR] CreateObject("Scripting.Dictionary")
      .CompareMode = 1
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
           .Item(UCase(Dn.Offset(, 1).Value)) = Empty
           [COLOR="Navy"]If[/COLOR] Not Dic.exists(CStr(Dn.Value)) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(CStr(Dn.Value)) = CreateObject("Scripting.Dictionary")
            [COLOR="Navy"]End[/COLOR] If
        
        [COLOR="Navy"]If[/COLOR] Not Dic(CStr(Dn.Value)).exists(UCase(Dn.Offset(, 1).Value)) [COLOR="Navy"]Then[/COLOR]
                Dic(CStr(Dn.Value)).Add (UCase(Dn.Offset(, 1).Value)), 1
        [COLOR="Navy"]Else[/COLOR]
                Dic(CStr(Dn.Value)).Item(UCase(Dn.Offset(, 1).Value)) = _
                Dic(CStr(Dn.Value)).Item(UCase(Dn.Offset(, 1).Value)) + 1
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
   [COLOR="Navy"]Dim[/COLOR] k        [COLOR="Navy"]As[/COLOR] Variant
   [COLOR="Navy"]Dim[/COLOR] c        [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
   [COLOR="Navy"]Dim[/COLOR] Bd       [COLOR="Navy"]As[/COLOR] Variant
   
   ReDim ray(1 To Dic.Count + 1, 1 To .Count + 1)
    c = 1
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.keys
        c = c + 1: col = 1
        ray(c, 1) = k
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Bd [COLOR="Navy"]In[/COLOR] .keys
                col = col + 1
                ray(1, col) = Bd
                ray(c, col) = Dic(k).Item(Bd)
            [COLOR="Navy"]Next[/COLOR] Bd
    [COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Range("G1").Resize(UBound(ray, 1), UBound(ray, 2))
        .Value = ray
        .Borders.Weight = 2
        .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick