Excel 2007 Dropdown list from multiple column

Closed
ExcelJosh Posts 12 Registration date Wednesday August 8, 2012 Status Contributor Last seen October 12, 2012 - Aug 8, 2012 at 09:58 AM
ExcelJosh Posts 12 Registration date Wednesday August 8, 2012 Status Contributor Last seen October 12, 2012 - Aug 10, 2012 at 03:28 PM
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

4 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Aug 9, 2012 at 01:40 AM
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
1
ExcelJosh Posts 12 Registration date Wednesday August 8, 2012 Status Contributor Last seen October 12, 2012
Aug 9, 2012 at 08:27 AM
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
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Aug 9, 2012 at 11:16 PM
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
0
ExcelJosh Posts 12 Registration date Wednesday August 8, 2012 Status Contributor Last seen October 12, 2012
Aug 10, 2012 at 03:28 PM
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
0