Rotate and duplicate row values in Google SheetsJoin cells and headers value in a row with Google Sheets filtering blank cellsGoogle sheet group column data and display values in rowGet row and column of MAX(range)Conditionally formatting duplicate values in Google sheets with exclusion criteria?Copying values between Google SheetsDuplicate values in dynamic sortingReturn first vertical value that matches given criterionConditional Formatting Duplicate Invoices in Google SheetsHow to sum values of the same row from different columns in a separate sheet with QUERY?Find previous non-blank cell, but without a specified range in Google Sheets
If the Charles SSL Proxy shows me sensitive data, is that data insecure/exposed?
why "American-born", not "America-born"?
How to safely discharge oneself
Gambler's Fallacy Dice
Good examples of "two is easy, three is hard" in computational sciences
Filter a file list against an integer array?
Is there a word for pant sleeves?
Eigenvalues of the Laplace-Beltrami operator on a compact Riemannnian manifold
How can sister protect herself from impulse purchases with a credit card?
Expand a hexagon
Is presenting a play showing Military characters in a bad light a crime in the US?
Difference in 1 user doing 1000 iterations and 1000 users doing 1 iteration in Load testing
Ribbon Cable Cross Talk - Is there a fix after the fact?
Barron states that 4.18×10⁸ joules equal 1 kcal, is this correct?
How to tease a romance without a cat and mouse chase?
Was murdering a slave illegal in American slavery, and if so, what punishments were given for it?
Reverse Array, Let Elements in New Array Equal Length of Original Array Elements - JavaScript
Do most Taxis give Receipts in London?
pwaS eht tirsf dna tasl setterl fo hace dorw
How to play vs. 1.e4 e5 2.Nf3 Nc6 3.Bc4 d6?
What city and town structures are important in a low fantasy medieval world?
Parse a C++14 integer literal
Why is this python script running in background consuming 100 % CPU?
Connecting circles clockwise in TikZ
Rotate and duplicate row values in Google Sheets
Join cells and headers value in a row with Google Sheets filtering blank cellsGoogle sheet group column data and display values in rowGet row and column of MAX(range)Conditionally formatting duplicate values in Google sheets with exclusion criteria?Copying values between Google SheetsDuplicate values in dynamic sortingReturn first vertical value that matches given criterionConditional Formatting Duplicate Invoices in Google SheetsHow to sum values of the same row from different columns in a separate sheet with QUERY?Find previous non-blank cell, but without a specified range in Google Sheets
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I'm trying to rotate values in a sheet and then duplicate the first value in the row as a header. For example:
a | b | c | d
would become:
a | b
-----
a | c
-----
a | d
Is there any way to do this with just Google Sheets commands, or do I need to use a script?
google-sheets formulas google-sheets-query google-sheets-arrayformula concatenate
edited 4 hours ago
user0
12.8k71737
asked 4 hours ago
Ethan ChapmanEthan Chapman
1085
New contributor
Ethan Chapman is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I'm trying to rotate values in a sheet and then duplicate the first value in the row as a header. For example:
a | b | c | d
would become:
a | b
-----
a | c
-----
a | d
Is there any way to do this with just Google Sheets commands, or do I need to use a script?
google-sheets formulas google-sheets-query google-sheets-arrayformula concatenate
edited 4 hours ago
user0
12.8k71737
asked 4 hours ago
Ethan ChapmanEthan Chapman
1085
New contributor
Ethan Chapman is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
can you provide larger sample of example?
– user0
4 hours ago
@user0 Sure, did that help?
– Ethan Chapman
4 hours ago
add a comment |
I'm trying to rotate values in a sheet and then duplicate the first value in the row as a header. For example:
a | b | c | d
would become:
a | b
-----
a | c
-----
a | d
Is there any way to do this with just Google Sheets commands, or do I need to use a script?
google-sheets formulas google-sheets-query google-sheets-arrayformula concatenate
edited 4 hours ago
user0
12.8k71737
asked 4 hours ago
Ethan ChapmanEthan Chapman
1085
New contributor
Ethan Chapman is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I'm trying to rotate values in a sheet and then duplicate the first value in the row as a header. For example:
a | b | c | d
would become:
a | b
-----
a | c
-----
a | d
Is there any way to do this with just Google Sheets commands, or do I need to use a script?
google-sheets formulas google-sheets-query google-sheets-arrayformula concatenate
google-sheets formulas google-sheets-query google-sheets-arrayformula concatenate
edited 4 hours ago
user0
12.8k71737
asked 4 hours ago
Ethan ChapmanEthan Chapman
1085
New contributor
Ethan Chapman is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 4 hours ago
user0
12.8k71737
asked 4 hours ago
Ethan ChapmanEthan Chapman
1085
New contributor
Ethan Chapman is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 4 hours ago
user0
12.8k71737
edited 4 hours ago
user0
12.8k71737
edited 4 hours ago
user0
12.8k71737
12.8k71737
asked 4 hours ago
Ethan ChapmanEthan Chapman
1085
New contributor
Ethan Chapman is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 4 hours ago
Ethan ChapmanEthan Chapman
1085
asked 4 hours ago
Ethan ChapmanEthan Chapman
1085
1085
New contributor
Ethan Chapman is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Ethan Chapman is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
can you provide larger sample of example?
– user0
4 hours ago
@user0 Sure, did that help?
– Ethan Chapman
4 hours ago
add a comment |
can you provide larger sample of example?
– user0
4 hours ago
@user0 Sure, did that help?
– Ethan Chapman
4 hours ago
can you provide larger sample of example?
– user0
4 hours ago
can you provide larger sample of example?
– user0
4 hours ago
@user0 Sure, did that help?
– Ethan Chapman
4 hours ago
@user0 Sure, did that help?
– Ethan Chapman
4 hours ago
add a comment |
1 Answer
1
active
oldest
votes
=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(
TRANSPOSE("♥"&A1&"♦"&B1:D1), , 999^99), "♥")), "♦")))
answered 4 hours ago
user0user0
12.8k71737
1
Wow! I'm still trying to wrap my head around how this works, but using ♥ and ♦ as delimiters was a neat trick! Very fast solution as well! Would you mind explaining how the blank query works?
– Ethan Chapman
4 hours ago
1
blank query is an alternative to JOIN or TEXTJOIN which are limited to join only 50000 characters. this "query smush" is pretty much limitless (can process 999^99 (= 9.05698E+296) rows of data)
– user0
4 hours ago
1
After picking this apart, I came up with=ARRAYFORMULA(SPLIT(TRANSPOSE("♥"&A1&"♦"&B1:D1), "♦♥", TRUE)), which seems more efficient. Are there any issues with this solution?
– Ethan Chapman
4 hours ago
1
nah, that would work too for one row. however, it's not scalable so: i.stack.imgur.com/YumzC.png
– user0
4 hours ago
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "34"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
noCode: true, onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Ethan Chapman is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fwebapps.stackexchange.com%2fquestions%2f129866%2frotate-and-duplicate-row-values-in-google-sheets%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(
TRANSPOSE("♥"&A1&"♦"&B1:D1), , 999^99), "♥")), "♦")))
answered 4 hours ago
user0user0
12.8k71737
1
Wow! I'm still trying to wrap my head around how this works, but using ♥ and ♦ as delimiters was a neat trick! Very fast solution as well! Would you mind explaining how the blank query works?
– Ethan Chapman
4 hours ago
1
blank query is an alternative to JOIN or TEXTJOIN which are limited to join only 50000 characters. this "query smush" is pretty much limitless (can process 999^99 (= 9.05698E+296) rows of data)
– user0
4 hours ago
1
After picking this apart, I came up with=ARRAYFORMULA(SPLIT(TRANSPOSE("♥"&A1&"♦"&B1:D1), "♦♥", TRUE)), which seems more efficient. Are there any issues with this solution?
– Ethan Chapman
4 hours ago
1
nah, that would work too for one row. however, it's not scalable so: i.stack.imgur.com/YumzC.png
– user0
4 hours ago
add a comment |
=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(
TRANSPOSE("♥"&A1&"♦"&B1:D1), , 999^99), "♥")), "♦")))
answered 4 hours ago
user0user0
12.8k71737
1
Wow! I'm still trying to wrap my head around how this works, but using ♥ and ♦ as delimiters was a neat trick! Very fast solution as well! Would you mind explaining how the blank query works?
– Ethan Chapman
4 hours ago
1
blank query is an alternative to JOIN or TEXTJOIN which are limited to join only 50000 characters. this "query smush" is pretty much limitless (can process 999^99 (= 9.05698E+296) rows of data)
– user0
4 hours ago
1
After picking this apart, I came up with=ARRAYFORMULA(SPLIT(TRANSPOSE("♥"&A1&"♦"&B1:D1), "♦♥", TRUE)), which seems more efficient. Are there any issues with this solution?
– Ethan Chapman
4 hours ago
1
nah, that would work too for one row. however, it's not scalable so: i.stack.imgur.com/YumzC.png
– user0
4 hours ago
add a comment |
=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(
TRANSPOSE("♥"&A1&"♦"&B1:D1), , 999^99), "♥")), "♦")))
answered 4 hours ago
user0user0
12.8k71737
=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(
TRANSPOSE("♥"&A1&"♦"&B1:D1), , 999^99), "♥")), "♦")))
answered 4 hours ago
user0user0
12.8k71737
answered 4 hours ago
user0user0
12.8k71737
answered 4 hours ago
user0user0
12.8k71737
answered 4 hours ago
user0user0
12.8k71737
12.8k71737
1
Wow! I'm still trying to wrap my head around how this works, but using ♥ and ♦ as delimiters was a neat trick! Very fast solution as well! Would you mind explaining how the blank query works?
– Ethan Chapman
4 hours ago
1
blank query is an alternative to JOIN or TEXTJOIN which are limited to join only 50000 characters. this "query smush" is pretty much limitless (can process 999^99 (= 9.05698E+296) rows of data)
– user0
4 hours ago
1
After picking this apart, I came up with=ARRAYFORMULA(SPLIT(TRANSPOSE("♥"&A1&"♦"&B1:D1), "♦♥", TRUE)), which seems more efficient. Are there any issues with this solution?
– Ethan Chapman
4 hours ago
1
nah, that would work too for one row. however, it's not scalable so: i.stack.imgur.com/YumzC.png
– user0
4 hours ago
add a comment |
1
Wow! I'm still trying to wrap my head around how this works, but using ♥ and ♦ as delimiters was a neat trick! Very fast solution as well! Would you mind explaining how the blank query works?
– Ethan Chapman
4 hours ago
1
blank query is an alternative to JOIN or TEXTJOIN which are limited to join only 50000 characters. this "query smush" is pretty much limitless (can process 999^99 (= 9.05698E+296) rows of data)
– user0
4 hours ago
1
After picking this apart, I came up with=ARRAYFORMULA(SPLIT(TRANSPOSE("♥"&A1&"♦"&B1:D1), "♦♥", TRUE)), which seems more efficient. Are there any issues with this solution?
– Ethan Chapman
4 hours ago
1
nah, that would work too for one row. however, it's not scalable so: i.stack.imgur.com/YumzC.png
– user0
4 hours ago
1
1
Wow! I'm still trying to wrap my head around how this works, but using ♥ and ♦ as delimiters was a neat trick! Very fast solution as well! Would you mind explaining how the blank query works?
– Ethan Chapman
4 hours ago
Wow! I'm still trying to wrap my head around how this works, but using ♥ and ♦ as delimiters was a neat trick! Very fast solution as well! Would you mind explaining how the blank query works?
– Ethan Chapman
4 hours ago
1
1
blank query is an alternative to JOIN or TEXTJOIN which are limited to join only 50000 characters. this "query smush" is pretty much limitless (can process 999^99 (= 9.05698E+296) rows of data)
– user0
4 hours ago
blank query is an alternative to JOIN or TEXTJOIN which are limited to join only 50000 characters. this "query smush" is pretty much limitless (can process 999^99 (= 9.05698E+296) rows of data)
– user0
4 hours ago
1
1
After picking this apart, I came up with
=ARRAYFORMULA(SPLIT(TRANSPOSE("♥"&A1&"♦"&B1:D1), "♦♥", TRUE)), which seems more efficient. Are there any issues with this solution?– Ethan Chapman
4 hours ago
After picking this apart, I came up with
=ARRAYFORMULA(SPLIT(TRANSPOSE("♥"&A1&"♦"&B1:D1), "♦♥", TRUE)), which seems more efficient. Are there any issues with this solution?– Ethan Chapman
4 hours ago
1
1
nah, that would work too for one row. however, it's not scalable so: i.stack.imgur.com/YumzC.png
– user0
4 hours ago
nah, that would work too for one row. however, it's not scalable so: i.stack.imgur.com/YumzC.png
– user0
4 hours ago
add a comment |
Ethan Chapman is a new contributor. Be nice, and check out our Code of Conduct.
Ethan Chapman is a new contributor. Be nice, and check out our Code of Conduct.
Ethan Chapman is a new contributor. Be nice, and check out our Code of Conduct.
Ethan Chapman is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Web Applications Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fwebapps.stackexchange.com%2fquestions%2f129866%2frotate-and-duplicate-row-values-in-google-sheets%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
can you provide larger sample of example?
– user0
4 hours ago
@user0 Sure, did that help?
– Ethan Chapman
4 hours ago