Stack Two Dynamic Columns into One Column

Tashat

Board Regular
Joined
Jan 12, 2005
Messages
137
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFG
1FruitFruits AlphabeticalVegConcatStacked List
2StrawberriesApplesPotatoesWhole ApplesWhole Apples
3ApplesBlueberriesTurnipsWhole BlueberriesWhole Blueberries
4PearsMelonCarrotsWhole MelonWhole Melon
5BlueberriesPearsSwedeWhole PearsWhole Pears
6MelonPineapplePeasWhole PineappleWhole Pineapple
7PineappleStrawberriesWhole StrawberriesWhole Strawberries
8WholePotatoes
9WholeTurnips
10WholeCarrots
11WholeSwede
12WholePeas
Sheet2
Cell Formulas
RangeFormula
B2:B7B2=IF(COUNTA(FruitList)>=ROWS($B$2:B2), INDEX(FruitList, MATCH(SMALL(COUNTIF(FruitList, "<"&FruitList), ROW(B2)), COUNTIF(FruitList, "<"&FruitList), 0)), "")
E2:E12E2=CONCAT("Whole ",B2)
Named Ranges
NameRefers ToCells
FruitList=OFFSET(Sheet2!$A$2,0,0,(COUNTA(Sheet2!$A$2:$A$600)+1))B2:B7
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Since you have 365, you can simplify some of your current formulas, and the stacking is not too bad:

ExampleFormulas.xlsx
ABCDEFG
1FruitFruits AlphabeticalVegConcatStacked List
2StrawberriesApplesPotatoesWhole ApplesWhole Apples
3ApplesBlueberriesTurnipsWhole BlueberriesWhole Blueberries
4PearsMelonCarrotsWhole MelonWhole Melon
5BlueberriesPearsSwedeWhole PearsWhole Pears
6MelonPineapplePeasWhole PineappleWhole Pineapple
7PineappleStrawberriesWhole StrawberriesWhole Strawberries
8Potatoes
9Turnips
10Carrots
11Swede
12Peas
Sheet2
Cell Formulas
RangeFormula
B2:B7B2=SORT(FruitList)
E2:E7E2="Whole "&B2#
G2:G12G2=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
NameRefers ToCells
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
 
Upvote 0
Solution
Since you have 365, you can simplify some of your current formulas, and the stacking is not too bad:

ExampleFormulas.xlsx
ABCDEFG
1FruitFruits AlphabeticalVegConcatStacked List
2StrawberriesApplesPotatoesWhole ApplesWhole Apples
3ApplesBlueberriesTurnipsWhole BlueberriesWhole Blueberries
4PearsMelonCarrotsWhole MelonWhole Melon
5BlueberriesPearsSwedeWhole PearsWhole Pears
6MelonPineapplePeasWhole PineappleWhole Pineapple
7PineappleStrawberriesWhole StrawberriesWhole Strawberries
8Potatoes
9Turnips
10Carrots
11Swede
12Peas
Sheet2
Cell Formulas
RangeFormula
B2:B7B2=SORT(FruitList)
E2:E7E2="Whole "&B2#
G2:G12G2=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
NameRefers ToCells
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
Excel Formula:
="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
ABCDEFG
1Fruits AlphabeticalConcatVegConcat VegStacked List
2ApplesWhole ApplesPotatoes#REF!Whole Apples
3BlueberriesWhole BlueberriesTurnipsWhole Blueberries
4MelonWhole MelonCarrotsWhole Melon
5PearsWhole PearsSwedeWhole Pears
6PineappleWhole PineapplePeasWhole Pineapple
7StrawberriesWhole StrawberriesWhole Strawberries
8Potatoes<--So this should read "Whole Potatoes"
9"Whole "&C2# … I couldn't get this to work for the Veg for some reasonTurnips<--Whole Turnips
10Carrots<--Whole Carrots
11Swede<--Whole Swede
12Peas<--"Whole Peas
Sheet2
Cell Formulas
RangeFormula
A2:A7A2=SORT(FruitList)
B2:B7,D2B2="Whole "&A2#
F2:F12F2=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
NameRefers ToCells
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top