Combining rows if data in certain colums are equal

I'm trying to make this Excel document but I want the AdminTimes for the same LastRxNo to be on the same line. So if the LastRxNo is the same, have one line and the AdminTime column will have multiple values.

excelquestion.jpg

Here is a picture of what I want it to look like

excelquestion2.jpg

Any ideas? Thanks

Anybody able to help?

Not tested

Sub test()
    Dim a, i As Long, ii As Long, n As Long, txt As String
    a = Range("a1").CurrentRegion.Value
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        n = 1
        For i = 2 To UBound(a, 1)
            If a(i, 2) = 855 Then
                txt = Join$(Array(a(i, 3), a(i, 4), a(i, 5)), Chr(2))
                If Not .exists(txt) Then
                    n = n + 1
                    For ii = 1 To UBound(a, 2)
                        a(n, ii) = a(i, ii)
                    Next
                    .Item(txt) = n
                Else
                    a(.Item(txt), UBound(a, 2)) = _
                    a(.Item(txt), UBound(a, 2)) & ", " & a(i, UBound(a, 2))
                End If
            End If
        Next
    End With
    Sheets(2).Cells(1).Resize(n, UBound(a, 2)).Value = a
End Sub

Is there anything special I need to do with the code? Just go to developer tab and click on Visual Basic and add the code there? Sorry, not too familiar.

If you go to Visual Basic Editor (VBE)

Go to [Insert] - [Module] then paste the code on to the right pane and go back to excel.

When you are in Excel, hit Alt + F8, choose "test" then click on [Run]

Okay, I did that and I get a Run-Time error '9': Subscript out of range

and I hit debug and it references this line of the code

Sheets(2).Cells(1).Resize(n, UBound(a, 2)).Value = a

How many worksheets do you have?
If you have only one worksheet, add one more.

Otherwise, I need to see your file, (dummy data (the same format) will be fine)

Parata Check.xlsx

This is the modified raw data from the whole document. I just change all the names to be my name. All the data is pulled from a SQL server and is auto refreshed upon opening the document (if that matters).

OK

What is the criteria to combine the rows?

First I filter out everything in the AdminTime column to show only values with a time (so it will filter out blanks and any words. Then if the value in the LastRxNo equals another LastRxNo the values in the AdminTime fields need to be combined. I could probably filter more out with the SQL query, but this is just all the data I'll need to look at.

How about this one.

Sub test()
    Dim a, i As Long, ii As Long, n As Long
    Dim txt As String, temp As String
    a = Sheets(1).Range("a1").CurrentRegion.Value
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        n = 1
        For i = 2 To UBound(a, 1)
            txt = a(i, 3)
            If Not .exists(txt) Then
                n = n + 1
                For ii = 1 To UBound(a, 2)
                    a(n, ii) = a(i, ii)
                Next
                .Item(txt) = n
            Else
                If Trim$(a(i, UBound(a, 2))) <> "" Then
                    temp = Trim(a(.Item(txt), UBound(a, 2)))
                    a(.Item(txt), UBound(a, 2)) = _
                    temp & IIf(temp = "", "", ", ") & _
                    Trim$(a(i, UBound(a, 2)))
                End If
            End If
        Next
    End With
    Sheets(2).Cells(1).Resize(n, UBound(a, 2)).Value = a
End Sub

I think that might have done it. I'm going to filter out all of the unwanted results in the admintime with the SQL query, then run that code. I'll let you know how it goes tomorrow. THanks