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.
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,698
Office Version
  1. 2010
Platform
  1. Windows
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.
 

excelbrainimplode

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

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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,409
A possible formula solution

Assuming your data in Sheet1 A1:G4 adjust to suit

Sheet2

A
a
b
c
d
e
f
g

<colgroup><col style="width: 48pt;" width="64"> <tbody>
</tbody>


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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,409

ADVERTISEMENT

Forgot to say

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

M.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,698
Office Version
  1. 2010
Platform
  1. Windows
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! (y)
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,409

ADVERTISEMENT

10^4 eh? I like it... very clever use of R1C1 notation! (y)
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! :LOL:

Very kind of you.

M,
 

excelbrainimplode

New Member
Joined
Sep 15, 2012
Messages
6
A possible formula solution

Assuming your data in Sheet1 A1:G4 adjust to suit

Sheet2

A
a
b
c
d
e
f
g

<TBODY>
</TBODY>


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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,698
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,409
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,011
Messages
5,599,331
Members
414,305
Latest member
scarletX

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
Top