Feb 17, 2020
Manually re-ordering columns in Google Sheets Pivot Table
I want to do a trends table by fiscal quarter e.g. region by quarter, sales rep by qtr
- Data field for fiscal quarter is provided by CRM as Q1-2018, Q2-2019, Q3-2017 etc etc.
- So....when I pivot the data the columns are presented in the table in the order as follows
- Q1-2018 / Q1-2019 / Q1-2020 / Q2-2018 / Q2-2019 / Q3 - 2019 etc etc.
But - I want them to be sequential in time across the columns:-
- Q1-2018 / Q2-2018 / Q3-2018 / Q4-2018 / Q1-2019 /Q2-2019 etc.
I can't seem to drag columns to be able to reorder manually (like you can with the main rival spreadsheet product). I can't change the CRM fields. It's automated so I don't want to add manual fields.
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
Feb 17, 2020
=ArrayFormula(IF(LEN(A2:A),RIGHT(A2:A,4)&left(A2:A,2),))
Original Poster Lee Bonnici marked this as an answer
Feb 17, 2020
I don't see Chris Hick around at the moment ... so let me suggest
if you had the following entries in cells J8:J11
Q1-2018
Q1-2019
Q3-2019
Q1-2020
then use the following formula in anothe cell outside of J8:J11
=ArrayFormula(right(J8:J11,4)&left(J8:J11,2))
to convert the entries into
2018Q1
2019Q1
2019Q3
2020Q1
I hope this helps.
Cheers!
Yogi Anand
Original Poster Lee Bonnici marked this as an answer
All Replies (5)
Feb 17, 2020
In the meantime, could you add an additional column to the right-hand side of your CRM data that would specify the column order and then place that as the top value in the pivot table?
I set up this example sheet - the formula is in cell C1 and uses a VLOOKUP to place the quarters in the correct order:
=ARRAYFORMULA(IF(LEN(A2:A),VLOOKUP(A2:A,{"Q1-2018",1;"Q2-2018",2;"Q3-2018",3;"Q4-2018",4;"Q1-2019",5;"Q2-2019",6;"Q3-2019",7;"Q4-2019",8;"Q1-2020",9;"Q2-2020",10;"Q3-2020",11;"Q4-2020",12},2,0),))
Let me know if you think it will work for you
Feb 17, 2020
In the meantime, what I think would work better is if I could create a formula to move the year first, then ad the quarter. So it would read as 2018Q1; 2018Q2; 2019Q1; 2019Q2 etc. That was I think the ordering would be correct. I have lots of quarters and years, so trying to avoid the long "if" references and ordering value.
Is there a way to do such a thing? i.e. take the last 4 digits (e.g. 2018) and move them to the front of a string and take the first 2 digits (e.g. Q3) and move them to the end (removing the hyphen)?
Feb 17, 2020
In the meantime, what I think would work better is if I could create a formula to move the year first, then ad the quarter. So it would read as 2018Q1; 2018Q2; 2019Q1; 2019Q2 etc. That was I think the ordering would be correct. I have lots of quarters and years, so trying to avoid the long "if" references and ordering value.
Is there a way to do such a thing? i.e. take the last 4 digits (e.g. 2018) and move them to the front of a string and take the first 2 digits (e.g. Q3) and move them to the end (removing the hyphen)?
=ArrayFormula(IF(LEN(A2:A),RIGHT(A2:A,4)&left(A2:A,2),))
Original Poster Lee Bonnici marked this as an answer
I don't see Chris Hick around at the moment ... so let me suggest
if you had the following entries in cells J8:J11
Q1-2018
Q1-2019
Q3-2019
Q1-2020
then use the following formula in anothe cell outside of J8:J11
=ArrayFormula(right(J8:J11,4)&left(J8:J11,2))
to convert the entries into
2018Q1
2019Q1
2019Q3
2020Q1
I hope this helps.
Cheers!
Yogi Anand
Original Poster Lee Bonnici marked this as an answer