Creating a dictionary of all the values from a column

[sinz0000]

there is going to be a data in A column of current sheet containing unique values. I want to add all these already existing unique values to a dictionary and use each of the unique value as a criteria for filtering in next sheet.

How to add all the values of A column to the dictionary? and how to refer to each of the value in it?

 

[Peter_SSs]

Hi there is more processing that has come up on this.

Do you think there might be more changes? If so, it would be best to wait till the final format & requirements are determined before proceeding.

Suppose the current data is like this:

On what sheet?

I need to split 'Process values' in the 4 columns

On what sheet?

 

[Peter_SSs]

Could you please tell how this code is functioning?

In the dictionary, it is joining all the Processes for a Module together with a semicolon between - as described in post 6:

append all the column B values with a separator

 

[sinz0000]

Do you think there might be more changes? If so, it would be best to wait till the final format & requirements are determined before proceeding.


On what sheet?


On what sheet?

Hi,
No I am hoping that i wont receive any further changes after this from the upper management. So all in all the case is like this.
In criteria sheet, suppose we have:

Module	Processes
A	P1
A	P2
A	P3
A	P4
A	P5
A	P6
A	P7
A	P8
B	P9
B	P10
B	P11
B	P12
B	P13
B	P14
C	P15
C	P16
C	P17
C	P18
C	P19
C	P20
C	P21

<tbody>
</tbody>

And in the next sheet the outcome should be like(Division of processes in 4 columns):

A	P1	P2	P3	P4
	P5	P6	P7	P8
B	P9	P10	P11	P12
	P13	P14
C	P15	P16	P17	P18
	P19	P20	P21

<tbody>
</tbody>

 

[sinz0000]

In the dictionary, it is joining all the Processes for a Module together with a semicolon between - as described in post 6:

Yes i understood what that code is doing but i am not able to understand the syntax.

I am trying to understand syntax of:

Dict(Data(i, 1)) = Dict(Data(i, 1)) & Data(i, 2) & ";"

how to read the meaning here, for example what does "Dict(Data(i, 1))" and other syntax stand for?

 

[Peter_SSs]

Thanks for clarifying the layouts. I have just commented the changed sections in this code.

Sub CollateData_v2()
  Dim Dict As Object
  Dim Data As Variant, Results As Variant, Ky As Variant, Itm As Variant
  Dim i As Long, r As Long, c As Long
  
  Set Dict = CreateObject("Scripting.Dictionary")
  
  With Sheets("Criteria")
    Data = .Range("A1", .Range("B" & .Rows.Count).End(xlUp)).Value
  End With
 
  For i = 1 To UBound(Data)
    Dict(Data(i, 1)) = Dict(Data(i, 1)) & ";" & Data(i, 2)
  Next i
  
  'Set up an array at least big enough to hold the results
  ReDim Results(1 To UBound(Data), 1 To 5)
  
  'Work through the Keys (ie Modules) in the dictionary &
  'Split the Items (ie Processes) into rows of 4 columns
  'Putting them in the Results array
  For Each Ky In Dict.Keys()
    r = r + 1
    c = 1
    Results(r, 1) = Ky
    For Each Itm In Split(Mid(Dict(Ky), 2), ";")
      c = c + 1
      If c = 6 Then
        c = 2
        r = r + 1
      End If
      Results(r, c) = Itm
    Next Itm
  Next Ky
  
  'Add the new sheet and write the Results array into it
  Sheets.Add After:=Sheets(Sheets.Count)
  Sheets(Sheets.Count).Range("A1").Resize(r, 5).Value = Results
End Sub

 

[Peter_SSs]

I am trying to understand syntax of:

Dict(Data(i, 1)) = Dict(Data(i, 1)) & Data(i, 2) & ";"

how to read the meaning here, for example what does "Dict(Data(i, 1))" and other syntax stand for?

Not sure how clearly I can explain, but I'll try using this sheet as a small example

Book1
	A	B
1	Module	Processes
2	A	P1
3	A	P2
4	A	P3
5	B	P9
6	C	P15
7	C	P16
8
Criteria

