Creating an Unusual List

[j_brubaker]

My problem is the following:

I am looking to create a list in 'column A' by referencing a range of cells from the range B2:E20 with some blank cells and some not. In the list I want to include only the non-blank cells while also updating automatically if the cells in 'columns B:E' are changed.

This may only take a simple function that I am unaware of.

Thanks in advance for any help,
Josh

 

[sandy666]

try Power Query aka Get&Transform

or post example data

 

[Peter_SSs]

What sort of values might occur in B2:E20? (text?, numerical?, dates? errors? mix of text & numbers? etc)

 

[mohadin]

What about

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("b2:e20")) Is Nothing Then Exit Sub
    [a:a].ClearContents
    x1 = Range("b" & Rows.Count).End(xlUp).Row
    x1 = Filter(Evaluate("transpose(if(countif(offset(b2:b" & x1 & _
                         ",,,row(1:" & x1 & ")),b2:b" & x1 & ")=1,b2:b" & x1 & "))"), False, 0)
    x2 = Application.Max(Range("c" & Rows.Count).End(xlUp).Row, 3)
    x2 = Filter(Evaluate("transpose(if(countif(offset(c2:c" & x2 & _
                         ",,,row(1:" & x2 & ")),c2:c" & x2 & ")=1,c2:c" & x2 & "))"), False, 0)
    x3 = Application.Max(Range("d" & Rows.Count).End(xlUp).Row, 4)
    x3 = Filter(Evaluate("transpose(if(countif(offset(d2:d" & x3 & _
                         ",,,row(1:" & x3 & ")),d2:d" & x3 & ")=1,d2:d" & x3 & "))"), False, 0)
    x4 = Application.Max(Range("e" & Rows.Count).End(xlUp).Row, 5)
    x4 = Filter(Evaluate("transpose(if(countif(offset(e2:e" & x4 & _
                         ",,,row(1:" & x4 & ")),e2:e" & x4 & ")=1,e2:e" & x4 & "))"), False, 0)
    a = Split(Join(x1, Chr(164)) & Chr(164) & Join(x2, Chr(164)) & Chr(164) & Join(x3, Chr(164)) _
            & Chr(164) & Join(x4, Chr(164)), Chr(164))
    Cells(2, 1).Resize(UBound(a)) = Application.Transpose(a)
End Sub

 

[AhoyNC]

A possible formula solution. This is not my formula, but comes from post#4 in the link below.
https://www.mrexcel.com/forum/excel...ll-extract-table-into-single-column-list.html

This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed.

Excel Workbook
	A	B	C	D	E
1	t1
2	bh	t1
3	you		bh		you
4	po
5	34		po	34
6
7
8
9
Sheet