Tjyylli

^{click to enlarge}

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

^{click to enlarge}

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.

You could just make a pivot table from this data, although rather than being presented in rows it goes down in a column.
i.e. select all the data, Insert>Pivot table, drag PatID, LastRxNo and Admintime into "Row Labels" area of the pivot table, hide any totals or subtotals (I'd post pics but my reputation is high enough on here yet - my first post!)
Creates a pivot table that looks something like this(not sure this will look right in my post):
855
87160
6AM
2PM
10PM
(and so on...)

Alternatively, if you changed the SQL query in your select statment to return the whole name in one cell (Select ......., Patients.PatLName+', '+Patients.PatFname, ........)
you could have the pivot return the whole patient name rather than number, something like this...
Parker, Corde
87160
6AM
2PM
10PM
(and so on...)

It might not be the exact same format you originally asked for, but it seems legible to me (assuming you're using it to check off when meds have been administered?)

Create a column which adds the count to the code

=B5&" "&COUNTIF($B$2:$B5,B5)

then you can lookup each separate entry and concatenate the text the formula in d2 becomes:-

=IF($B2&" "&1=D2, INDEX(C:C,MATCH($B2&" "&1,D:D,0))&" "&IFERROR(INDEX(C:C,MATCH($B2&" "&2,D:D,0)),"")&" "&IFERROR(INDEX(C:C,MATCH($B2&" "&3,D:D,0)),"")&" "&IFERROR(INDEX(C:C,MATCH($B2&" "&4,D:D,0)),""),"")

for each possibility you will need to add a &" "&IFERROR(INDEX(C:C,MATCH($B5&" "&5,D:D,0)),"") where the &5 become &6 etc. The above formula only stretches to 4 instances.

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

I would recommend using a macro to copy the contents of the table into an identical table, filter out duplicate values - while ignoring the AdminTime column - and then loop through the original table to find the same values and combine all their AdminTime values in a single string.
In the code below, you'll obviously have to tweak it a bit for your needs, especially where I define the tables and filter by unique values. In addition, I have assumed that AdminTime is the last column in your table which might not be the case.

Option Explicit



Sub combineAdminTimes()



    'Declarations

    Dim tbl1 As ListObject  'Original table

    Dim tbl2 As ListObject  'Abbreviated table

    Dim arrColumns()        'Array of column numbers in which to search for duplicate values

    Dim rcd1 As ListRow     'Generic list row in original table

    Dim rcd2 As ListRow     'Generic list row in abbreviated table

    Dim i As Long           'Generic counter

    Dim blnMatch As Boolean 'Whether or not the two records match and should be combined

    Dim s As String         'String in which to store the combined result



    'Initialize

    Set tbl1 = Sheet1.ListObjects(1)

    Set tbl2 = Sheet2.ListObjects(1)



    'Clear the old filtered list

    If Not tbl2.DataBodyRange Is Nothing Then tbl2.DataBodyRange.Rows.Delete



    'Copy the unfiltered list

    tbl1.DataBodyRange.Copy tbl2.Range(2, 1)



    'Remove all duplicate values

    tbl2.Range.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7), Header:=xlYes



    'Loop through records in abbreviated table

    For Each rcd2 In tbl2.ListRows

        s = ""

        'Loop through records in original table

        For Each rcd1 In tbl1.ListRows

            'Check if any fields in the two records do not match

            blnMatch = True

            For i = 1 To tbl1.ListColumns.Count - 1

                If rcd1.Range(1, i).Value <> rcd2.Range(1, i).Value Then blnMatch = False

            Next



            'If all matched, then add the time value to the string

            If blnMatch Then

                If LenB(s) > 0 Then s = s & ", "    'Separate each entry by a comma

                s = s & tbl1.ListColumns("AdminTime").Range(rcd1.Range.Row, 1).Value

            End If

        Next



        'Finally, store the combined string in the abbreviated table

        tbl2.ListColumns("AdminTime").Range(rcd2.Range.Row, 1).Value = s

    Next



End Sub

I would use the Power Query Add-In for this. I think you could achieve this task without writing a line of code.

I would first use the Duplicate Column command to duplicate the AdminTimes column. Then I would use the Pivot command to convert the AdminTimes data into columns:

https://support.office.com/en-US/Article/Pivot-columns-abc9c8da-3be9-44c4-886e-0be331ab387a?ui=en-US&rs=en-US&ad=US

In the Pivot definition I would set the Values column to the "Copy of AdminTimes" column created previously, and the Aggregate Value Function to "Don't Aggregate". This will automatically group by all the other columns.

Then I would use the Merge Columns command to combine the generated new columns into one. I would use a Custom separator ", " (comma, then space), to get the presentation you are after.