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
I know that there is a preselected candidate for a position to be filled at my department. What should I do?
Do I need full recovery mode when I have multiple daily backup?
Website returning plaintext password
Did this character show any indication of wanting to rule before S8E6?
USPS Back Room - Trespassing?
Why do most published works in medical imaging try to reduce false positives?
Where have Brexit voters gone?
Specific alignment within beginalign environment
Ingress filtering on edge routers and performance concerns
What is the difference between singing and speaking?
Is it truly impossible to tell what a CPU is doing?
Melodic minor Major 9 chords
Why did Theresa May offer a vote on a second Brexit referendum?
How to attach cable mounting points to a bicycle frame?
Should one buy new hardware after a system compromise?
Could a 19.25mm revolver actually exist?
Is the field of q-series 'dead'?
When the Torah was almost lost and one (or several) Rabbis saved it?
Is the Unsullied name meant to be ironic? How did it come to be?
Is "cool" appropriate or offensive to use in IMs?
Can my floppy disk still work without a shutter spring?
Is it legal to have an abortion in another state or abroad?
Can I connect my older mathematica front-end to the free wolfram engine?
Efficient Algorithm for the boundary of a set of tiles
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 May 19 at 1:39
user0
13.2k71737
asked May 19 at 1:28
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 May 19 at 1:39
user0
13.2k71737
asked May 19 at 1:28
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
May 19 at 1:30
@user0 Sure, did that help?
– Ethan Chapman
May 19 at 1:32
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 May 19 at 1:39
user0
13.2k71737
asked May 19 at 1:28
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 May 19 at 1:39
user0
13.2k71737
asked May 19 at 1:28
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 May 19 at 1:39
user0
13.2k71737
asked May 19 at 1:28
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 May 19 at 1:39
user0
13.2k71737
edited May 19 at 1:39
user0
13.2k71737
edited May 19 at 1:39
user0
13.2k71737
13.2k71737
asked May 19 at 1:28
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 May 19 at 1:28
Ethan ChapmanEthan Chapman
1085
asked May 19 at 1:28
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
May 19 at 1:30
@user0 Sure, did that help?
– Ethan Chapman
May 19 at 1:32
add a comment |
can you provide larger sample of example?
– user0
May 19 at 1:30
@user0 Sure, did that help?
– Ethan Chapman
May 19 at 1:32
can you provide larger sample of example?
– user0
May 19 at 1:30
can you provide larger sample of example?
– user0
May 19 at 1:30
@user0 Sure, did that help?
– Ethan Chapman
May 19 at 1:32
@user0 Sure, did that help?
– Ethan Chapman
May 19 at 1:32
add a comment |
1 Answer
1
active
oldest
votes
=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(
TRANSPOSE("♥"&A1&"♦"&B1:D1), , 999^99), "♥")), "♦")))
answered May 19 at 1:38
user0user0
13.2k71737
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
May 19 at 1:45
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
May 19 at 1:51
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
May 19 at 1:56
1
nah, that would work too for one row. however, it's not scalable so: i.stack.imgur.com/YumzC.png
– user0
May 19 at 2:06
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 May 19 at 1:38
user0user0
13.2k71737
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
May 19 at 1:45
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
May 19 at 1:51
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
May 19 at 1:56
1
nah, that would work too for one row. however, it's not scalable so: i.stack.imgur.com/YumzC.png
– user0
May 19 at 2:06
add a comment |
=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(
TRANSPOSE("♥"&A1&"♦"&B1:D1), , 999^99), "♥")), "♦")))
answered May 19 at 1:38
user0user0
13.2k71737
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
May 19 at 1:45
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
May 19 at 1:51
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
May 19 at 1:56
1
nah, that would work too for one row. however, it's not scalable so: i.stack.imgur.com/YumzC.png
– user0
May 19 at 2:06
add a comment |
=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(
TRANSPOSE("♥"&A1&"♦"&B1:D1), , 999^99), "♥")), "♦")))
answered May 19 at 1:38
user0user0
13.2k71737
=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(
TRANSPOSE("♥"&A1&"♦"&B1:D1), , 999^99), "♥")), "♦")))
answered May 19 at 1:38
user0user0
13.2k71737
answered May 19 at 1:38
user0user0
13.2k71737
answered May 19 at 1:38
user0user0
13.2k71737
answered May 19 at 1:38
user0user0
13.2k71737
13.2k71737
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
May 19 at 1:45
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
May 19 at 1:51
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
May 19 at 1:56
1
nah, that would work too for one row. however, it's not scalable so: i.stack.imgur.com/YumzC.png
– user0
May 19 at 2:06
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
May 19 at 1:45
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
May 19 at 1:51
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
May 19 at 1:56
1
nah, that would work too for one row. however, it's not scalable so: i.stack.imgur.com/YumzC.png
– user0
May 19 at 2:06
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
May 19 at 1:45
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
May 19 at 1:45
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
May 19 at 1:51
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
May 19 at 1:51
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
May 19 at 1:56
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
May 19 at 1:56
1
1
nah, that would work too for one row. however, it's not scalable so: i.stack.imgur.com/YumzC.png
– user0
May 19 at 2:06
nah, that would work too for one row. however, it's not scalable so: i.stack.imgur.com/YumzC.png
– user0
May 19 at 2:06
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
May 19 at 1:30
@user0 Sure, did that help?
– Ethan Chapman
May 19 at 1:32