Stacking multiple nonadjacent columns into one (best practice)

[xlyfe]

Hello,

I'm looking for some guidance on how to efficiently take the data from multiple nonadjacent columns, and place it into one long continuous column, while ignoring all blanks in the process.
The new stacked column would look like a stacked version of the columns being used to copy from, but without the blank spaces.
The "copied from" columns are variable in length. They could consist of zero rows, 1 row, or hundreds of rows.

Below is an example of this scenario...

Book1
	D	E	F	G	H	I	J	K	L	M
8	Col 1			Col2			Col 3			Stacked
9	asd			posdop			ksisj3			asd
10	1dfg			i4359						1dfg
11	5er			o238duyh			lskdj2			5er
12	3699g			73hdh			87gsd			3699g
13				02kjfg						posdop
14				067'fdek						i4359
15							63hd			o238duyh
16				jsdfjd8						73hdh
17				42gsd			jk342jkl			02kjfg
18							uiew78342			067'fdek
19										jsdfjd8
20										42gsd
21							kjsdkj8			ksisj3
22										lskdj2
23										87gsd
24							k9843			63hd
25										jk342jkl
26										uiew78342
27										kjsdkj8
28										k9843
Sheet1

What would be the best way for me to approach getting that "Stacked" column to populate itself automatically in that format shown there?

 

[xlyfe]

So I do have some methods to achieve my end result but was hoping to see some fresh ideas on how the guru's here would go about this problem in hopes of learning more advanced and efficient techniques.

Any support that anyone can provide is greatly appreciated.

 

[sandy666]

see Combine multiple columns of table data into a single column with duplicates removed , rename the table in code and the name of the result column respectively

Stacked
02kjfg
067'fdek
1dfg
3699g
42gsd
5er
63hd
73hdh
87gsd
asd
i4359
jk342jkl
jsdfjd8
k9843
kjsdkj8
ksisj3
lskdj2
o238duyh
posdop
uiew78342

 

[xlyfe]

Unfortunately, Excel tables can’t be used in this scenario. Only formulas or VBA due to the structure of my actual workbook.

Thank you so much for your reply tho Sandy!

 

[DRSteele]

Maybe this video will help. Stacking columns with formulas is a tough task. I hear that MS is working on it, though.

 

[sandy666]

Unfortunately, Excel tables can’t be used in this scenario. Only formulas or VBA due to the structure of my actual workbook.

Thank you so much for your reply tho Sandy!

no problem
have a nice day

btw. there can be used named range instead of Excel Table but problem will be with updates about new data

 

[Fluff]

If the other columns will be blank, have a look here

Unique values for an array of n rows and m columns

Hi, I have a table of n rows and m columns. For simplicity, I use a table of 5 rows and 2 columns from B1:C5. I try to find unique values, filtering for blank value (as in C3). I look for a formula that extracts values from both columns and rows - I googled for the usage of UNIQUE function but...

www.mrexcel.com

 

[Fluff]

If you have the new LET function, another option is

+Fluff v2.xlsm
	D	E	F	G	H	I	J	K	L	M	N
8	Col 1			Col2			Col 3			Stacked
9	asd			posdop			ksisj3			asd	asd
10	1dfg			i4359						1dfg	1dfg
11	5er			o238duyh			lskdj2			5er	5er
12	3699g			73hdh			87gsd			3699g	3699g
13				02kjfg						posdop	posdop
14				067'fdek						i4359	i4359
15							63hd			o238duyh	o238duyh
16				jsdfjd8						73hdh	73hdh
17				42gsd			jk342jkl			02kjfg	02kjfg
18							uiew78342			067'fdek	067'fdek
19										jsdfjd8	jsdfjd8
20										42gsd	42gsd
21							kjsdkj8			ksisj3	ksisj3
22										lskdj2	lskdj2
23										87gsd	87gsd
24							k9843			63hd	63hd
25										jk342jkl	jk342jkl
26										uiew78342	uiew78342
27										kjsdkj8	kjsdkj8
28										k9843	k9843
Main

Cell Formulas
Range	Formula
N9:N28	N9	=UNIQUE(LET( ranges, (D9:D50,G9:G50,J9:J50), areas, AREAS(ranges), rows, ROWS(D9:D50), TotalRows, SEQUENCE(areas*rows), NumArea, CEILING.MATH(SEQUENCE(areas*rows),rows)/rows, Row, TotalRows-(NumArea-1)*rows, indx,INDEX(ranges,Row,1,NumArea),FILTER(indx,indx<>"")))
Dynamic array formulas.

Formula taken from here https://www.myonlinetraininghub.com/excel-forum/excel/unique-values-from-two-sources

 

[xlyfe]

If you have the new LET function, another option is

+Fluff v2.xlsm
	D	E	F	G	H	I	J	K	L	M	N
8	Col 1			Col2			Col 3			Stacked
9	asd			posdop			ksisj3			asd	asd
10	1dfg			i4359						1dfg	1dfg
11	5er			o238duyh			lskdj2			5er	5er
12	3699g			73hdh			87gsd			3699g	3699g
13				02kjfg						posdop	posdop
14				067'fdek						i4359	i4359
15							63hd			o238duyh	o238duyh
16				jsdfjd8						73hdh	73hdh
17				42gsd			jk342jkl			02kjfg	02kjfg
18							uiew78342			067'fdek	067'fdek
19										jsdfjd8	jsdfjd8
20										42gsd	42gsd
21							kjsdkj8			ksisj3	ksisj3
22										lskdj2	lskdj2
23										87gsd	87gsd
24							k9843			63hd	63hd
25										jk342jkl	jk342jkl
26										uiew78342	uiew78342
27										kjsdkj8	kjsdkj8
28										k9843	k9843
Main

Cell Formulas
Range	Formula
N9:N28	N9	=UNIQUE(LET( ranges, (D9:D50,G9:G50,J9:J50), areas, AREAS(ranges), rows, ROWS(D9:D50), TotalRows, SEQUENCE(areas*rows), NumArea, CEILING.MATH(SEQUENCE(areas*rows),rows)/rows, Row, TotalRows-(NumArea-1)*rows, indx,INDEX(ranges,Row,1,NumArea),FILTER(indx,indx<>"")))
Dynamic array formulas.

Formula taken from here https://www.myonlinetraininghub.com/excel-forum/excel/unique-values-from-two-sources

This is genius. Thank you for sharing this. ?
Removing the UNIQUE wrapper will generate a non-unique stacked list too. Such a flexible formula.

 

[Fluff]

This is genius.

Agreed, I'm still trying to figure it out