Creating named ranges

[FryGirl]

I need to create a couple of dependent data validation cells and I know I can create named ranges and then use the Indirect function in the first data validation cell to populate the second.

Is there any way to automate this process? First extract the unique items from column A into column D and then for each value in column D extract the the corresponding values into columns E, F, G, etc,.

 

[FryGirl]

Any thoughts on an automated process to to compare the values in the extracted list from column D and then use each item to create more named ranges with all the items from C which match D and place those ranges starting in E and to the right until done?

Column A contains multiples of North and South, while column B will contain all the values which belong to either North or South.

 

[machopicho]

With VBA?

 

[FryGirl]

Yes a VBA method would be nice.

 

[machopicho]

Step by step!
Unique values in column D:

Sub extract_unique_value()
Columns("A").Copy Destination:=Columns("D")
n = Cells(Rows.Count, "D").End(xlUp).Row
For r = 1 To n
For rw = r + 1 To n
If Cells(rw, "D") = Cells(r, "D") Then
Cells(rw, "D").Delete
End If
Next rw, r
End Sub

Then?

 

[FryGirl]

Ciao machopicho,

Thank you for the code, but it didn't work quite right. Instead of unique's it pulled over all values.

Also, I had to remove option explicit to get it to work because the variables were not defined.

The code below will work to extract the unique's from A into D and now to get the unique's per D out of B. As I was thinking about this maybe a filter will work using the values in D starting with D2 until the list is exhausted. What do you think?

Sub ExtractUnique()
    Range("A1", Range("A" & Rows.Count).End(xlUp)).AdvancedFilter _
    Action:=xlFilterCopy, CopyToRange:=Range("D1"), Unique:=True
End Sub

 

[machopicho]

mmmm...
It could work... try...