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

excelbrainimplode

New Member
Joined
Sep 15, 2012
Messages
6
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.
 
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.
 
Upvote 0

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Rick,

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

God Bless
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top