Wednesday, March 14, 2018

yogi_From Pivot Table Consolidate Data In Another Table On The Same Sheet

Google Spreadsheet   Post  #2396

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Mar-14-2018

question by: Tony Cannon
https://plus.google.com/102455272676579377322/posts/fdb2C2oFdjD
yogi_From Pivot Table Consolidate Data In Another Table On The Same Sheet 
Hi all

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!

Tony C