Concatenate random text from a column

[public]

I have an excel column with about 100 rows, each row with an Excel formula that returns text under certain conditions. Note that, the text is different from one row to another. If conditions are not met, certain rows remain blank (so, without text).
Now, I would like in another excel cell to identify lines (rows) that contain text and join them through a comma. How do I do that?

[davesexcel]

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and click "manage attachments" to open the upload window.


To add a file to a post

[public]

I've attached a sample file. The column I referenced in the first post is the column called "return".
So, I want in the G2 cell to be a formula that concatenate, by comma, randomly generated text from the return column. Sorry for my english.

[davesexcel]

I like using Concatenate, then you can work in each individual box, could use some work.

=CONCATENATE(IF(C2>0,A2,""),IF(C2>0,",",""),IF(C2>0,B2,""),IF(D2>0,",",""),IF(D2>0,D2,""))

[public]

@davesexcel, you did not understand me. I marked the yellow cell G2, not C2.
So, in the G2 cell I would like the words generated in the "return" column to be displayed as follows: CAT II-Name 3 3, CAT V-Name 6 5, CAT IV-Name 10 5 ...
If I use concatenate in the G2 cell, the problem is I don't know how to jump over empty cells in the "return" column, because I will have to consider each row, even if it contains text, even if it does not contain.

[davesexcel]

Well here is a code you can use:

Sub Combine_It()
    Dim LstRw As Long, Rng As Range, C As Range, s As String

    LstRw = Cells(Rows.Count, "E").End(xlUp).Row
    Set Rng = Range("E2:E" & LstRw)
    For Each C In Rng.Cells

        If C <> " " Then
            s = s & C & ","
        End If
    Next C

    Range("G2") = Left(s, Len(s) - 1)
End Sub

[José Augusto]

Here a solution with a helper column

In cell F2 use the following formula and copy down (hide this column)

=IF(TRIM($E2)<>"",IF(F1<>"",F1&", ","")&TRIM($E2),IF(F1="","",F1))

In G2 use

=INDEX(F:F,COUNTA(F:F)-3,1)

See the file

[public]

Thank you very much. Now I have one step. In this sense, I made a small update to the file posted by @ José Augusto (ee the attachment below).
In this file we transferred the final calculation to column H and in the G column we added the five categories: CAT I, CAT II, CAT III, CAT IV, CAT V.
I would like the result in column H to be distributed by category. Yours can help me in this regard! I have tried a SUMPRODUCT separately but it does not work.

[public]

No one, please!

[shukla.ankur281190]

Sort you data according your category to do so select A2:F101 then press Alt->A->S->S select short by category then ok.

now in H2

=LOOKUP(2,1/($G2=$A$2:$A$101),$F$2:$F$101)

Copy down.!

[public]

Thanks for the answer but unfortunately I can not use the sort date option every time.
In addition, your formula works well only in the H2 cell, in H3 ... H6 does not work well.
So, I would need a solution without using the manual sorting of the data.

[shukla.ankur281190]

Can you mention your expected result in sample file ?

[public]

At this point in cell H2 is returned: CAT I-Name 16 5, CAT I-Name 57 3; what is correct because we have CAT I in the G2 cell.
Further, in H3 cell should only show lines from column E starting with CAT II, because CAT II appears in G3 and so on... in H4 cell = CAT III, because G4 = CAT III... How can I make H3 ... H6 cells work?

[José Augusto]

Hi
Sorry for late replay
I use a little better approach.
In a new sheet (draft in my example) use in A3, B3, C3 the following formulas:

=IFERROR(AGGREGATE(15,6,ROW(name!$E$2:$E$101)/(LEN(TRIM(name!$E$2:$E$101))<>0),ROW($B1)),"")
=IF(A3<>"",TRIM(INDEX(name!E:E,A3,1)),"")
=IF($A3<>"",IF(LEFT($B3,LEN(C$1))=C$1,IF(C2<>"",C2&", ","")&$B3,IF(C2="","",C2)),"")

The first formula gives me the line numbers where there is text in the name column!E

The second formula allows me to get that text

The third formula uses the column header at C $ 1 to separate the text into columns. This formula can be copied to the other columns with headers.

Note: In name!J2 I provide a array formula, for compatibility with prior version of Excel2010, that can replace my first formula.

=IFERROR(SMALL(ROW($E$2:$E$101)*(TRIM($E$2:$E$101)<>""),ROW(A101)-$J$1),"")

See the file for clarification

[public]

Thanks for the reply José Augusto but in your example there appears a countifs what excel 2003 does not support. In addition, I tried to implement your solution, that is to adapt it to my example and it is not working (maybe I did not understand something good, maybe!).

For this reason, I decided to use a VBA code that searches for two values on two columns and return the corresponding result for the third column (see the example in the file attached below).

I have tried to implement the VBA below in the attached excel file does not work on two search criteria !!! Please, some help.

Function Multi_LookUpConcat(ByVal SearchList As String, SearchRange As Range, ReturnRange As Range, _
                      Optional SearchListDelimiter As String = ",", _
                      Optional Delimiter As String = " ", _
                      Optional MatchWhole As Boolean = True, _
                      Optional UniqueOnly As Boolean = False, _
                      Optional MatchCase As Boolean = False)
                  
  Dim X As Long, CellVal As String, ReturnVal As String, Result As String
  
'Parse the SearchList into Strings
' Spaces next to the delimiters will be ignored
Dim SearchString As String
Dim List As String
Dim C1 As Integer
Dim C2 As Integer

  If StrComp(SearchList, "") = 0 Then
    Multi_LookUpConcat = ""

  ElseIf (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or _
     (ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then
    Multi_LookUpConcat = CVErr(xlErrRef)
  
  Else
    
    SearchList = SearchList & SearchListDelimiter   'Ensure that it runs at least once
    C1 = 1
    C2 = InStr(C1, SearchList, SearchListDelimiter)
    
    While C2 > 0
        SearchString = Trim(Mid(SearchList, C1, C2 - C1))

        If Not MatchCase Then SearchString = UCase(SearchString)
        For X = 1 To SearchRange.Count
          If MatchCase Then
            CellVal = SearchRange(X).Value
          Else
            CellVal = UCase(SearchRange(X).Value)
          End If
          ReturnVal = ReturnRange(X).Value
          If MatchWhole And CellVal = SearchString Then
            If UniqueOnly And InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) > 0 Then GoTo Continue
            Result = Result & Delimiter & ReturnVal
          ElseIf Not MatchWhole And CellVal Like "*" & SearchString & "*" Then
            If UniqueOnly And InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) > 0 Then GoTo Continue
            Result = Result & Delimiter & ReturnVal
          End If
Continue:
        Next
   
    ' Advance the pointers to search for the next element
    C1 = C2 + 1
    C2 = InStr(C1, SearchList, SearchListDelimiter)
    Wend
    
    Multi_LookUpConcat = Mid(Result, Len(Delimiter) + 1)
  
  End If
  
End Function