Combine 3 non-contiguous columns into one removing blanks - array help?

[Emerlin]

Hi,

I have data in columns B2:B9, D2:D9 and F2:F9. Some of those cells have blanks.

I would like to combine them into one column in starting in J2 and ignore the blanks.

I know this is an array formula, and can get one column to work with this:
=IFERROR(INDEX($B$2:$B$9,SMALL(IF($B$2:$B$9<>"",ROW($B$2:$B$9)-ROW(B$2)+1),ROWS(J$2:J2))),"")

How can I add the other columns to the list...? I have tried multiple things with no success.

Thanks in advance for any all help.

 

[Peter_SSs]

Thank you for your efforts - I am using stand alone, desktop version of Excel 2016.

OK, so you don't have the TEXTJOIN function I believe. You can still use an auto-updating standard worksheet formula.

Excel Workbook
	B	C	D	E	F	G	H	I	J
1
2	red				orange				red
3	blue		red						blue
4	yellow				black				yellow
5			green		white				black
6	black		yellow		blue				red
7									green
8									yellow
9									orange
10									black
11									white
12									blue
13
List data

 

[Emerlin]

@ Peter Ah... the above looks most useful. I will implement and report back.

Thanks!

 

[Rick Rothstein]

Thank you Rick this mostly works. I am trying to reverse engineer this so i can perhaps use it elsewhere -

Evaluate("ROW(2:" & LastRow & ")"), Split("2 4 6"))

Is that the line that specifies which row to start in? and 2,4,6 are the columns to start?

My problem is that there is other data below Row 10 in columns B,D,and F that this is picking up as well. Any way to have it look at only range B2:B10?

Here is my code revised so that you can modify the values highlighted in red for use elsewhere...

[table="width: 500"]
[tr]
	[td]Sub CombineColumnsBandDandF()
  Dim R As Long, C As Long, X As Long, StartRow As Long, LastRow As Long, ColCount As Long
  Dim Cols As String, Arr As Variant, Result As Variant
  StartRow = [B][COLOR="#FF0000"][SIZE=3]2[/SIZE][/COLOR][/B]
  LastRow = [B][COLOR="#FF0000"][SIZE=3]10[/SIZE][/COLOR][/B]
  Cols = [B][COLOR="#FF0000"][SIZE=3]"2 4 6"[/SIZE][/COLOR][/B] [B][COLOR="#008000"] 'Column numbers separated by spaces[/COLOR][/B]
  ColCount = 1 + UBound(Split(Cols))
  Arr = Application.Index(Cells, Evaluate("ROW(" & StartRow & ":" & LastRow & ")"), Split(Cols))
  ReDim Result(1 To ColCount * LastRow, 1 To 1)
  For C = 1 To ColCount
    For R = 1 To UBound(Arr)
      If Len(Arr(R, C)) Then
        X = X + 1
        Result(X, 1) = Arr(R, C)
      End If
    Next
  Next
  Range("[B][COLOR="#FF0000"][SIZE=3]J2[/SIZE][/COLOR][/B]").Resize(UBound(Result)) = Result
End Sub[/td]
[/tr]
[/table]

 

[Emerlin]

@ Peter and @ Rick

Both solutions worked very well. I think both have their place in this. The VBA was new to me so enjoy getting a chance to work with it.

I am very thankful to both you. You help was invaluable. You both taught me a new things about excel today which I will use moving forward. I hope it may help others.

Best Regards

 

[Peter_SSs]

@ Peter and @ Rick

I am very thankful to both you. You help was invaluable. You both taught me a new things about excel today which I will use moving forward.

You are very welcome.


Given the sort of parameters like Rick's red values near the start of his code, this could then be done without any looping and in a single line as follows, though I'm not suggesting you do that.
I think I would stick with the formulas.

Sub CombineColumnsWithoutBlanks()
  Const StartRow As Long = 2
  Const LastRow As Long = 10
  Const Cols As String = "2 4 6"
  
  Range("J2").Resize((1 + UBound(Split(Cols))) * (LastRow - StartRow + 1)).Value = Application.Transpose(Split(Replace(Join(Filter(Split(Join( _
    Application.Transpose(Application.Index(Application.Index(Cells, Evaluate("row(" & StartRow & ":" & LastRow & ")"), Split(Cols)), _
    Evaluate("if(row(1:" & (1 + UBound(Split(Cols))) * (LastRow - StartRow + 1) & "),mod(row(1:" & (1 + UBound(Split(Cols))) * (LastRow - StartRow + 1) & _
              ")-row(1:1),rows(1:" & LastRow - StartRow + 1 & "))+1)"), _
    Evaluate("if(row(1:" & (1 + UBound(Split(Cols))) * (LastRow - StartRow + 1) & "),int((row(1:" & (1 + UBound(Split(Cols))) * (LastRow - StartRow + 1) & _
              ")-row(1:1))/rows(1:" & LastRow - StartRow + 1 & "))+1)"))), _
              "|#|"), "#"), "||", False)), "| |", "|") & String((1 + UBound(Split(Cols))) * (LastRow - StartRow + 1), "|"), "|"))
End Sub

The introduction of some variables could make this code line more efficient (and easier to read) if required.