Combine two lists into rows and columns

[rainmaker1011]

Hi,

I have two lists of values and I need to combine them into one big list. See example

List A has values, List B has values. Each value from List A gets each value from List B. I need the output, where in column A I have List A values and in column B I have list B values.

Can you please suggest a nice VBA to solve this? In reality I have 300 entries in List A and 30 entries in List B

Thanks
Marek

 

[Joe4]

What is the exact range where:
- List A exists
- List B exists
- you would like the results to go to?

 

[rainmaker1011]

Thanks, I found the solution

Sub ListAllCombinations()
'Updateby Extendoffice
Dim xDRg1, xDRg2, xDRg3 As Range
Dim xRg  As Range
Dim xStr As String
Dim xFN1, xFN2, xFN3 As Integer
Dim xSV1, xSV2, xSV3 As String
Set xDRg1 = Range("A2:A5")  'First column data
Set xDRg2 = Range("B2:B4")  'Second column data
Set xDRg3 = Range("C2:C4")  'Third column data
xStr = "-"   'Separator
Set xRg = Range("E2")  'Output cell
For xFN1 = 1 To xDRg1.Count
    xSV1 = xDRg1.Item(xFN1).Text
    For xFN2 = 1 To xDRg2.Count
        xSV2 = xDRg2.Item(xFN2).Text
      For xFN3 = 1 To xDRg3.Count
        xSV3 = xDRg3.Item(xFN3).Text
        xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3
        Set xRg = xRg.Offset(1, 0)
       Next
    Next
Next
End Sub

 

[Fluff]

As you have 365 you could do it with a formula

Fluff.xlsm
	A	B	C	D	E
1
2	Dept 1	Country 1		Dept 1	Country 1
3	Dept 2	Country 2		Dept 1	Country 2
4	Dept 3	Country 3		Dept 1	Country 3
5	Dept 4	Country 4		Dept 1	Country 4
6	Dept 5			Dept 2	Country 1
7	Dept 6			Dept 2	Country 2
8				Dept 2	Country 3
9				Dept 2	Country 4
10				Dept 3	Country 1
11				Dept 3	Country 2
12				Dept 3	Country 3
13				Dept 3	Country 4
14				Dept 4	Country 1
15				Dept 4	Country 2
16				Dept 4	Country 3
17				Dept 4	Country 4
18				Dept 5	Country 1
19				Dept 5	Country 2
20				Dept 5	Country 3
21				Dept 5	Country 4
22				Dept 6	Country 1
23				Dept 6	Country 2
24				Dept 6	Country 3
25				Dept 6	Country 4
Sheet6

Cell Formulas
Range	Formula
D2:E25	D2	=LET(a,FILTER(A2:A500,A2:A500<>""),b,FILTER(B2:B500,B2:B500<>""),HSTACK(TOCOL(IF(SEQUENCE(,ROWS(b)),a)),TOCOL(IF(SEQUENCE(,ROWS(a)),b),,1)))
Dynamic array formulas.

 

[Joe4]

OK. Thanks for posting back. You always want to be sure to do that if you have a solution so that no one continues to work on a question that is no longer needed.

BTW, there are some slight issues with your variable declarations. It probably won't affect the output much, but could be problematic under the right circumstances (or at least may not have the desired effect).
Variable declarations lines like this:

Dim xDRg1, xDRg2, xDRg3 As Range

do NOT do what you think they do.
Only xDRg3 will be declared as a Range. The other two will actually be variant.

If you want to restrict the variables to certain data types, each one needs to be declared explicitly, like this:

Dim xDRg1 As Range, xDRg2 As Range, xDRg3 As Range

or this:

Dim xDRg1 As Range
Dim xDRg2 As Range
Dim xDRg3 As Range