Combine text from numerous cells into one, skipping blanks

[AFST]

I want to combined text from cells EJ17:EJ2516 into a single cell, but have it skip blanks.

All of the cells will contain either blank or a single word in them.

Is that possible?

 

[Domenic]

Try the following custom function...

1) Copy the following code in a regular module (Alt+F11 > Insert > Module > Copy/Paste code > Alt+Q)...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit
[/COLOR]
[COLOR=darkblue]Function[/COLOR] AConcat(a [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], [COLOR=darkblue]Optional[/COLOR] Sep [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR] = "") [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
[COLOR=green]' Harlan Grove, Mar 2002
[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Y [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]TypeOf[/COLOR] a [COLOR=darkblue]Is[/COLOR] Range [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] Y [COLOR=darkblue]In[/COLOR] a.Cells
            AConcat = AConcat & Y.Value & Sep
        [COLOR=darkblue]Next[/COLOR] Y
    [COLOR=darkblue]ElseIf[/COLOR] IsArray(a) [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] Y [COLOR=darkblue]In[/COLOR] a
            AConcat = AConcat & Y & Sep
        [COLOR=darkblue]Next[/COLOR] Y
    [COLOR=darkblue]Else[/COLOR]
        AConcat = AConcat & a & Sep
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    AConcat = Left(AConcat, Len(AConcat) - Len(Sep))
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]

2) Then try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUBSTITUTE(AConcat(IF(LEN(EJ17:EJ2516)>0,"[COLOR=#ff0000],[/COLOR]"&EJ17:EJ2516,"")),"[COLOR=#ff0000],[/COLOR]","",1)

Note that the formula uses a comma (in red) as a delimiter. Change the delimiter as desired.

Hope this helps!

 

[AFST]

Ya! That worked. Is there any way to make it skip duplicates?

 

[Domenic]

Try...

=SUBSTITUTE(AConcat(IF(LEN(EJ17:EJ2516)>0,IF(MATCH("~"&EJ17:EJ2516,EJ17:EJ2516&"",0)=ROW(EJ17:EJ2516)-ROW(EJ17)+1,","&EJ17:EJ2516,""),"")),",","",1)

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[AFST]

Domenic, that formula works perfectly. One last request. Is it possible to use you're formula to look at 4 seperate columns (EA,ED,EG,EJ) and keep all of the same criteria (skip blanks and duplicates).

Again the columns are all one word values.

Or

I've tried using your current formula 4 different times (one for each column) and then using it a 5th time to evaluate the 4 summaries, except that I will then potentially get duplicates in the result. For example, say "Blue, Red" is the result of one column summary, and "Blue" is the result of a different column summary. The overall summary now reads "Blue, Red, Blue" but I still just need Blue, Red.

That make sense?

 

[Rick Rothstein]

Domenic, that formula works perfectly. One last request. Is it possible to use you're formula to look at 4 seperate columns (EA,ED,EG,EJ) and keep all of the same criteria (skip blanks and duplicates).

You should really ask your actual question initially instead of making the volunteer who is trying to help you waste their time answering several more simplified intermediate lead-up questions one-at-a-time like you did in this thread... it is not really fair to the volunteer who would rather be helping not only you, but as many others as possible within the limited time available to them for their volunteering efforts.

Again the columns are all one word values.

Assuming the delimiter is a comma followed by a space, give the following UDF a try instead. Note that it is written specifically for the range you told us your data was in Columns EA, ED, EG and EJ between Rows 17 and 2516 and, hence, is called differently than the more generalize code Domenic posted for you earlier. My UDF is simply called like this...

=Combined()

Function Combined() As String
  Dim R As Long, C As Long, Data As Variant
  Application.Volatile
  Data = Range("EA17:EJ2516")
  For C = 1 To UBound(Data, 2) Step 3
    For R = 1 To UBound(Data, 1)
      If InStr(1, " " & Combined & " ", " " & Data(R, C) & " ", vbTextCompare) = 0 Then
         Combined = Combined & " " & Data(R, C)
      End If
    Next
  Next
  Combined = Replace(Mid(Combined, 2), " ", ", ")
End Function

 

[Domenic]

Try the following custom function, which accepts three arguments. The first argument is a range, the second argument is a separator, and the third argument specifies whether to exclude blanks. Note that the first argument accepts a multi-range, and that the second and third arguments are optional. The default for the second argument is a comma (,), and the default for the third argument is TRUE.

