question by: Tony Cannon
yogi_From Pivot Table Consolidate Data In Another Table On The Same Sheet
I have an issue with a spreadsheet that I'm hoping you can give me some guidance on.
I help run a snooker competition in the summer and one of the statistics gathered is high breaks. Using pivot tables I split the data down into lists based upon the competition the player is in (Mens, 40+, U21, U16, Billiards)
Now, this works fine BUT when someone has multiple breaks worth recording they are listed multiple times, such as
1 John Farrell Republic of Ireland 132
2 John Farrell Republic of Ireland 104
3 Dean Young Scotland 101
4 Michael Judge Northern Ireland 93
I'd prefer to list them as
1 John Farrell Republic of Ireland 132, 104, 62, 47, 43
2 Dean Young Scotland 101
3 Michael Judge Northern Ireland 93, 77, 56, 47, 43
On this way the breaks table is shorter but keeps more names on display
The sample data set that I have complete with pivot table can be found attached. This sheet has the submissions, a pivot table listing the breaks from the Mens competition and a third sheet where I started playing around with UNIQUE and VLOOKUP.
I'm sure it isn't impossible, but I'm drawing a blank and I'm normally pretty good, probably starting off with the wrong base with UNIQUE/VLOOKUP...
Any help would be greatly appreciated!