Grouping columns

[PharmD]

Hello! I have a report that I generate at work that lists medications entered by various providers at our hospital. It will have a column for the medication name, a column for the dose, and a column for the name of the provider. The list is VERY long. Is there a way to automatically group each of the providers prescriptions? For example, instead of having 62 separate rows for "Dr. Blue", and 102 from "Dr. White", can I click something to make it say " Dr. Blue-62", "Dr. White-102", and then click to expand if I wanted to see the individual orders? What I am ultimately interested in is a quick way to figure out the number of prescriptions entered by each provider. There are a lot of providers, so going through and counting manually is very time consuming. Thanks!

[#2]

Hello @PharmD , 

What you describe can be easily done with a Pivot Table.  Select all the data, then click Insert > Pivot table. Now you see the pivot table pane. Here, drag the name into the "Rows" area and again into the Values area.  The pivot table will now show the count of each individual name.

[#3]

@PharmD 

There are so many ways of doing this Functions, Pivot Tables, Subtotal Tool, Power Query)

I'm going to show you a simple method:

I created a Sample File with Names in Column A and in Column F I have a Unique list of Names (No Duplicates) and the Function in G2 reads:
=COUNTIF($A$2:$A$351,F2)

I also created the same functionality with a drop list in Cell I2 and the function in cell J2 combines the name with the number of occurences as follows:

=I2&" -"&COUNTIF($A$2:$A$351,I2)

Please look at the attached file

Hope that Helps

Nabil Mourad

[#4]

@nabilmourad  ... the pivot table creates the list of unique names dynamically. With hundreds of rows it might be easy to miss a name when creating the list for a formula.

[#5]

@Ingeborg Hawighorst 

I suspect that, if datasets are sufficiently small that you update them by hand rather than importing a fresh query from an external source, the modern dynamic array might be a contender. 

The new functions UNIQUE and SORT are fast and reliable and they update automatically as the data is changed.  The SUMIFS are somewhat more labour-intensive to create but it may only need a single instance of the formula to spill and populate the crosstab or list dynamically.  To home in on a specific provider, one could use FILTER to create a separate output table rather than the traditional worksheet filter that simply hides much of the source data.  Not quite as slick as slicers though.

The Pivot Table may still have the edge but it is no longer the only game in town!

[#6]

@Peter Bartholomew Definitely! Dynamic Arrays will be a game changer and make many a pivot table obsolete.

However, I'm conscious of the fact that as of today Dynamic Arrays are still only available to people who run Office 365 Insider Fast, so I'm advising the solution that will work for everybody right now.

[#7]

@PharmD 

I agree with all what was mentioned in the thread

The beauty of Excel is the availability of multiple techniques that achieve the same goal.
@Ingeborg Hawighorst  I love Pivot tables but I did not mention that as you had already covered it perfectly. On the other hand the function has an advantage over Pivot Tables (If the list of names is unchanged) which is automatically updating (specially if I use it with a Table or a defined name for an expandable range) while the PT requires Refreshing manually (unless you write a small code in VBA) or the maximum you can set the option to refresh on Opening the file.

I also agree with @Peter Bartholomew  on how practical Dynamic Arrays are but, among thousands of students I teach still very few have access to them... I made that survey just yesterday when I was introducing the new giant function XLOOKUP...
The moral of this post is... This is a wonderful community we all help each other.

have a great day everyone

Nabil Mourad