0

https://docs.google.com/spreadsheets/d/1GLwRcQ47M-NvIuxZY-VUPyE1qXtprqcmtFaKVXUIzvs/edit?usp=sharing

I have a problem in above google sheets. Basically I want to transpose data horizontal to vertical database. Example like below

Before

Name Type 2018 2019 2020 2021
Apple Fix 2000 3000 4000 5000

After

Name Type Year Value
Apple Fix 2018 2000
Apple Fix 2019 3000
Apple Fix 2020 4000
Apple Fix 2021 5000

Right now I do it manually copy and paste. But the data will grow every day. Right now I had almost 1000 rows so doing it manually will not fruitful. I am guessing that I need to make google apps script to solve this problem. May be someone can give suggestion how to solve this problem.

Thanks before

Fendy Sion 04
  • 23
  • 4
  • 1
    See tab called Proposal Result with the main formula in B2 `=query(arrayformula(split(flatten(Source!B2:B &"|"& Source!C2:C &"|"& Source!D2:D &"|"& Source!E2:E &"|"& Source!F2:F &"|"& Source!G1:R1 &"|"& Source!G2:R &"|"& Source!S2:S),"|")),"select * where Col2 is not null")` – Mike Steelson Nov 26 '21 at 11:58
  • Hi @MikeSteelson thanks for the help. I'm sorry if my question is suspected to be duplicate with other similar question. I already give vote up for your answer. The formula Flatten is really new for me. Again many thanks for highlighting it up to me. Now I can work more effective, no need to sit all day long, using your solution. Best regards – Fendy Sion 04 Nov 26 '21 at 12:11
  • 1
    @MikeSteelson Don't see how your solution is different from https://stackoverflow.com/a/64389174/ The link provided is exhaustive and perfect. – TheMaster Nov 26 '21 at 13:09
  • 1
    About sharing spreadsheets, do note that [it exposes your email address](https://meta.stackoverflow.com/questions/394304/). So, use a dummy Google account if you consider your email address to be private. – TheMaster Nov 26 '21 at 13:10
  • @MikeSteelson OP already tagged [google-apps-script]. Almost all answers there work. It's OP's job to search and research before asking the question. (See https://meta.stackoverflow.com/questions/261592/) Even if not, if it's closed as duplicate, op should check all the answers there(barely 10 answers) and use the best thing suited to his needs. If OP can't be bothered to do that, We shouldn't go spoonfeeding OP. – TheMaster Nov 26 '21 at 15:50
  • Alright next time I will use dummy Google account. It's quite new for me. Thanks @TheMaster. – Fendy Sion 04 Nov 26 '21 at 16:38

0 Answers0