Grouping of cell value from one cell to another cell by matching criteria

[AmitSingh]

Hi, need help to grouping the cell values on one cell to another cell by matching the criteria. For example, if XYZ value is given in continues 3 rows then their value should be grouped as 1,2,3 in another column of same row where the XYZ is there. I have attached the screenshot with name Result Image and a file name Workbook1.xlsm.
Any help is appreciated as this little urgent. Thanks in advance.

 

[Stevie]

Hi Amit,
do you need a VBA solution?
2 minutes of googling found me a formula solution which does what you want:

Assuming your skills are in column A and your notes are in column B then paste this in column C and click on the formula bar and do Ctrl + Shift + Enter to enter it as an array formula.

=TEXTJOIN(", ",TRUE,IF($A$2:$A$19=A2,$B$2:$B$19,""))

You will need to change the size of the range to match your dataset.

There is obviously a VBA solution, and I can post one, but you said it was urgent, so here is a quick response.

Link to the web page I found this solution on:
https://exceljet.net/formula/multiple-matches-in-comma-separated-list

If this helped, please click 'Like'!

 

[AmitSingh]

Thank you for quick response and yes...I need VBA solution.

 

[Stevie]

Hiya,
no sweat, try this:

Sub results()
    Dim pskill As String
    Dim notes As String
    Dim result As String
    Dim row As Double
    Dim orow As Double
    Dim rowcount As Double
    Dim first As Boolean
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    rowcount = ws.Cells(ws.Rows.Count, "A").End(xlUp).row
    first = False
    For orow = 2 To rowcount
        first = True
        result = ""
        For row = 2 To rowcount
            If ws.Cells(row, 1).Text = ws.Cells(orow, 1).Text And ws.Cells(row, 2) <> "" Then
                If first Then
                    result = ws.Cells(row, 2).Text
                    first = False
                Else:
                    result = result & "," & ws.Cells(row, 2).Text
                End If
            End If
        Next
        ws.Cells(orow, 3) = result
    Next
End Sub

If this was helpful, please click 'Like'!

 

[AmitSingh]

Thanks and it work really well.....many many thanks.

 

[Stevie]

No worries. ^.^