Forum Discussion
Grouping columns
6 Replies
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.
IngeborgHawighorst 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 PeterBartholomew1 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
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
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.
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!
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.
