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]

Welcome to the MrExcel board!

See if this heads you in the right direction. Post back with more details if you need further help.

Sub Test()
  Dim Dict As Object
  Dim Data As Variant, Ky As Variant, Itm As Variant
  
  Set Dict = CreateObject("Scripting.Dictionary")
  Data = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  For Each Itm In Data
    Dict(Itm) = Empty
  Next Itm
  For Each Ky In Dict.Keys()
    'Do your filtering of each value here
  Next Ky
End Sub

 

[sinz0000]

thanks for the reply.

Could you please tell what this code is doing?

Dict(Itm) = Empty

Also i will share my full scenario now.

I have one sheet named "criteria", one temporary sheet will be added named as "temporary". This temporary sheet is to take out uniques from Criteria!A:A. Later from these two sheets processing will be done and third sheet will be added.
I want to apply filter in Criteria sheet where the criteria will be taken from the unique cells of "temporary" sheet.

My Original data in Criteria Sheet
<code>Module Process
A P1
A P2
A P3
B P5
B P7
C P4
C P6
C P8

What i want from vba into third new sheet

</code><code><code>Module Process
A P1 | P2 | P3
B P5 | P7 |
C P4 | P6 | P8</code>


For this So far what i have done is

Sub FilterTest1()


ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "temporary"

ActiveSheet.[A:A].Value = Worksheets("Criteria").[A:A].Value

Cells.RemoveDuplicates Columns:=Array(1)

<code>'for heading deletion</code>
Range("A1").EntireRow.Delete 

Dim B As Integer
B = Cells.CurrentRegion.Rows.Count

With Sheets("Temporary")
Selection.End(xlDown).Select
End With


Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

[COLOR=#006400]'need help from here[/COLOR]</code><code>[COLOR=#006400][/COLOR]  

End Sub

I have learned this much so far but i am still really new. So it would be really helpful if you could explain what a particular syntax in your additions is doing.

thanks a lot.

</code>

 

[Peter_SSs]

Could you please tell what this code is doing?

Dict(Itm) = Empty

When you put something in a dictionary you must add a Key and an associated Item. That line puts every entry in the column as a Key and 'Empty' (nothing really) as the Item. The Item could just as easily have been something like 1 (ie Dict(Itm) = 1) since the way you want to use the dictionary only involves the Keys, not the Items.

What i want from vba into third new sheet

Module Process
A P1 | P2 | P3
B P5 | P7 |
C P4 | P6 | P8

It is not clear quite what that means.
- Is "P1 | P2 | P3" all in a single cell, or
- Does that mean P1 in one cell, P2 in the next cell to the right, P3 in the next cell to the right?

I doubt you will need to use RemoveDuplicates (as the dictionary will only allow each entry to occur once anyway), or a temporary worksheet. I will suggest a code when the above question is clarified.

 

[sinz0000]

Oh so remove duplicates is already done while making the dictionary. great.

I want to have P1, P2, P3 in different cells sequentially one after another like you said in the second line of ur question.

 

[Peter_SSs]

I want to have P1, P2, P3 in different cells sequentially one after another like you said in the second line of ur question.

Try this in a copy of your workbook.

Sub CollateData()
  Dim Dict As Object
  Dim Data As Variant
  Dim i As Long
  
  'Set up the dictionary
  Set Dict = CreateObject("Scripting.Dictionary")
  
  'Read the data from cols A:B of sheet 'Criteria" into an array in memory
  'This gives faster processing than going back to the worksheet to read each cell
  With Sheets("Criteria")
    Data = .Range("A1", .Range("B" & .Rows.Count).End(xlUp)).Value
  End With
  
  'Put all the column A values into the dictionary.
  'They will only be added once and just append all the column B values with a separator
  For i = 1 To UBound(Data)
    Dict(Data(i, 1)) = Dict(Data(i, 1)) & Data(i, 2) & ";"
  Next i
  
  'Add the new sheet
  Sheets.Add After:=Sheets(Sheets.Count)
  
  With Sheets(Sheets.Count).Range("A1")
    'Put all the dictionary Keys (col A values) and items (appended col B values) into this sheet
    .Resize(Dict.Count, 2).Value = Application.Transpose(Array(Dict.Keys, Dict.Items))
    'Use Text to Columns to split the appended col B values into separate columns
    .CurrentRegion.Columns(2).TextToColumns DataType:=xlDelimited, Semicolon:=True
  End With
End Sub

 

[sinz0000]

Try this in a copy of your workbook.

This is just perfect.
Thanks a lot

 

[Peter_SSs]

This is just perfect.
Thanks a lot

You're welcome. Thanks for letting us know.

 

[sinz0000]

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

Suppose the current data is like this:

Module	Process
A	P1	P2	P3	P3	P4	P5	P6	P7	P8	P9
B	P10	P11	P12	P13	P14	P15	P16	P17
C	P20	P21	P22	P23	P24	P25	P26	P27	P28
and so on
I need to split 'Process values' in the 4 columns
Module	Process
A	P1	P2	P3	P3
	P4	P5	P6	P7
	P8	P9
B	P10	P11	P12	P13
	P14	P15	P16	P17
C	P20	P21	P22	P23
	P24	P25	P26	P27
	P28

<tbody>
</tbody>

 

[sinz0000]

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

Could you please tell how this code is functioning?