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.

 

[Rick Rothstein]

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...

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.

 

[excelbrainimplode]

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...

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.

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)

 

[Marcelo Branco]

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.

 

[Marcelo Branco]

Forgot to say

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

M.

 

[Rick Rothstein]

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

10^4 eh? I like it... very clever use of R1C1 notation!

 

[Marcelo Branco]

10^4 eh? I like it... very clever use of R1C1 notation!

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!

Very kind of you.

M,

 

[excelbrainimplode]

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.

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.

 

[Rick Rothstein]

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.

Hey, I love it too... and plan to plagiarize it in the future myself.

 

[Marcelo Branco]

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.

You are very welcome and thanks for the feedback!

M.