Custom Function

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit
[/COLOR]
[COLOR=darkblue]Function[/COLOR] CONCATUNIQUEVALUES([COLOR=darkblue]ByVal[/COLOR] rRange [COLOR=darkblue]As[/COLOR] Range, [COLOR=darkblue]Optional[/COLOR] [COLOR=darkblue]ByVal[/COLOR] sSep [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR] = ",", [COLOR=darkblue]Optional[/COLOR] [COLOR=darkblue]ByVal[/COLOR] bExcludeBlanks [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR] = [COLOR=darkblue]True[/COLOR]) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String
[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] colUniqueVals           [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] Collection
    [COLOR=darkblue]Dim[/COLOR] vItem                   [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] rArea                   [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] rCell                   [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] sTxt                    [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] rRange = Intersect(rRange.Parent.UsedRange, rRange)
    
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] rArea [COLOR=darkblue]In[/COLOR] rRange.Areas
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] rCell [COLOR=darkblue]In[/COLOR] rArea.Cells
            colUniqueVals.Add rCell.Value, [COLOR=darkblue]CStr[/COLOR](rCell.Value)
        [COLOR=darkblue]Next[/COLOR] rCell
    [COLOR=darkblue]Next[/COLOR] rArea
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
    
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] vItem [COLOR=darkblue]In[/COLOR] colUniqueVals
        [COLOR=darkblue]If[/COLOR] bExcludeBlanks [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]If[/COLOR] Len(vItem) > 0 [COLOR=darkblue]Then[/COLOR]
                sTxt = sTxt & sSep & vItem
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Else[/COLOR]
            sTxt = sTxt & sSep & vItem
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] vItem
    
    CONCATUNIQUEVALUES = Mid(sTxt, Len(sSep) + 1)
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]

Worksheet Formulas

Here are some examples of worksheet formulas that can be used...

=CONCATUNIQUEVALUES(A2:A10)

=CONCATUNIQUEVALUES(A2:A10,",",TRUE)

=CONCATUNIQUEVALUES(A2:A10," / ", FALSE)

=CONCATUNIQUEVALUES((EA:EA,ED:ED,EG:EG,EJ:EJ))

Hope this helps!

 

[Domenic]

You should really ask your actual question initially instead of making the volunteer who is trying to help you waste their time answering several more simplified intermediate lead-up questions one-at-a-time like you did in this thread... it is not really fair to the volunteer who would rather be helping not only you, but as many others as possible within the limited time available to them for their volunteering efforts.

Yes, it would be nice.

 

[GTO]

Greetings all

I was just thinking that if as a UDF, we could exceed the max char's in a cell limit. Anyways... was also thinking that dictionary would maybe be quicker (if a lot of uniques), so here's my try:

Function JOINUNIQUES(CellRange As Range, _
                     Optional Seperator As String = ", ", _
                     Optional IncludeBlank As Boolean = False) As String
  
Dim colArea As Range, Cell, Keys
  
  If Not CellRange.Cells.Count > 1 Then
    JOINUNIQUES = "Error, need range of cells"
    Exit Function
  End If
  
  With CreateObject("Scripting.Dictionary")
    
    For Each colArea In CellRange.Areas
      For Each Cell In colArea.Value
        .Item(CStr(Cell)) = Empty
      Next
    Next
    
    If (.Exists(vbNullString) And (Not IncludeBlank)) Then .Remove vbNullString
    
    JOINUNIQUES = Join(.Keys, Seperator)
    
  End With
  
  JOINUNIQUES = IIf(Len(JOINUNIQUES) > 32767, _
                    Left$(JOINUNIQUES, 32767 - Len(Seperator & "...")) & Seperator & "...", _
                    JOINUNIQUES _
                    )
  
End Function

...excepting use of dictionary, much like Domenic's, so called like:

=JOINUNIQUES(EJ17:EM2516,", ",FALSE)

Hope that helps,

Mark

 

[AFST]

Thanks folks, the answers work great. I do have one last question.... I'm sorry, and I hope its a relatively easy fix (Sorry Rick Rothstein, I'm building the workbook as we go and wonder if there's new possibilities, otherwise I would ask at the beginning).

Is it possible to add a criteria to either the code or the formula, so that it combines the text only when whatever value/text is in 'Data Input'!C15 appears in a cell K17:K2516.

So for example 'Data Input'!C15 = "blue" and blue appears is K20 and K42. The corresponding texts in EA20,ED20,EG20,EJ20 and EA42,ED42,EG42,EJ42 get combined, skipping blanks and duplicates. Thanks in advance if you take on the request.