Stack Two Dynamic Columns into One Column

[Tashat]

Hi All

Can someone help me to combine/stack two columns so they appear as one column please? The issue is that both columns will change in length and could be longer/shorter over time. The example I've added below is just dummy data and the actual data is much longer and more complicated and obviously not fruits

Fruit Example.xlsm
	A	B	C	D	E	F	G
1	Fruit	Fruits Alphabetical	Veg		Concat		Stacked List
2	Strawberries	Apples	Potatoes		Whole Apples		Whole Apples
3	Apples	Blueberries	Turnips		Whole Blueberries		Whole Blueberries
4	Pears	Melon	Carrots		Whole Melon		Whole Melon
5	Blueberries	Pears	Swede		Whole Pears		Whole Pears
6	Melon	Pineapple	Peas		Whole Pineapple		Whole Pineapple
7	Pineapple	Strawberries			Whole Strawberries		Whole Strawberries
8					Whole		Potatoes
9					Whole		Turnips
10					Whole		Carrots
11					Whole		Swede
12					Whole		Peas
Sheet2

Cell Formulas
Range	Formula
B2:B7	B2	=IF(COUNTA(FruitList)>=ROWS($B$2:B2), INDEX(FruitList, MATCH(SMALL(COUNTIF(FruitList, "<"&FruitList), ROW(B2)), COUNTIF(FruitList, "<"&FruitList), 0)), "")
E2:E12	E2	=CONCAT("Whole ",B2)

Named Ranges
Name	Refers To	Cells
FruitList	=OFFSET(Sheet2!$A$2,0,0,(COUNTA(Sheet2!$A$2:$A$600)+1))	B2:B7

 

[RoryA]

Since you have 365, you can simplify some of your current formulas, and the stacking is not too bad:

ExampleFormulas.xlsx
	A	B	C	D	E	F	G
1	Fruit	Fruits Alphabetical	Veg		Concat		Stacked List
2	Strawberries	Apples	Potatoes		Whole Apples		Whole Apples
3	Apples	Blueberries	Turnips		Whole Blueberries		Whole Blueberries
4	Pears	Melon	Carrots		Whole Melon		Whole Melon
5	Blueberries	Pears	Swede		Whole Pears		Whole Pears
6	Melon	Pineapple	Peas		Whole Pineapple		Whole Pineapple
7	Pineapple	Strawberries			Whole Strawberries		Whole Strawberries
8							Potatoes
9							Turnips
10							Carrots
11							Swede
12							Peas
Sheet2

