Page 1 of 3 123 LastLast
Results 1 to 10 of 27
Like Tree6Likes

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

This is a discussion on Array formula - non-blank cell extract from table into a single column list within the Excel Questions forums, part of the Question Forums category; First of all, I'm very sorry and thank you in advance for any help - this is probably easy and ...

  1. #1
    New Member
    Join Date
    Sep 2012
    Posts
    6

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

    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.

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    16,393

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

    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? Try one of these MrExcel HTML Maker, Excel jeanie or Borders-Copy-Paste

  3. #3
    New Member
    Join Date
    Sep 2012
    Posts
    6

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

    Quote Originally Posted by Rick Rothstein View Post
    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)

  4. #4
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    8,501

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

    A possible formula solution

    Assuming your data in Sheet1 A1:G4 adjust to suit

    Sheet2

    A
    a
    b
    c
    d
    e
    f
    g


    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.

  5. #5
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    8,501

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

    Forgot to say

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

    M.

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    16,393

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

    Quote Originally Posted by Marcelo Branco View Post
    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!
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? Try one of these MrExcel HTML Maker, Excel jeanie or Borders-Copy-Paste

  7. #7
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    8,501

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

    Quote Originally Posted by Rick Rothstein View Post
    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,

  8. #8
    New Member
    Join Date
    Sep 2012
    Posts
    6

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

    Quote Originally Posted by Marcelo Branco View Post
    A possible formula solution

    Assuming your data in Sheet1 A1:G4 adjust to suit

    Sheet2

    A
    a
    b
    c
    d
    e
    f
    g


    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.

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    16,393

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

    Quote Originally Posted by Marcelo Branco View Post
    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? Try one of these MrExcel HTML Maker, Excel jeanie or Borders-Copy-Paste

  10. #10
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    8,501

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

    Quote Originally Posted by excelbrainimplode View Post
    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.

Page 1 of 3 123 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com