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

E

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.
 
Sort by date Sort by votes
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)))
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
taurean said:
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)))
Click to expand...

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.
 
Upvote 0
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.
 
Upvote 0
Marcelo,

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

Thanks!!
 
Upvote 0
Marcelo Branco said:
Could you show us a data sample and expected result?

M.
Click to expand...

[TABLE="width: 521"]
<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>[TR]
[TD="width: 118, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"]currently[/TD]
[TD="bgcolor: yellow"]sheet1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] A[/TD]
[TD="bgcolor: transparent"]B [/TD]
[TD="bgcolor: transparent"]C [/TD]
[TD="bgcolor: transparent"]D[/TD]
[TD="bgcolor: transparent"]E[/TD]
[TD="bgcolor: transparent"]F[/TD]
[TD="bgcolor: transparent"]G[/TD]
[TD="bgcolor: transparent"]H[/TD]
[TD="bgcolor: transparent"]I[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]APPLES[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]PERARS[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]PEARS[/TD]
[TD="bgcolor: transparent"]ORANGES[/TD]
[TD="bgcolor: transparent"]MANGOS[/TD]
[TD="bgcolor: transparent"]GRAPES[/TD]
[TD="bgcolor: transparent"]GRAPES[/TD]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Ignore B1 and D1 as these are blanks. Also ignore I1 as this is a duplicate[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Result sheet2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]APPLES[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]PEARS[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ORNAGES[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]MANGOS[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]GRAPES[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance for your help

God Bless!
 
Upvote 0
Maybe something like this

Sheet2


[Table="class: grid"][tr][td] [/td][td]
A​
[/td][/tr]
[tr][td]
1​
[/td][td]
MyList​
[/td][/tr]


[tr][td]
2​
[/td][td]
APPLES​
[/td][/tr]


[tr][td]
3​
[/td][td]
PEARS​
[/td][/tr]


[tr][td]
4​
[/td][td]
ORANGES​
[/td][/tr]


[tr][td]
5​
[/td][td]
MANGOS​
[/td][/tr]


[tr][td]
6​
[/td][td]
GRAPES​
[/td][/tr]
[/table]

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.
 
Upvote 0
Marcelo Branco said:
Maybe something like this

Sheet2


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]
A​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]
MyList​
[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]
APPLES​
[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]
PEARS​
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]
ORANGES​
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]
MANGOS​
[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]
GRAPES​
[/TD]
[/TR]
</tbody>[/TABLE]


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.
Click to expand...

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
 
Upvote 0
brofm said:
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
Click to expand...

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.
 
Last edited:
Upvote 0
brofm said:
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?
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Return non-blank cells in an array to a single column
Replies
3
Views
461
Peter_SSs
Peter_SSs
ExcelNewbie2020
  • Solved
extract the header of sets of non empty cell
Replies
3
Views
73
ExcelNewbie2020
ExcelNewbie2020
M
  • Solved
Unique() filter to exclude blanks and multiple strings
Replies
2
Views
390
metcala
M
M
  • Solved
Matching two columns cell for cell
Replies
2
Views
142
jolivanes
jolivanes
C
  • Solved
Counting commas between multiple specific strings of text in a single cell
2
Replies
10
Views
376
Country_Calc_2
C

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

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