Combining data in excel cells
You could just make a pivot table from this data, although rather than being presented in rows it goes down in a column. Creates a pivot table that looks something like this not sure this will look right in my post: Alternatively, if you changed the SQL query in your select statment to return the whole name in one cell Select 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?
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. 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. 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:. 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.
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. Then, the question is: Next, you want to copy the formula down to all of the cells in the column. A shortcut method for doing this is to double-click the fill handle while cell C2 is selected. The fill handle is the darker square dot in the lower right corner of the cell pointer. The dot looks like this:.
Excel uses the column to the left when figuring out how far to copy cells after the double click. If you happened to have a blank cell in B8, this trick would stop at row 7. Leave it to Cher to cause a problem.
If this is the case, you might want to grab the fill handle and drag down to all of the rows in order to copy the formula. The Proper function is excellent, but it does not properly capitalize last names like McCartney See cell C7. You will have to manually go through and capitalize the C after the Mc. It would also have a problem with VanHalen. Is this a pain? Yes - but it is easier to fix a few cells than to retype everything in proper case. You can't do this yet.
In general, this will work out fairly well. In the image below, I've used the formula to build a phrase containing a name in column A with an age in column C. The trick is when the number is displayed in one format and you want it to be used in another format.
Dates are a classic example of this.