Vertically stack columns

MossyPants · Oct 16, 2022

I'm looking for a way to vertically combine multiple columns of different lengths into a single master column. The columns' rows may be added or deleted from them in the future. When that happens, the master column should auto-update. I used to have a LET function that performed this task, but our company's Excel has been downgraded recently, and LET (or VSTACK) are no longer available.
Here is the worksheet. I would like every Set to be consolidated into column A.

Book1.xlsx

A

B

C

D

1

All Sets

SetA

SetB

SetC

2

3

JB00 Fluorescent White Cotton

Mz-1 Baby Food

EMPA-101 Carbon black/olive oil

4

JB01 Carbon on Cotton

Mz-2 Tea

EMPA-104 Carbon black/olive oil on PolyCotton

5

JB02 Protein on Cotton

Mz-3 Spagetti Sauce

EMPA-106 Carbon black/mineral oil

6

JB03 Sebum on Cotton

Mz-4 Makeup

EMPA-107 Carbon black/olive oil on Wool

7

JB04 Water Soluble Dirt

Mz-5 Clay

EMPA-111 Blood

8

JB05 Clay Type Dirt Cloth

Mz-6 Carrot Juice

EMPA-112 Cocoa

9

Mz-7 ACD Blood

EMPA-114 Red wine

10

Mz-8 Curry sauce

EMPA-115 Immedial black

11

Mz-9 Anchor Butter

EMPA-116 Blood/milk/ink

12

Mz-10 Grass

EMPA-117 Blood/milk/ink on PolyCotton

13

Mz-11 Chocolate Desert

EMPA-118 Sebum/pigment

14

Mz-12 Red Wine

EMPA-119 Sebum/pigment on PolyCotton

15

Mz-13 Used Engine Oil

EMPA-120 Grease/quartz/iron oxide

16

Mz-14 Animal Fat & Dye

EMPA-122 Cotton soiled with Red Wine AGED

17

Mz-15 Garden Peat

EMPA-123 Cotton soiled Low Temp Washing

18

Mz-16 Blank

EMPA-128/1 Cotton Jeans, Indigo/sulfur black soiled

19

GC7-1 Heinz Banana Baby Food

EMPA-130 Cotton dyed with Direct Red 83.1

20

GC7-2 Heinz Apple & Banana BF

EMPA-131 Acid blue 113 on Polyamide

21

GC7-3 Heinz Apple & Mango BF

EMPA-132 Cotton dyed with Direct black 22

22

GC7-4 Heinz Spaghetti Bolognaise BF

EMPA-133 Cotton dyed with Direct blue 71

23

GC7-5 Veg. & Turkey (Hipp BF)

24

GC7-6 Bisto Gravy

25

GC7-7 Coleman's Mustard

26

GC7-8 Heinz Sun Dried Tomato Sauce

27

