Vertically stack columns

[MossyPants]

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

 

[MossyPants]

You could try this user-defined function then.

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

This is perfect!!!
Is there a way to use non-contiguous columns, instead of a range? E.g. STACKCOLS(B:B, D:D, G:G)...

 

[Peter_SSs]

Is there a way to use non-contiguous columns, instead of a range? E.g. STACKCOLS(B:B, D:D, G:G)...

Try this. (It will return an error if there are more than 65,536 items in the stack)

Function COLSTACK(ParamArray rngs() As Variant) As Variant
  Dim Data As Variant, a As Variant, rng As Variant
  Dim col As Range
  Dim r As Long, k As Long, lr As Long
  
  ReDim a(1 To Rows.Count)
  For Each rng In rngs
    For Each col In rng.Columns
      lr = 0
      On Error Resume Next
      lr = col.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
      On Error GoTo 0
      If lr >= col.Row Then
        Data = col.Resize(lr).Value
        For r = 1 To UBound(Data)
          If Len(Data(r, 1)) > 0 Then
            k = k + 1
            a(k) = Data(r, 1)
          End If
        Next r
      End If
    Next col
  Next rng
  If k < 65537 Then
    ReDim Preserve a(1 To k)
    COLSTACK = Application.Transpose(a)
  Else
    COLSTACK = CVErr(xlErrRef)
  End If
End Function

MossyPants.xlsm
	A	B	C	D	E	F	G
1
2
3	a	a		d		j
4	b	b		e		k
5	c	c		f		l	n
6	d			g		m
7	e			h
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 (6)

Cell Formulas
Range	Formula
A3:A17	A3	=COLSTACK(B:B,D3:D15,F:G)
Dynamic array formulas.

 

[MossyPants]

Perfection! Much appreciated!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.