Array formula - non-blank cell extract from table into a single column list

[excelbrainimplode]

First of all, I'm very sorry and thank you in advance for any help - this is probably easy and I can usually work my way through these sorts of things from looking at other excellent examples posted here. Despite searching long and hard and perhaps because I'm now out of time, etc, I'm at "headless chicken" stage and descending into INDEX/MATCH blindness. Onto the challenge:

What I have is a large table which contains either blank cells or cells which contain unique strings of text. All(!) I need to do is to extract all the text cells into a vertical (single column) list - the order doesn't matter at all.

E.g.:

- A B C D E F G
1 - a - b - c -
2 - - d - - - e
3 - - - - - - -
4 f - - - g - -

What I need - on a separate sheet (order of text doesn't matter) is:

- A B C D E F G
1 a - - - - - -
2 b - - - - - -
3 c - - - - - -
4 d - - - - - -
5 e - - - - - -
6 f - - - - - -
7 g - - - - - -


Formula preferred due to macro restrictions at work, although honestly at this stage ANYTHING would help.
Thank you so much.

 

[brofm]

I think you mean: if someone inserts a column in Sheet1 between A1:I1

If so, try this new version

A2 and copied down
=IFERROR(INDEX(Sheet1!$A$1:$I$1,SMALL(IF(Sheet1!$A$1:$I$1<>"",IF(ISNA(MATCH(Sheet1!$A$1:$I$1,A$1:A1,0)),COLUMN(Sheet1!$A$1:$I$1)-COLUMN(Sheet1!$A$1)+1)),1)),"")

Ctrl+Shift+Enter

M.

Correct!! I meant column not row. This is exactly what I was looking for. YOU ROCK!! Thanks so much for the help.

 

[brofm]

Rick,

I also tried your formula and worked perfectly. I thank you for taking the time to look into this.

God Bless

 

[sstef]

thank you for the good job

 

[sstef]

I have the same problem with excelbrainimplode to the first post.
the only deference is that i have a value for example to the cell A10 of sheet1 that contains a value that i dont want to transfer at sheet2. e.g. cell A10 has the value a then the sheet2 must be
b
c
d
e
f
g

it is without "a"
i try to transform Marcelo Branco's code but it doesnot work.
i make it like this
= IFERROR(INDIRECT("Sheet1!"&TEXT(SMALL(IF(AND(Sheet1!$A$1:$G$4<>"", Sheet1!$A$1:$G$4<>Sheet1!$A$10),ROW(Sheet1!$A$1:$G$4)*10^4+COLUMN(Sheet1!$A$1:$G$4)),ROWS($A$1:A1)),"R0000C0000"),0),"")
if it is not blank cell and if it is not equal to A10
why it does not work
how i have to make it to work

 

[Marcelo Branco]

Try

=IFERROR(INDIRECT("Sheet1!"&TEXT(SMALL(IF(Sheet1!$A$1:$G$4<>"",IF(Sheet1!$A$1:$G$4<>Sheet1!$A$10,ROW(Sheet1!$A$1:$G$4)*10^4+COLUMN(Sheet1!$A$1:$G$4))),ROWS($A$1:A1)),"R0000C0000"),0),"")

Ctrl+Shift+Enter

M.

 

[sstef]

Try

=IFERROR(INDIRECT("Sheet1!"&TEXT(SMALL(IF(Sheet1!$A$1:$G$4<>"",IF(Sheet1!$A$1:$G$4<>Sheet1!$A$10,ROW(Sheet1!$A$1:$G$4)*10^4+COLUMN(Sheet1!$A$1:$G$4))),ROWS($A$1:A1)),"R0000C0000"),0),"")

Ctrl+Shift+Enter

M.

thank you Marcelo it works
can you explain to me why with the AND function it does not work

 

[Marcelo Branco]

thank you Marcelo it works
can you explain to me why with the AND function it does not work

In array formulas the AND condition works differently. It returns TRUE (to all rows) only when all rows met the two conditions. And returns FALSE (to all rows) if a single row doesn't satisfy both conditions.

To create AND condition in an array formula you either
1. multiply the conditions
IF((condition1)*(condition2), DoSomethingIfTrue, DoOtherthingIfFalse)

Or
2. use nested IFs
IF(condition1,IF(condition2...

M.