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     www.energyefficientbuild.com.


user aleksialkio said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/n4xP-jmDtXQ
Using query to reorganize the player details
Hi,

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.

https://docs.google.com/spreadsheet/pub?key=0AkMVQyM7UcFddGg0SG56LTFlRVNZZTVIZGNzUHFJLVE&output=html

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

http://yogi--anand-consulting.blogspot.com/2012/07/yogirearrange-data-by-entities-in.html 

----

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?

Thanks,

Alexi

--------------------------------------------------------------------------------------------

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