Stack multiple columns into one column in Excel 365 Mac

[smashesxl]

I currently have my data in different columns after using a Text To Columns feature. Now what I want to do is to put them all into one column. Can you please advise what formula to use or is there a custom script please? I have got Office 365 for Mac. Thank you . I am computer literate but not familiar on using VBA, however if the answer is in VBA if I can kindly ask for some guidance please on how to implement it that'd be great. Thank you so much everyone

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future, consider this for copyable sample data: XL2BB
The easier it is for helpers, the faster you will generally get help.

Do you have the TOCOL function?

23 02 07.xlsm
	A	B	C	D	E
1
2	Name 1	Name 6	Name 11		Name 1
3	Name 2	Name 7	Name 12		Name 2
4	Name 3	Name 8	Name 13		Name 3
5	Name 4	Name 9	Name 14		Name 4
6	Name 5	Name 10	Name 15		Name 5
7					Name 6
8					Name 7
9					Name 8
10					Name 9
11					Name 10
12					Name 11
13					Name 12
14					Name 13
15					Name 14
16					Name 15
Sheet2

Cell Formulas
Range	Formula
E2:E16	E2	=TOCOL(A2:C6,1,1)
Dynamic array formulas.

 

[smashesxl]

Hi. Many thanks for the reply. I don't seem to have a TOCOL function. Do you have any idea why? Thank you

 

[Peter_SSs]

I don't seem to have a TOCOL function. Do you have any idea why?

Check the 'Availabilty' section here.

Until you have TOCOL, try this instead.

23 02 07.xlsm
	A	B	C	D	E
1
2	Name 1	Name 6	Name 11		Name 1
3	Name 2	Name 7	Name 12		Name 2
4	Name 3	Name 8	Name 13		Name 3
5	Name 4	Name 9	Name 14		Name 4
6	Name 5	Name 10	Name 15		Name 5
7					Name 6
8					Name 7
9					Name 8
10					Name 9
11					Name 10
12					Name 11
13					Name 12
14					Name 13
15					Name 14
16					Name 15
17
Sheet2

Cell Formulas
Range	Formula
E2:E16	E2	=LET(r,A2:C6,w,ROWS(r),s,SEQUENCE(w*COLUMNS(r),,0),INDEX(r,MOD(s,w)+1,INT(s/w)+1))
Dynamic array formulas.

 

[smashesxl]

Hi. Really appreciate the reply. I found the TOCOl function . Could I ask you to please help me understand a scan by row vs a scan by column a bit better? I did not really understand this aspect, also from that Microsoft link you provided it was not clear... Thank you

 

[Peter_SSs]

Could I ask you to please help me understand a scan by row vs a scan by column a bit better?

Sure. The scan by argument (last argument) is theoretically a TRUE/FALSE argument but 1/0 can be used instead and that is what I had used in my earlier post.
The argument simply tells the function whether to go down then across or across and then down.
Should be clear enough from the examples below I think.

23 02 07.xlsm
	A	B	C	D	E	F
1					Scan by column	Scan by column = FALSE means scan by row
2	Name 1	Name 6	Name 11		Name 1	Name 1
3	Name 2	Name 7	Name 12		Name 2	Name 6
4	Name 3	Name 8	Name 13		Name 3	Name 11
5	Name 4	Name 9	Name 14		Name 4	Name 2
6	Name 5	Name 10	Name 15		Name 5	Name 7
7					Name 6	Name 12
8					Name 7	Name 3
9					Name 8	Name 8
10					Name 9	Name 13
11					Name 10	Name 4
12					Name 11	Name 9
13					Name 12	Name 14
14					Name 13	Name 5
15					Name 14	Name 10
16					Name 15	Name 15
17
Sheet2

Cell Formulas
Range	Formula
E2:E16	E2	=TOCOL(A2:C6,1,TRUE)
F2:F16	F2	=TOCOL(A2:C6,1,FALSE)
Dynamic array formulas.

 

[alansidman]

An alternative solution is with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Columns",{"Value"})
in
    #"Removed Other Columns"

 

[Fluff]

For future reference.

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How do I stack multiple columns into one column in Excel 365 Mac
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.