Combine 2 Columns of Different Lengths for More than 1 set of Data in Excel

[KeKemp]

Hello,

I am new to posting so I apologize if this is written incorrectly or confusing.

I am looking for a formula or Macro that will allow me to combine 2 columns of different lengths. If it is a Macro I need it to be able to run based on a different cell each time.

So if I use the example data below I am trying to get a new column for Data Set 1 that has:

A1
A2
A3
B1
B2
B3 (etc.)

However, then I want to use the same method toward Data Set 2 even though it has different column lengths an will not be in the same location on a sheet as Data Set 1

Example:

Data Set 1			Data Set 2
Program	Location		Program	Location
A	1​		A	1​
B	2​		B	2​
C	3​		C	3​
D			D	4​
E			E	5​
F			F	6​
G			G
H			H
			I
			J
			K

Thank you.

 

[Zot]

The macro will create list in the 3rd column (to the right of Location column).
How to use:
Select anchor cell which is A in Program column. Alt+F8 and run macro

Sub CombineList()

Dim n As Long, m As Long
Dim cell_Prog As Range, rngProg As Range
Dim cell_Loc As Range, rngLoc As Range

n = ActiveCell.Row
m = ActiveCell.Offset(, 2).Column

Set rngProg = Range(ActiveCell, ActiveCell.End(xlDown))
Set rngLoc = Range(ActiveCell.Offset(, 1), ActiveCell.Offset(, 1).End(xlDown))

For Each cell_Prog In rngProg
    For Each cell_Loc In rngLoc
        Cells(n, m) = cell_Prog & cell_Loc
        n = n + 1
    Next
Next

End Sub

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[KeKemp]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I have added this information to my profile Thank you.

 

[KeKemp]

The macro will create list in the 3rd column (to the right of Location column).
How to use:
Select anchor cell which is A in Program column. Alt+F8 and run macro

Sub CombineList()

Dim n As Long, m As Long
Dim cell_Prog As Range, rngProg As Range
Dim cell_Loc As Range, rngLoc As Range

n = ActiveCell.Row
m = ActiveCell.Offset(, 2).Column

Set rngProg = Range(ActiveCell, ActiveCell.End(xlDown))
Set rngLoc = Range(ActiveCell.Offset(, 1), ActiveCell.Offset(, 1).End(xlDown))

For Each cell_Prog In rngProg
    For Each cell_Loc In rngLoc
        Cells(n, m) = cell_Prog & cell_Loc
        n = n + 1
    Next
Next

End Sub

This was exactly what I was looking for! Thank you!

 

[Fluff]

Thanks for that, with a formula

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	Data Set 1			Data Set 2
2	Program	Location		Program	Location
3	A	1		A	1			A	A
4	B	2		B	2			B	B
5	C	3		C	3			C	C
6	D			D	4			D	D
7	E			E	5			E	E
8	F			F	6			F	F
9	G			G				G	G
10	H			H				H	H
11				I				1	I
12				J				2	J
13				K				3	K
14									1
15									2
16									3
17									4
18									5
19									6
Main

Cell Formulas
Range	Formula
H3:H13	H3	=LET(Rng,A3:B10,r,ROWS(Rng),s,SEQUENCE(r*COLUMNS(Rng),,0),a,INDEX(Rng, MOD(s,r)+1,INT(s/r)+1),FILTER(a,a<>""))
I3:I19	I3	=LET(Rng,D3:E13,r,ROWS(Rng),s,SEQUENCE(r*COLUMNS(Rng),,0),a,INDEX(Rng, MOD(s,r)+1,INT(s/r)+1),FILTER(a,a<>""))
Dynamic array formulas.

Then all you need to do is change the range in the variable Rng for any data set