Excel 2007 Dropdown list from multiple column Closed

Question

Hello, 

I hope all is well.  I am back again with some questions and would appreciate any ideas/tips you may have.  

I am trying to create a dropdown list from data in 2 columns.  

Example:
1     6
2     7
3     8
4     9
5     0

And the dropdown list would be:
1
2
3
4
5
6
7
8
9
0

I am not using numbers.  I just used those to illustrate an example.

Any help would be appreciated.

Thank you,
Josh

Configuration: Windows Vista / Safari 537.1

venkat1926 · Answer

DATA IS LIKE THIS FROM a1 TO b5
1	6
2	7
3	8
4	9
5	0



hightglightg data in col. B that is B1 to B5 
control+ and select one after last data in column A that is A6 and control +v and sort(no header) column A only

ExcelJosh · Answer

venkat1926:

Excellent advise.  However, I am trying to do this without physically manipulating the spreadsheet that the columns are in.  Would I be best suited in using a combo box instead of a data validation list?  I would like to manipulate the data with a macro.

Thank you,
Josh

venkat1926 · Answer

not clear "without physically manipulating the "

doe it mean that you do not want to lose the original configuration of the data

will this macro do? you may not need combo box etc


Sub test()
Dim ra  As Range, rb As Range, r As Range, j As Long, k As Long, m As Long
Set ra = Range(Range("A1"), Range("A1").End(xlDown))
Set rb = ra.Offset(0, 1)
Set r = Range("a1").CurrentRegion
j = WorksheetFunction.CountA(ra)
Columns("F:F").Cells.Clear

For k = 1 To j
For m = 1 To 2
Cells(Rows.Count, "F").End(xlUp).Offset(1, 0) = r.Cells(k, m)
Next m
Next k
Range(Range("F1"), Cells(Rows.Count, "F").End(xlUp)).Sort key1:=Range("F1"), header:=xlNo
End Sub

ExcelJosh · Answer

venkat1926:

Great code and will come in handy when I need to reorganize data into 1 column.  

I want to use a drop down.  I want to allow a user to chose from that list and have the data in a cell.  This drop down list will be in multiple rows.

I hope this helps.

Thank you,
Josh

Excel 2007 Dropdown list from multiple column

4 responses

Similar discussions

Subscribe To Our Newsletter!