Saturday, August 4, 2012

yogi_Rearrange Data By Entities In Column B C D And Set Of Specified Number of Columns _Solution2

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #669   Aug 04, 2012

user aleksialkio said: (!category-topic/docs/spreadsheets/n4xP-jmDtXQ
Using query to reorganize the player details
I'm trying to organize the spreadsheet to a new way. I'm wondering is the query function able to do this and I have tried to think it with many ways but I do need some help. Perhaps I'm thinking too difficult :-)
I have a form that collect the information from one team to one row. I need better way to show them and I will make individual sheets for every age group to publish. I made an example sheet what I need to do.
Thanks, Aleksi
I had provided a solution to Aleksi as presented in my following blog post:

yogi_Rearrange Data By Entities In Column B C D And Set Of Specified Number of Columns
however Aleksi commented
Hi Yogi,
Thanks for the example. I tried it and it works. Then I realized a huge problem: I will have 800 teams in one season and 80 colums in the main page (form) so it means 64.000 cells and because I have 6 individual sheets for every age group, it will double 128.000. This is still under the limit so everything is okey.. If I use Index formula as in your example, I will have approx. 800 teams * 15 players * 5 cell = 60.000 formulas and that's over the limit. Query or arrayformula would fix this problem because the continue is not caltulated as a formula (as I red from another post). Now the problem is how to use this formula in my case?
I don't know about Aleksi's project scope, constraints, and preferences, however, I have presented another solution in my following blog post

wherein I have taken the source data in Sheet1 and prepped it foe QUERYing (see yogi_Sheet1) and then I have extracted data by Club, Coach, and Manager