• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

AmitSingh

Member
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.
 

Attachments

Stevie

Active Member
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.
Code:
=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'!
 
Last edited:

Stevie

Active Member
Hiya,
no sweat, try this:
Code:
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'!
 
Top