Cell Formulas
Range	Formula
B2:B7	B2	=SORT(FruitList)
E2:E7	E2	="Whole "&B2#
G2:G12	G2	=LET(range1,E2#,range2,Veg_LIst,rows1,ROWS(range1),rows2,ROWS(range2),cols1,COLUMNS(range1),rowindex,SEQUENCE(rows1+rows2),colindex,SEQUENCE(1,cols1),IF(rowindex<=rows1,INDEX(range1,rowindex,colindex),INDEX(range2,rowindex-rows1,colindex)))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
FruitList	=Sheet2!$A$2:INDEX(Sheet2!$A:$A,COUNTA(Sheet2!$A:$A))	B2
Veg_LIst	=Sheet2!$C$2:INDEX(Sheet2!$C:$C,COUNTA(Sheet2!$C:$C))	G2

 

[Tashat]

Since you have 365, you can simplify some of your current formulas, and the stacking is not too bad:

ExampleFormulas.xlsx
	A	B	C	D	E	F	G
1	Fruit	Fruits Alphabetical	Veg		Concat		Stacked List
2	Strawberries	Apples	Potatoes		Whole Apples		Whole Apples
3	Apples	Blueberries	Turnips		Whole Blueberries		Whole Blueberries
4	Pears	Melon	Carrots		Whole Melon		Whole Melon
5	Blueberries	Pears	Swede		Whole Pears		Whole Pears
6	Melon	Pineapple	Peas		Whole Pineapple		Whole Pineapple
7	Pineapple	Strawberries			Whole Strawberries		Whole Strawberries
8							Potatoes
9							Turnips
10							Carrots
11							Swede
12							Peas
Sheet2

Cell Formulas
Range	Formula
B2:B7	B2	=SORT(FruitList)
E2:E7	E2	="Whole "&B2#
G2:G12	G2	=LET(range1,E2#,range2,Veg_LIst,rows1,ROWS(range1),rows2,ROWS(range2),cols1,COLUMNS(range1),rowindex,SEQUENCE(rows1+rows2),colindex,SEQUENCE(1,cols1),IF(rowindex<=rows1,INDEX(range1,rowindex,colindex),INDEX(range2,rowindex-rows1,colindex)))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
FruitList	=Sheet2!$A$2:INDEX(Sheet2!$A:$A,COUNTA(Sheet2!$A:$A))	B2
Veg_LIst	=Sheet2!$C$2:INDEX(Sheet2!$C:$C,COUNTA(Sheet2!$C:$C))	G2

This solution is absolutely perfect! My only issue is that I was a bit silly when I did my example data and I oversimplified it hoping I would be able to adapt the formula - however, the LET function seems to be well beyond my abilities and I'm not sure how to adapt it. I actually have the first fruit list which was in Column A on a different sheet. Meaning column A actually is the Sorted Fruit list. I also wanted to concatonate the Veg column (C) to be "Whole Potatoes" etc. Meaning I have an additional column as shown below. It's because of this I can't get your LET function to work. Are you able to help me adjust it so it works. I don't seem to be able to get the

="Whole "&C2#

formula to work for the veg column either. My apologies for wasting your time initially, but thank you so much for your help again. I'm learning lots from this.

Fruit Example.xlsm
	A	B	C	D	E	F	G
1	Fruits Alphabetical	Concat	Veg	Concat Veg		Stacked List
2	Apples	Whole Apples	Potatoes	#REF!		Whole Apples
3	Blueberries	Whole Blueberries	Turnips			Whole Blueberries
4	Melon	Whole Melon	Carrots			Whole Melon
5	Pears	Whole Pears	Swede			Whole Pears
6	Pineapple	Whole Pineapple	Peas			Whole Pineapple
7	Strawberries	Whole Strawberries				Whole Strawberries
8						Potatoes	<--So this should read "Whole Potatoes"
9				"Whole "&C2# … I couldn't get this to work for the Veg for some reason		Turnips	<--Whole Turnips
10						Carrots	<--Whole Carrots
11						Swede	<--Whole Swede
12						Peas	<--"Whole Peas
Sheet2

Cell Formulas
Range	Formula
A2:A7	A2	=SORT(FruitList)
B2:B7,D2	B2	="Whole "&A2#
F2:F12	F2	=LET(range1,B2#,range2,Veg_LIst,rows1,ROWS(range1),rows2,ROWS(range2),cols1,COLUMNS(range1),rowindex,SEQUENCE(rows1+rows2),colindex,SEQUENCE(1,cols1),IF(rowindex<=rows1,INDEX(range1,rowindex,colindex),INDEX(range2,rowindex-rows1,colindex)))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
FruitList	=Sheet3!$A$2:INDEX(Sheet3!$A:$A,COUNTA(Sheet3!$A:$A))	A2
Veg_LIst	=Sheet2!$C$2:INDEX(Sheet2!$C:$C,COUNTA(Sheet2!$C:$C))	F2, D2

 

[RoryA]

Your veg list isn't a dynamic array, so you can't use C2#. You have to just use C2 and copy the formula down.

 

[Tashat]

Your veg list isn't a dynamic array, so you can't use C2#. You have to just use C2 and copy the formula down.

Ah YES! Of course. Thank you so much. ?‍