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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Until someone figures out how to do what you want with formulas (I cannot think of such a way), here is a macro that should work...

Code: 
Sub GetValuesMoveToAnotherSheet()
  Dim Index As Long, V As Variant, ArrIn As Variant, ArrOut As Variant
  Const TableRange As String = "A1:G4"
  Const OutputSheet As String = "Sheet4"
  Const OutputStartCell As String = "A1"
  ArrIn = Range(TableRange)
  ReDim ArrOut(1 To WorksheetFunction.CountA(Range(TableRange)), 1 To 1)
  For Each V In ArrIn
    If Len(V) Then
      Index = Index + 1
      ArrOut(Index, 1) = V
    End If
  Next
  Worksheets(OutputSheet).Range(OutputStartCell).Resize(UBound(ArrOut)) = ArrOut
End Sub
Note there are three constants (Const statements) that you have to set to match your actual setup. The TableRange constant is the address range for your data (no headers); OutputSheet is the name of the worksheet you want the output to go to: and OutputStartCell is the cell address on that worksheet where the output list should start at.
 
Upvote 0
Rick Rothstein said:
Until someone figures out how to do what you want with formulas (I cannot think of such a way), here is a macro that should work...

Code: 
Sub GetValuesMoveToAnotherSheet()
  Dim Index As Long, V As Variant, ArrIn As Variant, ArrOut As Variant
  Const TableRange As String = "A1:G4"
  Const OutputSheet As String = "Sheet4"
  Const OutputStartCell As String = "A1"
  ArrIn = Range(TableRange)
  ReDim ArrOut(1 To WorksheetFunction.CountA(Range(TableRange)), 1 To 1)
  For Each V In ArrIn
    If Len(V) Then
      Index = Index + 1
      ArrOut(Index, 1) = V
    End If
  Next
  Worksheets(OutputSheet).Range(OutputStartCell).Resize(UBound(ArrOut)) = ArrOut
End Sub
Note there are three constants (Const statements) that you have to set to match your actual setup. The TableRange constant is the address range for your data (no headers); OutputSheet is the name of the worksheet you want the output to go to: and OutputStartCell is the cell address on that worksheet where the output list should start at.
Click to expand...

Thank you SO much for your quick reply Rick. That macro works an absolute treat and I'm definitely going to make good use to get me out of this weekend's darkness - thank you, you've really saved my week!

If anyone does come up with a formula method to solve this, I would still be very interested to learn (if only for my future sanity)
 
Upvote 0
A possible formula solution

Assuming your data in Sheet1 A1:G4 adjust to suit

Sheet2

A[TABLE="width: 48"]
<colgroup><col style="width: 48pt;" width="64"> <tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]a[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]b[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]c[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]d[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]e[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]f[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]g[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]


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

confirmed with Ctrl+Shift+Enter

copy down till you get a blank cell

M.
 
Upvote 0
Forgot to say

The formula above works in Excel 2007 or higher. Can be adapted to 2003.

M.
 
Upvote 0
Marcelo Branco said:
Array formula in A1
=IFERROR(INDIRECT("Sheet1!"&TEXT(SMALL(IF(Sheet1!$A$1:$G$4<>"",ROW(Sheet1!$A$1:$G$4)*10^4+COLUMN(Sheet1!$A$1:$G$4)),ROWS($A$1:A1)),"R0000C0000"),0),"")

confirmed with Ctrl+Shift+Enter

copy down till you get a blank cell
Click to expand...

10^4 eh? I like it... very clever use of R1C1 notation! :bow:
 
Upvote 0
Rick Rothstein said:
10^4 eh? I like it... very clever use of R1C1 notation! :bow:
Click to expand...
Thank you very much, Rick

But i have to say: merits for Barry Houdini. I saw some time ago, this type of formula being used by him in a thread and i loved it.

I'm only a good plagiarist.:)

Not to go against the adage - "The only modesty that exists is the false one" - okay, i accept 50% of the congratulations! :laugh:

Very kind of you.

M,
 
Upvote 0
Marcelo Branco said:
A possible formula solution

Assuming your data in Sheet1 A1:G4 adjust to suit

Sheet2

A[TABLE="width: 48"]
<TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]a
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]b
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]c
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]d
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]e
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]f
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]g
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]


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

confirmed with Ctrl+Shift+Enter

copy down till you get a blank cell

M.
Click to expand...


WOW, that is simply amazing, thank you! It works beautifully - it's going to take me days to get my head around that :-) but I'm (oddly) really looking forward to it.
Thank you Marcelo (and you Rick) I'm completely humbled and totally blown away by both of these perfect solutions.
 
Upvote 0
Marcelo Branco said:
But i have to say: merits for Barry Houdini. I saw some time ago, this type of formula being used by him in a thread and i loved it.

I'm only a good plagiarist.:)
Click to expand...

Hey, I love it too... and plan to plagiarize it in the future myself. ;)
 
Upvote 0
excelbrainimplode said:
WOW, that is simply amazing, thank you! It works beautifully - it's going to take me days to get my head around that :-) but I'm (oddly) really looking forward to it.
Thank you Marcelo (and you Rick) I'm completely humbled and totally blown away by both of these perfect solutions.
Click to expand...

You are very welcome and thanks for the feedback!

M.
 
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,248
Messages
6,171,021
Members
452,374
Latest member
keccles

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