GC7-9 Tomato Soup (Campbell's)

28

GC7-10 Heinz Spaghetti Sauce

29

GC7-11 HP Brown Sauce

30

GC7-12 Colemans Beef Casserole

31

GC7-13 Homepride Chilli Con Carne

32

GC7-14 Asda Chocolate Ice Cream

33

GC7-15 Frijj Chocolate Milkshake

34

GC7-16 Nestle Chocolate Build Up

35

GC8-1 Gerber Banana BF

36

GC8-2 Gerber Apple & Banana BF (Mixed Cereal)

37

GC8-3 Gerber Apple Cinnamon Oatmeal BF

38

GC8-4 Gerber Banana Raspberry Oatmeal BF

39

GC8-5 Quaker Original Oatmeal

40

GC8-6 Quaker Apples & Cinnamon Oatmeal

41

GC8-7 Quaker Cinnamon Spice Oatmeal

42

GC8-8 Quaker Maple & Brown Sugar Oatmeal

43

GC8-9 Gerber Sweet Potato BF

StainList

Peter_SSs · Oct 16, 2022

MossyPants said:
Excel has been downgraded recently, and LET (or VSTACK) are no longer available.

How would we know what functions are available?
Have you gone back to Excel 2019 or some other particular version?
Are you able to use any dynamic array formulas that automatically 'spill' into the required rows/columns?

MossyPants · Oct 16, 2022

Peter_SSs said:
How would we know what functions are available?
Have you gone back to Excel 2019 or some other particular version?
Are you able to use any dynamic array formulas that automatically 'spill' into the required rows/columns?

It's still Excel 365, but I believe now we're on semi-annual enterprise channel, instead of current channel. No admin privileges, either, so I cannot switch channels.
I am not sure what formulas are or aren't available. I just know that my LET and VSTACK formulas stopped working after our system "upgrade".

Peter_SSs · Oct 16, 2022

MossyPants said:
It's still Excel 365

OK, in that case you could try this (adjust row 50 if required) in A3 unless your data columns are actually much longer than your samples in which case the formula may return an error.

Excel Formula:

=SUBSTITUTE(FILTERXML("<p><c>"&SUBSTITUTE(TEXTJOIN("</c><c>",1,B3:B50,C3:C50,D3:D50),"&","#")&"</c></p>","//c"),"#","&")

MossyPants · Oct 17, 2022

Peter_SSs said:
OK, in that case you could try this (adjust row 50 if required) in A3 unless your data columns are actually much longer than your samples in which case the formula may return an error.

Excel Formula:

=SUBSTITUTE(FILTERXML("<p><c>"&SUBSTITUTE(TEXTJOIN("</c><c>",1,B3:B50,C3:C50,D3:D50),"&","#")&"</c></p>","//c"),"#","&")

Wow, what a neat way of doing this! It worked...until one of my data cells happened to contain a ">" symbol. Any work-around?

Peter_SSs · Oct 17, 2022

MossyPants said:
.until one of my data cells happened to contain a ">" symbol

That probably means that you also had one or more with a "<" symbol. You could try this version but if any more 'difficult' characters appear then it would probably be best to look for an alternative method.

Excel Formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FILTERXML("<p><c>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("^",1,B3:B50,C3:C50,D3:D50),"&","#"),"<","!"),">","@"),"^","</c><c>")&"</c></p>","//c"),"#","&"),"!","<"),"@",">")

Peter_SSs · Oct 17, 2022

Peter_SSs said:
if any more 'difficult' characters appear then it would probably be best to look for an alternative method.

.. vba would be one such method.
However, another formula method that is not so sensitive to what characters are in your data would be this - but ..

It does require to be copied down as far as might ever be needed, and
Assumes that there are no formulas returning "" in columns B:D (though an adjustment could be made for that if required)

This formula would go in A3:

Excel Formula:

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$3:B$50)/(B$3:B$50<>""),ROWS(A$3:A3))),
IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(B$3:B$50)/(C$3:C$50<>""),ROWS(A$3:A3)-COUNTA(B$3:B$50))),
IFERROR(INDEX(D:D,AGGREGATE(15,6,ROW(B$3:B$50)/(D$3:D$50<>""),ROWS(A$3:A3)-COUNTA(B$3:C$50))),"")))

Kerryx · Oct 17, 2022

Use power query and option to

Delete the Attribute column and load complete list back to column A

