英文:
Repeat blocks of columns and row N times based of N number of Columns in google sheet
问题
I am currently working in Google Sheets and would like to create transpose specific columns and repeat on all rows in the table. For example:
| ID | Another header | Desc | Item1 | Item2 | Item3 | Item4 | Item5 |
|---|---|---|---|---|---|---|---|
| 1 | AA | BBB | 0 | 0 | 0 | 0 | 0 |
| 2 | BB | CCC | 0 | 1 | 0 | 0 | 0 |
I would like to get a result as:
| ID | Another header | Desc | Item | |
|---|---|---|---|---|
| 1 | AA | BBB | Item1 | |
| 1 | AA | BBB | Item2 | |
| 1 | AA | BBB | Item3 | |
| 1 | AA | BBB | Item4 | |
| 2 | BB | CCC | Item1 | |
| 2 | BB | CCC | Item2 | |
| 2 | BB | CCC | Item3 | |
| 2 | BB | CCC | Item4 |
I tried using a Lambda function LAMBDA(x, y, REDUCE(x, SEQUENCE(y-1), LAMBDA(a, b, IF(b, {a; x})))) (range, 4), but this only helped me replicate the rows. I would appreciate it if anyone could help me with this. Thank you!
英文:
I am currently working in Google sheet and would like to create transpose specific columns and repeat on all rows in the table, For example
| ID | Another header | Desc | Item1 | Item2 | Item3 | Item4 | Item5 |
|---|---|---|---|---|---|---|---|
| 1 | AA | BBB | 0 | 0 | 0 | 0 | 0 |
| 2 | BB | CCC | 0 | 1 | 0 | 0 | 0 |
I would like to get a result as
| ID | Another header | Desc | Item | |
|---|---|---|---|---|
| 1 | AA | BBB | Item1 | |
| 1 | AA | BBB | Item2 | |
| 1 | AA | BBB | Item3 | |
| 1 | AA | BBB | Item4 | |
| 2 | BB | CCC | Item1 | |
| 2 | BB | CCC | Item2 | |
| 2 | BB | CCC | Item3 | |
| 2 | BB | CCC | Item4 |
I tried using Lamba function LAMBDA(x, y, REDUCE(x, SEQUENCE(y-1), LAMBDA(a, b, IF(b, {a; x})))) (range, 4) but this only helped me to replicated the rows. I would appreciate if anyone could help me with this. Thank you !
答案1
得分: 1
Your formatting for the input table got a bit mangled but I think I was able to determine the layout. If your input table begins at A1 in the sheet you can try the following to generate the desired output:
={{A1:C1,"项目"};arrayformula(split(tocol(A2:A3&"|"&B2:B3&"|"&C2:C3&"|"&D1:H1),"|"))}
英文:
Your formatting for the input table got a bit mangled but I think I was able to determine the layout. If your input table begins at A1 in the sheet you can try the following to generate the desired output:
={{A1:C1,"Item"};arrayformula(split(tocol(A2:A3&"|"&B2:B3&"|"&C2:C3&"|"&D1:H1),"|"))}
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论