Combine Multiple Values From Multiple Columns Into One Long Column Of Values?

Support
#1

Is there a way to combine several columns into one column as illustrated below in column E?

In other words, lets say I am using the Dump function in columns A, B, and C - so the number of values in each column will vary. And I would like to have them all combined in column E. Is there some way, perhaps by using the INDEX (and perhaps COUNT) and DUMP functions to pull something like this off? Any ideas?

#2

I forgot to add... I realized I can grab all the values from column A with a formula such as:

=Dump(INDEX(A:A,COUNT(A:A)))

But if I try to daisy chain the formulas together like this:

=Dump(INDEX(A:A,COUNT(A:A)))&Dump(INDEX(B:B,COUNT(B:B)))

Then the second dump from column B just overwrites the first dump from column A. There may be a better way to do this, but I can't figure out how to get the second dump from column B to occur right after the first dump from column A left off, and so forth.

#3

I think the easiest way todo that is through VBA.

#4

You can use this formula, although its a bit messy with auto-updating if you constantly change the source values:

=OFFSET($A$1;MOD(ROW()-1;10);INT((ROW()-1)/10))

*10 is because you have 10 rows of values etc.

1 Like
#5

@tomhouy, this should do the trick...

Here's a UDF I've written that pulls data from multiple columns and displays the results into a single column [thanks to SeoTools' Dump()!]. Call the function like this:

    =Dump( RangeToColumn(
	 sourceRange [range   : Required],
	 onlyUniques [boolean : Optional, default=False],
	 skipBlanks  [boolean : Optional, default=True]
     ) )
  • When onlyUniques = True, duplicate cell values will be omitted
  • When skipBlanks = True, blank cells will be omitted

Error cells are automatically omitted and cell values are trimmed of extra spaces. To preserve the cells' original values, simply replace all instances of Trim(entry.Value) with entry.Value in the code below.

Public Function RangeToColumn(ByRef sourceRange As Range, _
    Optional ByVal onlyUniques As Boolean = False, _
    Optional ByVal skipBlanks As Boolean = True) As Variant

    Dim valuesArray() As Variant, entry As Variant
    Dim col As Range
    Dim k As Long, colNum As Long, rowNum As Long
    Dim dict As New Scripting.Dictionary

  ' TextCompareâ€șCase-Insensitive | BinaryCompareâ€șExact:
    dict.CompareMode = TextCompare
    Application.ScreenUpdating = False

    k = 0
    colNum = 0
    rowNum = 0
    On Error Resume Next

    For Each col In sourceRange.Columns
        colNum = colNum + 1
        For Each entry In col.Cells
            If Not IsError(entry) Then
                rowNum = rowNum + 1
                If (Trim(entry.Value) <> "" And entry.Value <> "n/a") _
                    Or Not skipBlanks Then
                    k = k + 1
                    ReDim Preserve valuesArray(1 To k)
                    valuesArray(k) = Trim(entry.Value)
                    dict(Trim(entry.Value)) = 1 'Save uniques to dictionary
                End If
            End If
        Next entry
        rowNum = 0
    Next col

    If onlyUniques Then
        k = 1
        For Each entry In dict.keys
            ReDim Preserve valuesArray(1 To k)
            valuesArray(k) = entry
            k = k + 1
        Next entry
    End If

    RangeToColumn = Application.Transpose(valuesArray)
    Application.ScreenUpdating = True

End Function

I hope this helps!

2 Likes
#6

Thank you! I will give this a try.

#7

Late to the party, but this sounds like a perfect use for the "concatenate" function.

Sadly, the original image isn't loading, so I'm only guessing at the intended outcome.

There are many ways to achieve the same goal, though, and it might be a better result if you could share what it is you're trying to do. Sometimes a slight design change opens up a lot of other options that work better.

:wink: