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?

 

[etax]

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

Hi,How would you revise the same formula if it is from multiple worksheets.

 

[xlyfe]

Hi,How would you revise the same formula if it is from multiple worksheets.

You’d adjust each range to reference each worksheet/workbook’s range instead. O365 handles this nicely. ??

 

[etax]

I did but got Value error when I adjusted the range.

UNIQUE(LET(ranges,(LAM!C6:C1188,PPI!C6:C1188,MPI!C6:C1188,ANUJ!C6:C1188),areas,AREAS(ranges),rows,ROWS(C6:C1188),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<>"")))

 

[Fluff]

You cannot index ranges on multiple sheets like that.

as you have started a new thread for your question Compile multiple worksheets data into one I will close this one.

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.