Excel 2007 Dropdown list from multiple column [Closed]

Report
Posts
12
Registration date
Wednesday August 8, 2012
Status
Contributor
Last seen
October 12, 2012
-
Posts
12
Registration date
Wednesday August 8, 2012
Status
Contributor
Last seen
October 12, 2012
-
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

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
12
Registration date
Wednesday August 8, 2012
Status
Contributor
Last seen
October 12, 2012

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
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
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
Posts
12
Registration date
Wednesday August 8, 2012
Status
Contributor
Last seen
October 12, 2012

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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!