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 :

Custo No	Brand	Product code	Product Name
120	HEINZ	002	ketchup Paste
130	Fruit	010	Orange
130	Heinz	001	Ketchup Mayo
115	Fruit	011	Apple
116	Heinz	003	Ketchup BBQ sauce
120	Fruit	010	Orange
130	HEINZ	002	ketchup Paste
116	Heinz	002	Ketchup Paste
116	Fruit	011	Apple

<tbody>
</tbody>

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

Account code	Heinz	Fruit
130	2	1
116	2	1

<tbody>
</tbody>

 

[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