1

I have a big sheet with quite a lot of info that keeps growing over time. Based on that, I have created several pivot tables that do some calculations and rankings.

Every ranking keeps growing, so pivot tables may contain 10 rows now, but can grow up to 20 or 30 rows.

I managed to insert several pivot tables in the same sheet and now it looks well, with every ranking after the next one. However, if I add multiple rows, the pivot tables grow and start overlapping, so after a while the ones below start disappearing in favour of the first ones.

Is there a way to have multiple pivot tables in the same sheet with a fixed number of rows among them, preventing them from overlapping?

If you want to 'play' with the data, I created a sample in https://docs.google.com/spreadsheets/d/1MVX3tp6GIqVX6hTyk6TsCxV7YngiMpi7E8oSxa7a9ck/edit?usp=sharing. It is just a ranking on races, where I want to track the best times on different legs. Then it has a second sheet 'rankings' in which I have different pivot tables, one for each leg.

fedorqui
  • 275,237
  • 103
  • 548
  • 598
  • What about if you adds a "safe zone" (meaning a lot of blank rows) between pivot tables, then hide the unused rows? Have you considered the use of Google Apps Script or are you looking for built-in features only? – Rubén Jun 04 '20 at 14:55
  • 1
    @Rubén well I started using pivot tables not a long ago ([when someone explained them to me](https://es.stackoverflow.com/a/289671/83) :D) and I feel they suffice for my needs right now, but I am definitely open to see other ways to solve the problem. – fedorqui Jun 04 '20 at 15:03

2 Answers2

2

HOw's this for a single formula solution that will scale infinitely in users or legs:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(results!A2:A&"|Best of "&results!D1:1&"|"&OFFSET(results!D2,,,ROWS(results!D2:D),COLUMNS(results!D1:1))),"|",0,0),"Select Col2,Col1,MIN(Col3) where Col1<>'' and Col2<>'Best of ' group by Col1,Col2 order by Col2, MIN(Col3) label MIN(Col3)'Best', Col1'User',Col2''"))

You'll find it in cell B1 on the new tab in your sample called MK.Idea

I should mention that FLATTEN() is an undocumented function that I only recently discovered. I've believe it is intended to remain "hidden" in the back end of the sheets programming, but if what I did is what you're after, there really isn't a more efficient way to do it. I've spoken with an engineer at Google who was surprised it existed as well and told me there were no plans to deprecate it, so here's hoping! For a demo of what it does generally, you can see my sample here: https://docs.google.com/spreadsheets/d/196NDPUZ-p2sPiiiYlYsJeHD6F_eJq7CWO_hP7rFqGpc/edit?usp=sharing

Rubén
  • 34,714
  • 9
  • 70
  • 166
MattKing
  • 7,373
  • 8
  • 13
  • 1
    Wow! I will need time to go through all the formula and try to adapt it to my case. Many thanks for discovering such powerful tools – fedorqui Jun 05 '20 at 15:44
  • First time for me to read about FLATTEN, so +1 :) There is at least one caveat of using a formula like this that might should be mentioned like `QUERY` doesn't allow to have different data types in a column – Rubén Jun 05 '20 at 17:09
1

Spreadsheets and Pivot Tables are marvelous tools for data analysis but they aren't too friendly for creating reports and dashboards, if you are open, to recommendations try Google Data Studio (it includes pivot tables too --> https://support.google.com/datastudio/answer/7516660?hl=en)

Let say that you don't have time to learn another app or you just prefer to keep using spreadsheets, in this case it will be required to implement a workaround.

First, bear in mind that Pivot Tables don't actually overlap, instead an error message is shown:

Solution: insert some rows/columns to give enough room to the Pivot Table to be expanded.

NOTE: You could do this in advance by including a "safe zone" (meaning blank rows /columns) around the pivot tables. You could hide/unhide this "safe zone" as needed.

If you don't want to do the above manually, I think that it should be possible to use an on edit trigger of Google Apps Script to detect that pivot tables are shown and insert new rows/columns to avoid this. If the Pivot Table top left cell returns #REF! your script could use a do.. while or a while to insert the required rows or columns. An smart algorithm will read the Pivot Table settings to calculate the required rows and columns and then insert the required rows / columns in one pass.

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • Cool! And will the hiden rows become unhidden when the data reaches them? I mean, if I have 10 rows followed by 5 hidden ones, will those 5 rows become unhidden when the result of the pivot gets bigger than 10 rows? – fedorqui Jun 05 '20 at 15:46
  • @fedorqui'SOstopharming' AFAIK Goole Sheets will not do that automatically so you will have to show them manually or by using a script. – Rubén Jun 05 '20 at 15:52