Combining lists!

[needexcelhelp22]

Hello,

How would I do the following, if my workbook looks like:

Column A:
Place1
Place2
Place3
etc...


Column B:
Weather1
Weather2
Weather3
Weather4
Weather5
etc...

I would to have all different combinations separated so that it looks like (in different columns):
Column A/Column B:
Place 1/Weather 1
Place 1/Weather 2
Place 1/Weather 3
.....
Place3/Weather 5

Is there any macro I could record for this? My issue is that I may be adding new data into either column 1 or column 2 and I would like it to be easily updated.

 

[JST013]

Sub wombocombo()
    Dim places As Range
    Dim ws As Worksheet
    Dim weathers As Range
    Dim col
    Set ws = ThisWorkbook.ActiveSheet
    Set places = ws.Range("Table1[Places]")
    Set weathers = ws.Range("Table1[Weathers]")
    
    col = 4
    
    For Each place In places
        For Each weather In weathers
            Cells(1, col) = place & "/" & weather
            col = col + 1
        Next weather
    Next place
End Sub

this should do what you're asking I believe...It does not however update automatically, whenever you update the lists you will need to rerun the code...

It writes the values in row 1 column "D" and then moves to the next column

 

[hiker95]

needexcelhelp22,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

Here is another macro solution for you to consider, that uses three arrays in memory, and, writes the results to column E.

Each time you add new information to columns A, and, B, and, run the macro again, the results column E will be cleared, and, the new results will be listed.

You can change the raw data worksheet name in the macro.

Sample raw data:

Excel 2007
	A	B	C	D	E
1	Place1	Weather1
2	Place2	Weather2
3	Place3	Weather3
4		Weather4
5		Weather5
6
7
8
9
10
11
12
13
14
15
16
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E
1	Place1	Weather1			Place1/Weather1
2	Place2	Weather2			Place1/Weather2
3	Place3	Weather3			Place1/Weather3
4		Weather4			Place1/Weather4
5		Weather5			Place1/Weather5
6					Place2 /Weather1
7					Place2 /Weather2
8					Place2 /Weather3
9					Place2 /Weather4
10					Place2 /Weather5
11					Place3/Weather1
12					Place3/Weather2
13					Place3/Weather3
14					Place3/Weather4
15					Place3/Weather5
16
Sheet1

Then you add new information in column A, and, B:

Excel 2007
	A	B	C	D	E
1	Place1	Weather1			Place1/Weather1
2	Place2	Weather2			Place1/Weather2
3	Place3	Weather3			Place1/Weather3
4	Place4	Weather4			Place1/Weather4
5		Weather5			Place1/Weather5
6		Weather6			Place2 /Weather1
7		Weather7			Place2 /Weather2
8					Place2 /Weather3
9					Place2 /Weather4
10					Place2 /Weather5
11					Place3/Weather1
12					Place3/Weather2
13					Place3/Weather3
14					Place3/Weather4
15					Place3/Weather5
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Sheet1

And, run the macro again, and, you get this:

Excel 2007
	A	B	C	D	E
1	Place1	Weather1			Place1/Weather1
2	Place2	Weather2			Place1/Weather2
3	Place3	Weather3			Place1/Weather3
4	Place4	Weather4			Place1/Weather4
5		Weather5			Place1/Weather5
6		Weather6			Place1/Weather6
7		Weather7			Place1/Weather7
8					Place2 /Weather1
9					Place2 /Weather2
10					Place2 /Weather3
11					Place2 /Weather4
12					Place2 /Weather5
13					Place2 /Weather6
14					Place2 /Weather7
15					Place3/Weather1
16					Place3/Weather2
17					Place3/Weather3
18					Place3/Weather4
19					Place3/Weather5
20					Place3/Weather6
21					Place3/Weather7
22					Place4/Weather1
23					Place4/Weather2
24					Place4/Weather3
25					Place4/Weather4
26					Place4/Weather5
27					Place4/Weather6
28					Place4/Weather7
29
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub Combine_ColA_ColB()
' hiker95, 06/17/2015, ME862166
Dim a As Variant, b As Variant, o As Variant
Dim i As Long, ii As Long, j As Long, n As Long
With Sheets("Sheet1")   '<-- you can change the sheet name here
  .Columns(5).ClearContents
  a = .Range("A1", .Cells(Rows.Count, "A").End(xlUp))
  b = .Range("B1", .Cells(Rows.Count, "B").End(xlUp))
  ReDim o(1 To (UBound(a, 1) * UBound(b, 1)), 1 To 1)
  For i = LBound(a, 1) To UBound(a, 1)
    For ii = LBound(b, 1) To UBound(b, 1)
      j = j + 1: o(j, 1) = a(i, 1) & "/" & b(ii, 1)
    Next ii
  Next i
  Range("E1").Resize(UBound(o, 1)) = o
  .Columns(5).AutoFit
  Erase a: Erase b: Erase o
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the Combine_ColA_ColB macro.

 

[needexcelhelp22]

I apologize for not including the above information. I am using Mac for Excel 2011 I believe

 

[hiker95]

needexcelhelp22,

Have you tried the macro?