VBA to find unique from multiple columns and count on multiple criteria. Anybody please

[Ombir]

Hi friends,


I have a worksheet shown below.

	A	B	C	D	E	F
Dt	Sb1	Sb2	Sb3	Sb4	Sb5
Am	ENC	HIC	POS	ECO	CPU
Am	ENC	HIC	POS	MAS	MAT
Bw	ENC	HIC	POS	ECO	CPU
Bw	ENC	HOS	PHE	CHE	BIO
Jn	ENC	HIC	POS	ECO	CPU
Jn	ENC	HIC	POS	ECO	CPU
Jn	ENC	HIC	MAT	OSH	OSE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

</tbody>

Sheet3​

First I want to find Unique values based on Column 1 and Column(2-6) then count these values as shown below.

	H	I	J
Dt	Sb	Cnt
Am	ENC
Am	HIC
Am	POS
Am	ECO
Am	CPU
Am	MAS
Am	MAT
Bw	ENC
Bw	HIC
Bw	POS
Bw	PHE
Bw	ECO
Bw	CPU
Bw	CHE
Bw	BIO
Jn	ENC
Jn	HIC
Jn	POS
Jn	ECO
Jn	CPU
Jn	MAT
Jn	OSH
Jn	OSE

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]23[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]24[/TD]

[TD="align: right"]1[/TD]

</tbody>

Sheet3​

I am able to code this in Visual Fox Pro but can anybody help me with some VBA code to achieve this. Thanks

 

[MickG]

Perhaps an example would help:-
This is a basic "Dictionary code" which is trying to Find the count of each Unique Item in column "A" and displaying the Results in columns "B/C"
Copy Column "A" to your sheet and run the code !!!!

[COLOR=RoyalBlue][B]Row No [/B][/COLOR] [COLOR=RoyalBlue][B]Col(A) [/B][/COLOR] [COLOR=RoyalBlue][B]Col(B)  [/B][/COLOR] [COLOR=RoyalBlue][B]Col(C) [/B][/COLOR]
1.      Data    Results  Count  
2.      10      10       3      
3.      12      12       1      
4.      13      13       2      
5.      10      18       1      
6.      18      19       1      
7.      19                      
8.      13                      
9.      10

[COLOR=Navy]Sub[/COLOR] MG29Dec03
[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] Dic [COLOR=Navy]As[/COLOR] Object
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
'[COLOR=Green][B]Base on the data in column "A" , we are trying to find the count of each Unique item[/B][/COLOR]
[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=Green][B]The first value is "10" and is set as the first "Key"[/B][/COLOR]
    '[COLOR=Green][B]The Count is by default "1" because its the first time "10" is found[/B][/COLOR]
        Dic.Add Dn.Value, 1
    [COLOR=Navy]Else[/COLOR]
     '[COLOR=Green][B]When "10" appears again, it appears in the "Else" statement,[/B][/COLOR]
     '[COLOR=Green][B]because it already exists in the Dictionary[/B][/COLOR]
     '[COLOR=Green][B]Now Dn.Value = 10 and the Item of Dn.value :- "Dic.item(Dn.value)" is "1" , as shown  below, and is[/B][/COLOR]
     '[COLOR=Green][B]increased by "1" so "Dic.item(dn.value)"now becomes "2"[/B][/COLOR]
   
     Dic.Item(Dn.Value) = Dic.Item(Dn.Value) + 1
    '[COLOR=Green][B]This  "Addition" is repeated every time a repeated[/B][/COLOR]
    '[COLOR=Green][B]value of an Existing "Key" is found.[/B][/COLOR]
    
    '[COLOR=Green][B]NB:-If the Item of "Dic.item(dn.value)" had been an array (Like:- "array(n,1)" in orig code) you would[/B][/COLOR]
    '[COLOR=Green][B]need to Hold it in a variable Like "Q" and it would need to be updated after its value had changed :-[/B][/COLOR]
    '[COLOR=Green][B]Ref:- "Dic.item(dn.value)= Q"[/B][/COLOR]
    '[B]NB:-In this case Q is a Variant array representing "Array(n,1), so the first value would be
    '"Q(0)= n and the second "Q(1)"[/B] [COLOR=Green][B]which represents, NOT JUST "1" but the value it has
    'increases to as its count increases.[/B][/COLOR]
[COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]
'[COLOR=Green][B]Display results in column "B&C"[/B][/COLOR]
Range("B2").Resize(Dic.Count, 2) = Application.Transpose(Array(Dic.Keys, Dic.items))
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[Ombir]

Thanks Mick for explaining it with an example but still confused with my first point.


1. As you have declared item as array(n,1) and storing this into variable Q. How this code is identifying that it will contain two values i.e Q(0) and Q(1) not say 4 like Q(0),Q(1),Q(2),Q(3). Does this array(n,1) is telling Q to store 2 values. If yes then how ?

 

[MickG]

If I wanted to store a Range of cells in an Array, I could do this:-
Dim Ray as variant
Ray = Range("A1:Z200")
The array will store what its given, in the same way when I say Q = Array(n,1), then Q stores 2 items as an array that are Q(0) and Q(1), because that all that's there !!!
If the array was Array(a,b,c,d) then the Values in "Q" would Q(0),Q(1),Q(2), and Q(3)

 

[Ombir]

I was getting confused with Array(n,1) because I was comparing it with a matrix of n rows and 1 column. Thanks for clearing my doubt Mick.

 

[MickG]

You're welcome