Using summary tool for variable amount of fields

[Viwi]

Hi, I have been applying the summary tool to this dataset: 

Category	Alice	Bob
1	5	4
1	7	1
2	8	5
2	4	7
2	2	8

I would apply group by to "Category" and as aggregation method choose "max" for all names, so the result would be 

Category	Alice	Bob
1	7	4
2	8	8

However, the names in my source data may change (e.g. Bob-> Ben) and also the amount of names may vary. So I am looking for a workflow that can in addition to above aggregation also aggregate this table: 

Category	Alice	Ben	Oscar
1	1	2	3
1	6	5	4
2	7	8	9
2	7	8	9
2	6	5	4

to 

Category	Alice	Ben	Oscar
1	6	5	4
2	7	8	9

Thanks in advance for any help :-) 

[BrandonB · Accepted solution]

What about transposing your data first and grouping by category as a Key Column. This would change the orientation vertical and would accommodate new columns if you had the dynamic or unknown columns box checked. Then you could use a summarize tool where you group by category, group by the new "Name" column, and then choose Max on the values column. This would give you the max value for each name by category. Then if you want the names of the people back as column headers you would just crosstab the data back to a horizontal layout.