I'll also use the new version of that line of code as it changed slightly (putting the semicolon in a different place)

Dict(Data(i, 1)) = Dict(Data(i, 1)) & ";" & Data(i, 2)

Data is a 2-dimensional array holding the data you see above so still think of rows and columns.
The code line above is in a loop where the variable i runs from 1 to 7 as there are 7 rows of data.
When i = 1
Data(i,1) = Data(1,1) = "Module"
Data(i,2) = Data(1,2) = "Processes"
so the code line says
Dict("Module") = Dict("Module") & ";" & "Processes"

Dict("Module") means what is stored in the dictionary for "Module". When this line is about to run for the first time nothing is in the dictionary at all so what is stored for "Module" is nothing & so this line is saying
In the dictionary for "Module" store whatever is already in there (nothing) and append a semicolon and append "Processes"
So, after processing row 1 above what is stored in the dictionary is what is shown in F2:G2 below

Row 2 above is similar in that "A" is not already in the dictionary so after processing row 2 what is in the dictionary is what is shown in F2:G3 below.

When we get to row 3 above "A" is repeated so our code line says, for the Key "A", store whatever is already in the dictionary for "A" (ie ";P1") and append a semicolon and whatever is in the 2nd column of that row. So for "A" now is stored ";P1;P2" as shown in row 4 below. Row 3 below is no longer relevant. In the end, there are just 4 Keys & 4 Items in the dictionary - the last row of each Key (coloured).
etc

Hope that helped. If you need more about dictionaries in vba, here is one resource but Googling I'm sure would find you plenty more.

Book1
	A	B	C	D	E	F	G
1	Module	Processes			After Row	Dict Keys	Dict Items
2	A	P1			1	Module	;Processes
3	A	P2			2	A	;P1
4	A	P3			3	A	;P1;P2
5	B	P9			4	A	;P1;P2;P3
6	C	P15			5	B	;P9
7	C	P16			6	C	;P15
8					7	C	;P15;P16
Criteria

 

[sinz0000]

Thanks for clarifying the layouts. I have just commented the changed sections in this code.

Thanks a lot for this Sir. working perfectly. would study the code now. : )

 

[sinz0000]

When i = 1
Data(i,1) = Data(1,1) = "Module"
Data(i,2) = Data(1,2) = "Processes"
so the code line says
Dict("Module") = Dict("Module") & ";" & "Processes"

Dict("Module") means what is stored in the dictionary for "Module". When this line is about to run for the first time nothing is in the dictionary at all so what is stored for "Module" is nothing & so this line is saying
In the dictionary for "Module" store whatever is already in there (nothing) and append a semicolon and append "Processes"
So, after processing row 1 above what is stored in the dictionary is what is shown in F2:G2 below

Row 2 above is similar in that "A" is not already in the dictionary so after processing row 2 what is in the dictionary is what is shown in F2:G3 below.

Awesomely explained. I got this now. Thank you very very much.

 

[Peter_SSs]

Thanks a lot for this Sir. working perfectly. would study the code now. : )

Awesomely explained. I got this now. Thank you very very much.

All good. Cheers.

 

[sinz0000]

Hi,

I wasnt able to put in some time to study this. Today, I tried to understand it. I again need your expertise to understand it; hope you can help. In the code

 For Each Ky In Dict.Keys()
    r = r + 1
    c = 1
    Results(r, 1) = Ky
    For Each Itm In Split(Mid(Dict(Ky), 2), ";")
      c = c + 1
      If c = 6 Then
        c = 2
        r = r + 1
      End If
      Results(r, c) = Itm
    Next Itm
  Next Ky

We are inserting dictionary values in results. However for
r = r + 1, where is the base value set for r? Like on the third line we are setting the base value of C as 1 but for r how is the code picking the base value?

Also could you help me with the below line, I know it is for splitting the values on the basis of ";" but i am not able to understand how the highlighted syntax is working.
For Each Itm In Split(Mid(Dict(Ky), 2), ";")

Thanks