In the attached file I have made use of two helper columns in the Client_List sheet, to join the Group and sub group together and then to get a unique sequential reference for each entry.
Then in the Table sheet I have put this formula in C3:
This can then be copied across to column I and then C3:I3 copied down to row 10 to give you what you want for Group A.
I've also set up 3 identical tables lower down on the sheet to get the same results for the other groups - the formulae are slightly different in each table, as they refer to the group in the top cell of each table.
As an alternative, I've also set up data validation in cell A1 to enable you to choose the group from a drop-down, and the top-table will automatically adjust. If you decide to use this, then you can delete the lower tables.
Hope this helps.
Pete
Bookmarks