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