Value	SetA	SetB	SetC
JB00 Fluorescent White Cotton	JB00 Fluorescent White Cotton	Mz-1 Baby Food	EMPA-101 Carbon black/olive oil
Mz-1 Baby Food	JB01 Carbon on Cotton	Mz-2 Tea	EMPA-104 Carbon black/olive oil on PolyCotton
EMPA-101 Carbon black/olive oil	JB02 Protein on Cotton	Mz-3 Spagetti Sauce	EMPA-106 Carbon black/mineral oil
JB01 Carbon on Cotton	JB03 Sebum on Cotton	Mz-4 Makeup	EMPA-107 Carbon black/olive oil on Wool
Mz-2 Tea	JB04 Water Soluble Dirt	Mz-5 Clay	EMPA-111 Blood
EMPA-104 Carbon black/olive oil on PolyCotton	JB05 Clay Type Dirt Cloth	Mz-6 Carrot Juice	EMPA-112 Cocoa
JB02 Protein on Cotton	new item 1	Mz-7 ACD Blood	EMPA-114 Red wine
Mz-3 Spagetti Sauce	new item1/2	Mz-8 Curry sauce	EMPA-115 Immedial black
EMPA-106 Carbon black/mineral oil		Mz-9 Anchor Butter	EMPA-116 Blood/milk/ink
JB03 Sebum on Cotton		Mz-10 Grass	EMPA-117 Blood/milk/ink on PolyCotton
Mz-4 Makeup		Mz-11 Chocolate Desert	EMPA-118 Sebum/pigment
EMPA-107 Carbon black/olive oil on Wool		Mz-12 Red Wine	EMPA-119 Sebum/pigment on PolyCotton
JB04 Water Soluble Dirt		Mz-13 Used Engine Oil	EMPA-120 Grease/quartz/iron oxide
Mz-5 Clay		Mz-14 Animal Fat & Dye	EMPA-122 Cotton soiled with Red Wine AGED
EMPA-111 Blood		Mz-15 Garden Peat	EMPA-123 Cotton soiled Low Temp Washing
JB05 Clay Type Dirt Cloth		Mz-16 Blank	EMPA-128/1 Cotton Jeans, Indigo/sulfur black soiled
Mz-6 Carrot Juice		GC7-1 Heinz Banana Baby Food	EMPA-130 Cotton dyed with Direct Red 83.1
EMPA-112 Cocoa		GC7-2 Heinz Apple & Banana BF	EMPA-131 Acid blue 113 on Polyamide
new item 1		GC7-3 Heinz Apple & Mango BF	EMPA-132 Cotton dyed with Direct black 22
Mz-7 ACD Blood		GC7-4 Heinz Spaghetti Bolognaise BF	EMPA-133 Cotton dyed with Direct blue 71
EMPA-114 Red wine		GC7-5 Veg. & Turkey (Hipp BF)	new item3
new item1/2		GC7-6 Bisto Gravy
Mz-8 Curry sauce		GC7-7 Coleman's Mustard
EMPA-115 Immedial black		GC7-8 Heinz Sun Dried Tomato Sauce
Mz-9 Anchor Butter		GC7-9 Tomato Soup (Campbell's)
EMPA-116 Blood/milk/ink		GC7-10 Heinz Spaghetti Sauce
Mz-10 Grass		GC7-11 HP Brown Sauce
EMPA-117 Blood/milk/ink on PolyCotton		GC7-12 Colemans Beef Casserole
Mz-11 Chocolate Desert		GC7-13 Homepride Chilli Con Carne
EMPA-118 Sebum/pigment		GC7-14 Asda Chocolate Ice Cream
Mz-12 Red Wine		GC7-15 Frijj Chocolate Milkshake
EMPA-119 Sebum/pigment on PolyCotton		GC7-16 Nestle Chocolate Build Up
Mz-13 Used Engine Oil		GC8-1 Gerber Banana BF
EMPA-120 Grease/quartz/iron oxide		GC8-2 Gerber Apple & Banana BF (Mixed Cereal)
Mz-14 Animal Fat & Dye		GC8-3 Gerber Apple Cinnamon Oatmeal BF
EMPA-122 Cotton soiled with Red Wine AGED		GC8-4 Gerber Banana Raspberry Oatmeal BF
Mz-15 Garden Peat		GC8-5 Quaker Original Oatmeal
EMPA-123 Cotton soiled Low Temp Washing		<GC8-6 Quaker Apples & Cinnamon Oatmeal
Mz-16 Blank		GC8-7 Quaker Cinnamon Spice Oatmeal
EMPA-128/1 Cotton Jeans, Indigo/sulfur black soiled		GC8-8 Quaker Maple & Brown Sugar Oatmeal
GC7-1 Heinz Banana Baby Food		>GC8-9 Gerber Sweet Potato BF
EMPA-130 Cotton dyed with Direct Red 83.1		New item
GC7-2 Heinz Apple & Banana BF		new item2
EMPA-131 Acid blue 113 on Polyamide
GC7-3 Heinz Apple & Mango BF
EMPA-132 Cotton dyed with Direct black 22
GC7-4 Heinz Spaghetti Bolognaise BF
EMPA-133 Cotton dyed with Direct blue 71
GC7-5 Veg. & Turkey (Hipp BF)
new item3
GC7-6 Bisto Gravy
GC7-7 Coleman's Mustard
GC7-8 Heinz Sun Dried Tomato Sauce
GC7-9 Tomato Soup (Campbell's)
GC7-10 Heinz Spaghetti Sauce
GC7-11 HP Brown Sauce
GC7-12 Colemans Beef Casserole
GC7-13 Homepride Chilli Con Carne
GC7-14 Asda Chocolate Ice Cream
GC7-15 Frijj Chocolate Milkshake
GC7-16 Nestle Chocolate Build Up
GC8-1 Gerber Banana BF
GC8-2 Gerber Apple & Banana BF (Mixed Cereal)
GC8-3 Gerber Apple Cinnamon Oatmeal BF
GC8-4 Gerber Banana Raspberry Oatmeal BF
GC8-5 Quaker Original Oatmeal
<GC8-6 Quaker Apples & Cinnamon Oatmeal
GC8-7 Quaker Cinnamon Spice Oatmeal
GC8-8 Quaker Maple & Brown Sugar Oatmeal
>GC8-9 Gerber Sweet Potato BF
New item
new item2

MossyPants · Oct 17, 2022

Peter_SSs said:
That probably means that you also had one or more with a "<" symbol. You could try this version but if any more 'difficult' characters appear then it would probably be best to look for an alternative method.

Excel Formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FILTERXML("<p><c>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("^",1,B3:B50,C3:C50,D3:D50),"&","#"),"<","!"),">","@"),"^","</c><c>")&"</c></p>","//c"),"#","&"),"!","<"),"@",">")

Yes, more difficult characters were encountered, unfortunately. That column is a few thousand items long

Peter_SSs said:
.. vba would be one such method.
However, another formula method that is not so sensitive to what characters are in your data would be this - but ..

It does require to be copied down as far as might ever be needed, and

Assumes that there are no formulas returning "" in columns B:D (though an adjustment could be made for that if required)

This formula would go in A3:

Excel Formula:

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$3:B$50)/(B$3:B$50<>""),ROWS(A$3:A3))), IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(B$3:B$50)/(C$3:C$50<>""),ROWS(A$3:A3)-COUNTA(B$3:B$50))), IFERROR(INDEX(D:D,AGGREGATE(15,6,ROW(B$3:B$50)/(D$3:D$50<>""),ROWS(A$3:A3)-COUNTA(B$3:C$50))),"")))

