Create a list from an existing list in excel

Question

I have a table with non-numerical values that are being populated from an if/index formula.

For example, I have the following set of data:

Hello	This
	is
This
	my
Is	second

My	column

Table	of

With	data
	here
Spaces

The values above are being pulled from another table using a combination if/index function, therefore the blank cells still have a value in them (a formula).

I would like to create a new list from this list that keeps them all together. For example, I would like the new list to look something like this:

Hello

This

Is

My

Table

With

Spaces

This

is

my

second

column

here

Is there any way this is possible? I feel like I've been able to do this before with a macro possibly, but I can't seem to locate the work I had previously done this with.

Any help is greatly appreciated!

lorenzo · Answer

dkingsbury&nbsp;&nbsp;Assuming Excel 2021 or 365:&nbsp;&nbsp;in&nbsp;E2:=LET(
    rws,    ROWS(MyRange),
    seqK,   SEQUENCE( rws * COLUMNS(MyRange),,0 ),
    seqR,   1 + MOD(seqK, rws),
    seqC,   1 + QUOTIENT(seqK, rws),
    stack,  INDEX(MyRange, seqR, seqC),
    FILTER(stack, stack &lt;&gt; "")
)

riny_van_eekelen · Answer

dkingsbury&nbsp;You may want to look into PowerQuery to achieve this, as demonstrated in the attached file.Alternatively, as in Insider (beta channel) you can use this:&nbsp;&nbsp;=TOCOL(Table1,1,TRUE)&nbsp;Edit: Added another, even simpler PQ solution.