Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

enter image description here
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

enter image description here
click to enlarge

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

enter image description here

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

share|improve this question
can you explain 28409 and down, why they don't consolidate? the 7A-7P already is the same? – datatoo Jul 3 '12 at 4:24
I should have actually removed the 7A-7P AdminTimes. Everything is being pulled from a SQL table and the (Certain Time through another Time) are medications that are dosed every shift vs twice a day or three times a day. It doesn't matter if they get consolidated. I just need every LastRxNo that are the same to consolidate the AdminTimes into a single row. – Corde Parker Jul 3 '12 at 17:19
since it is pulled from a SQL table could you create a query there to do all of this consolidation? or do you need to clear these values in excel first, and then do it? – datatoo Jul 3 '12 at 17:28
It could be done either way I guess. I have limited knowledge in SQL. I can post the query though. – Corde Parker Jul 3 '12 at 17:32
You can create a function in SQL that will do all of this for you automatically, which may be much easier than a vba solution in excel. if you want to try that approach I can suggest links… and an access example Once you have a function such as this, the query is not too bad – datatoo Jul 3 '12 at 19:09

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

End Sub
share|improve this answer
Thanks datatoo for the response. Yeah, it seems like a pretty messy macro. I would have hoped there was an easier way because I seem to be coming across things on multiple lines that I want to make into a single line on another type of report I'm trying to run. Does anybody else have any suggestions/solutions? – Corde Parker Jul 6 '12 at 17:14
"things on multiple lines that I want to make into a single line", like what? This only showed copying LastRxNumber but you could easily include everything else. If everything is the same except the adminTimes, this could get you there. But the ultimate easiest way is to create a function on the server itself that exports it the way you need – datatoo Jul 6 '12 at 17:20

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?)

share|improve this answer

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()

    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

    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

            '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

        'Finally, store the combined string in the abbreviated table
        tbl2.ListColumns("AdminTime").Range(rcd2.Range.Row, 1).Value = s

End Sub
share|improve this answer

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:

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.

share|improve this answer

enter image description here

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.