This seems to work for now. Thank you!
I do agree that perhaps VBA would be a more elegant long-term solution...probably via a public function.

Peter_SSs · Oct 18, 2022

MossyPants said:
Yes, more difficult characters were encountered, unfortunately. That column is a few thousand items long

This seems to work for now. Thank you!
I do agree that perhaps VBA would be a more elegant long-term solution...probably via a public function.

You could try this user-defined function then.

VBA Code:

Function STACKCOLS(rng As Range) As Variant
  Dim Data As Variant, a As Variant
  Dim r As Long, c As Long, k As Long
  
  Data = rng.Value
  ReDim a(1 To Rows.Count)
  For c = 1 To UBound(Data, 2)
    For r = 1 To UBound(Data, 1)
      If Len(Data(r, c)) > 0 Then
        k = k + 1
        a(k) = Data(r, c)
      End If
    Next r
  Next c
  ReDim Preserve a(1 To k)
  STACKCOLS = Application.Transpose(a)
End Function

Demo here on a smaller data set.

MossyPants.xlsm

A

B

C

D

1

2

3

a

d

j

4

b

e

k

5

c

f

l

6

d

g

m

7

e

h

n

8

f

i

9

g

j

10

h

11

i

12

j

13

j

14

k

15

l

16

m

17

n

18

Stack Columns (4)

Cell Formulas
Range	Formula
A3:A17	A3	=STACKCOLS(B3:D50)
Dynamic array formulas.

Vertically stack columns

MossyPants

New Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

MossyPants

New Member

Peter_SSs

MrExcel MVP, Moderator

MossyPants

New Member

Peter_SSs

MrExcel MVP, Moderator

Peter_SSs

MrExcel MVP, Moderator

Kerryx

Well-known Member

MossyPants

New Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Vertically stack columns

New Member

Excel Facts

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

MrExcel MVP, Moderator

Well-known Member

New Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock