How to combine data from two rows, when certain criteria is met

Question

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. Here is a picture of what I want it to look like

Any ideas? I was thinking an IF function, but I'm not too familiar with Excel to get it to work.

It was just made in the Microsoft Query tool in Excel.

Answer 1

This is a rather quick and messy macro that leaves lots still to do, but you will get the idea. It also presumes your original sheet("Sheet1") is sorted by LastRxNo. If you had the ability to add and call a function in the SQLServer it would be a simpler, less error prone task. hope this helps get you started. it outputs to a sheet called "summary"

Sub conCatTimes()
Dim rx As String
Dim admTimes As String
Dim tmp As String
Dim i As Long
Dim LastRow As Long
LastRow = Sheets("Sheet1").Range("C" & ActiveSheet.Rows.Count).End(xlUp).Row
rx = ""
i = 1
For Each cell In Sheets("Sheet1").Range("C2:C" & LastRow)
    If rx <> cell Then
        rx = cell
        i = i + 1
        tmp = ""
    End If
tmp = cell.Offset(0, 11)
admTimes = admTimes + tmp + ", "
    If cell.Offset(1, 0) <> rx Then
        Sheets("summary").Range("A" & i) = rx
        Sheets("summary").Range("B" & i) = Left(admTimes, Len(admTimes) - 2)
        admTimes = ""
    End If
Next

End Sub