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.

 

[Rick Rothstein]

It would have been better if you gave some examples, that way we would know exactly how you wanted to combine the data and if you wanted a delimiter between the items. Assuming you don't want a delimiter, does this formula in cell J2 copied down do what you appear to be asking for...

=B2&D2&F2

 

[Emerlin]

My apologies -

(blank) = blank with no data

Column B /// Column D /// Column F
red /// (blank) /// orange
blue /// red//// (blank)
yellow//// (blank) /// black
(blank) /// green /// White
black /// yellow /// blue

Combined Column J
red
blue
yellow
black
red
green
hellow
orange
black
white
blue

The data is combined with spaces eliminated.

Sorry I was not more clear. I have the working formula for one column listed. I would like to add D and F. I am for sure not excel guru but I am pretty sure it is an array formula. This is working to combine column b and remove blanks starting at J2

{=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))),"")}

Thanks for any/all help.

 

[sandy666]

Are you able to use PowerQuery (Get&Transform)?

 

[Rick Rothstein]

Can you make use of a VBA macro? If so...

[table="width: 500"]
[tr]
	[td]Sub CombineColumnsBandDandF()
  Dim R As Long, C As Long, X As Long, LastRow As Long, Arr As Variant, Result As Variant
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row
  Arr = Application.Index(Cells, Evaluate("ROW(2:" & LastRow & ")"), Split("2 4 6"))
  ReDim Result(1 To 3 * LastRow, 1 To 1)
  For C = 1 To 3
    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("J2").Resize(UBound(Result)) = Result
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (CombineColumnsBandDandF) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Emerlin]

@ sandy - I am not sure what this is.

@ rick - I have never tried, but will give it a shot.

 

[Emerlin]

Can you make use of a VBA macro? If so...

[table="width: 500"]
[tr]
	[td]Sub CombineColumnsBandDandF()
  Dim R As Long, C As Long, X As Long, LastRow As Long, Arr As Variant, Result As Variant
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row
  Arr = Application.Index(Cells, Evaluate("ROW(2:" & LastRow & ")"), Split("2 4 6"))
  ReDim Result(1 To 3 * LastRow, 1 To 1)
  For C = 1 To 3
    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("J2").Resize(UBound(Result)) = Result
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (CombineColumnsBandDandF) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

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?

Thanks again for you help.

 

[sandy666]

@ sandy - I am not sure what this is.

What is PowerQuery

What is your Excel version?

 

[Emerlin]

2016 desktop - I will check out the link. Thanks

 

[sandy666]

2016 desktop

so PowerQuery = Get&Transform section under Data Tab