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.

 

[taurean]

Here's one more that works with your data. I did a lot more acrobatics as I kept struggling with COLUMNS.

Name the Range to check as Rng i.e. $A$1:$G$4

And it is ARRAY formula.
=INDEX(Rng,SMALL(IF(Rng<>"",ROW(Rng),9E+307),ROWS($A$1:$A1)),SMALL(IF(Rng<>"",COLUMN(Rng)+(COLUMNS(Rng)*(ROW(Rng)-1)),9E+307),ROWS($A$1:$A1))-((SMALL(IF(Rng<>"",ROW(Rng),9E+307),ROWS($A$1:$A1))-1)*COLUMNS(Rng)))

 

[excelbrainimplode]

Here's one more that works with your data. I did a lot more acrobatics as I kept struggling with COLUMNS.

Name the Range to check as Rng i.e. $A$1:$G$4

And it is ARRAY formula.
=INDEX(Rng,SMALL(IF(Rng<>"",ROW(Rng),9E+307),ROWS($A$1:$A1)),SMALL(IF(Rng<>"",COLUMN(Rng)+(COLUMNS(Rng)*(ROW(Rng)-1)),9E+307),ROWS($A$1:$A1))-((SMALL(IF(Rng<>"",ROW(Rng),9E+307),ROWS($A$1:$A1))-1)*COLUMNS(Rng)))

Thanks Shrivallabha, I'm going to enjoy working through all these solutions over the next few days - I have no doubt there are volumes of "magic" that i'll be taking forward from these techniques. My humble and sincerest thanks to everyone for all your time and efforts, I REALLY appreciate it. Thank you.

 

[taurean]

I hope you enjoy "magic" in these formulas. Keep coming here and you'll discover that there's a lot more more magic to learn.

 

[brofm]

Marcelo,

This is a great and useful formula. is there a way to make this formula only copy unique values and ignore duplicates.

Thanks!!

 

[Marcelo Branco]

Marcelo,

This is a great and useful formula. is there a way to make this formula only copy unique values and ignore duplicates.

Thanks!!

Could you show us a data sample and expected result?

M.

 

[brofm]

Could you show us a data sample and expected result?

M.

			currently	sheet1
A	B	C	D	E	F	G	H	I
APPLES		PERARS		PEARS	ORANGES	MANGOS	GRAPES	GRAPES
Ignore B1 and D1 as these are blanks. Also ignore I1 as this is a duplicate
Result sheet2
APPLES
PEARS
ORNAGES
MANGOS
GRAPES

<colgroup><col width="118" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4315;"><colgroup><col width="64" style="width: 48pt;" span="9"><tbody>
</tbody>

Thanks in advance for your help

God Bless!

 

[Marcelo Branco]

Maybe something like this

Sheet2

	A​
1​	MyList​
2​	APPLES​
3​	PEARS​
4​	ORANGES​
5​	MANGOS​
6​	GRAPES​

Array formula in A2 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($A$1:$I$1)-COLUMN($A$1)+1)),1)),"")

confirmed with Ctrl+Shift+Enter

Hope this helps

M.

 

[brofm]

Maybe something like this

Sheet2

	A​
1​	MyList​
2​	APPLES​
3​	PEARS​
4​	ORANGES​
5​	MANGOS​
6​	GRAPES​

<tbody>
</tbody>

Array formula in A2 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($A$1:$I$1)-COLUMN($A$1)+1)),1)),"")

confirmed with Ctrl+Shift+Enter

Hope this helps

M.

This works like a charm. However I just got hit with a curve ball by a coworker as he tested the formula. if someone inserts a row in sheet one between a1:I1 the formula doesn't work. can you please look into this?

Thank a bunch

 

[Marcelo Branco]

This works like a charm. However I just got hit with a curve ball by a coworker as he tested the formula. if someone inserts a row in sheet one between a1:I1 the formula doesn't work. can you please look into this?

Thank a bunch

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.

 

[Rick Rothstein]

This works like a charm. However I just got hit with a curve ball by a coworker as he tested the formula. if someone inserts a row in sheet one between a1:I1 the formula doesn't work. can you please look into this?

Give this formula a try...

=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(INDEX(1:1,1):INDEX(1:1,9))-COLUMN($A$1)+1)),1)),"")

Note that the 9 that I highlighted in red is the column number for Column I (the last column with data in it at the time the formula is entered)... change it as needed.