Concatenate a string of text from one col., from corresponding text in another col. that matches a cell

[Lisa116]

The goal is to have a concatenated list separated by commas, based on a different list that matches a certain cell. All of which are text.

sheet 1 contains two lists; (I need to keep these lists whole)
first list has individual items (which will be concatenated)
second list which is different and can contain multiple of the same items (this list will match the cell on sheet 2).

sheet 2 will contain the concatenated list in one cell and the match reference in another cell.

I tried using index match but I only get the first matched cell and not the rest of the list.

Thank you,
Lisa

 

[DanteAmor]

You could help us with some examples of what you have in each sheet (sheet1 and sheet2) and in a third image what you expect of result.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

Or upload an excel image:
https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

 

[Lisa116]

Thank you. I hope this is a good visual:

Sheet 1:
Column A Column B
Plan A Plan 1
Plan A Plan 2
Plan B Plan 3
Plan A Plan 4

Sheet 2:
Column A Column B
Plan A Plan 1, Plan 2, Plan 4
Plan B Plan 3

 

[Steve_]

Thank you. I hope this is a good visual:

Sheet 1:
Column A Column B
Plan A Plan 1
Plan A Plan 2
Plan B Plan 3
Plan A Plan 4

Sheet 2:
Column A Column B
Plan A Plan 1, Plan 2, Plan 4
Plan B Plan 3

The provided code is assuming both your sheets have header rows.

Sub test()
    For i = 2 To Sheets("Sheet1").UsedRange.Rows.Count
        curData = Sheets("Sheet1").Cells(i, 1)
        If InStr(1, isDone, curData) = 0 Then
            For t = i To Sheets("Sheet1").UsedRange.Rows.Count
                If Sheets("Sheet1").Cells(t, 1) = curData Then
                    curOutput = curOutput & Sheets("Sheet1").Cells(t, 2) & ", "
                End If
            Next t
            curOutput = Mid(curOutput, 1, Len(curOutput) - 2)
            Sheets("Sheet2").Cells(Sheets("Sheet2").UsedRange.Rows.Count + 1, 1) = curData
            Sheets("Sheet2").Cells(Sheets("Sheet2").UsedRange.Rows.Count, 2) = curOutput
            isDone = isDone & curData & "|"
            curOutput = Empty
        End If
    Next i
End Sub

 

[Rick Rothstein]

Here is another macro for you to consider...

Sub Lisa116()
  Dim X As Long, Data As Variant
  Data = Sheets("Sheet1").Range("A1:B" & Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row)
  With CreateObject("Scripting.Dictionary")
    For X = 1 To UBound(Data)
      If Len(Data(X, 1)) Then
        .Item(Data(X, 1)) = .Item(Data(X, 1)) & ", " & Data(X, 2)
        If Left(.Item(Data(X, 1)), 1) = "," Then .Item(Data(X, 1)) = Mid(.Item(Data(X, 1)), 3)
      End If
    Next
    Sheets("Sheet2").Range("A1").Resize(.Count) = Application.Transpose(.Keys)
    Sheets("Sheet2").Range("B1").Resize(.Count) = Application.Transpose(.Items)
  End With
End Sub

 

[DanteAmor]

With just one formula, you could not concatenate several results. You would have to concatenate several formulas or put a formula in each column.
The other option is with a macro.
Try this macro.

Change Sheet1 and Sheet2 by the name of your sheets.

Sub [COLOR=#0000ff]Concatenate_string[/COLOR]()
  Dim c As Range, f As Range
  For Each c In Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp))
    Set f = Sheets("sheet2").Range("A:A").Find(c, , xlValues, xlWhole)
    If Not f Is Nothing Then
      f.Offset(, 1).Value = f.Offset(, 1).Value & ", " & c.Offset(, 1).Value
    Else
      Sheets("sheet2").Range("A" & Rows.Count).End(xlUp)(2).Resize(1, 2).Value = c.Resize(1, 2).Value
    End If
  Next
End Sub

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 (Concatenate_string) 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.

 

[Peter_SSs]

Is this any use? I have only used one sheet for simplicity here but columns D:E could be put on your second sheet.
This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel 2016
	A	B	C	D	E
1
2	Plan A	Plan 1		Plan A	Plan 1, Plan 2, Plan 4
3	Plan A	Plan 2		Plan B	Plan 3
4	Plan B	Plan 3
5	Plan A	Plan 4
Lists

Cell Formulas
Range	Formula
E2	{=TEXTJOIN(", ",TRUE,IF(A$2:A$5=D2,B$2:B$5,""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[DanteAmor]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]{=TEXTJOIN(", ",TRUE,IF(A$2:A$5=D2,B$2:B$5,""))}[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks Peter_SSs, for opening my eyes, in my 2007 version of Excel I don't have that formula, that's why I thought it was impossible with a single formula.

 

[Peter_SSs]

Thanks Peter_SSs, for opening my eyes, in my 2007 version of Excel I don't have that formula, ...

Cheers.
12 years on, nearly time to upgrade?

 

[Rick Rothstein]

Cheers.
12 years on, nearly time to upgrade?

Is TEXTJOIN available in all of the newer versions of Excel? I thought it was only available in the Excel 365 subscription.