Transpose column and add separator column
I'm trying to transpose a column from one sheet into a row of another sheet with a new blank column separating each result
=TRANSPOSE(Sheet1!A1:A30)
Whats the easist way to achieve this without having to add a blank row between each of the rows in the orginal sheet
Thanks
google-sheets google-sheets-query
asked Nov 15 '18 at 8:34
GrimlockzGrimlockz
1,04841931
add a comment |
I'm trying to transpose a column from one sheet into a row of another sheet with a new blank column separating each result
=TRANSPOSE(Sheet1!A1:A30)
Whats the easist way to achieve this without having to add a blank row between each of the rows in the orginal sheet
Thanks
google-sheets google-sheets-query
asked Nov 15 '18 at 8:34
GrimlockzGrimlockz
1,04841931
add a comment |
I'm trying to transpose a column from one sheet into a row of another sheet with a new blank column separating each result
=TRANSPOSE(Sheet1!A1:A30)
Whats the easist way to achieve this without having to add a blank row between each of the rows in the orginal sheet
Thanks
google-sheets google-sheets-query
asked Nov 15 '18 at 8:34
GrimlockzGrimlockz
1,04841931
I'm trying to transpose a column from one sheet into a row of another sheet with a new blank column separating each result
=TRANSPOSE(Sheet1!A1:A30)
Whats the easist way to achieve this without having to add a blank row between each of the rows in the orginal sheet
Thanks
google-sheets google-sheets-query
google-sheets google-sheets-query
asked Nov 15 '18 at 8:34
GrimlockzGrimlockz
1,04841931
asked Nov 15 '18 at 8:34
GrimlockzGrimlockz
1,04841931
asked Nov 15 '18 at 8:34
GrimlockzGrimlockz
1,04841931
asked Nov 15 '18 at 8:34
GrimlockzGrimlockz
1,04841931
asked Nov 15 '18 at 8:34
GrimlockzGrimlockz
1,04841931
1,04841931
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
I think this may be the easiest way
split(textjoin("||",,Sheet1!A1:A30),"|",,false)
answered Nov 15 '18 at 12:16
Tom SharpeTom Sharpe
12.9k31225
1
Simple! Thank you!
– Max Makhrov
Nov 15 '18 at 15:38
add a comment |
This answer is based on Toms answer:
split(textjoin("||",,Sheet1!A1:A30),"|",,false)
I like the solution because it is simple.
More general question would be:
How to add N extra separator columns with a formula
Here's the formula:
=TRANSPOSE(SPLIT(JOIN("|"&rept("|",1),A1:A30),"|",1,0))
where
- "|" is a rare char you do not have in your dataset
rept("|",1)is to get N separator columns. Change 1 to N.
The only problem with the formula is join function limit on 50000 characters.
The final function won't give the error with a large dataset.
Please try:
=TRANSPOSE(ArrayFormula(TRIM(SPLIT(QUERY(A1:A30&"|"&rept("|",1),,2^99),"|",1,0))))
queryreplacesjoinand have no limits
trimis needed because query creates spaces at the end of each line.
answered Nov 16 '18 at 7:24
Max MakhrovMax Makhrov
10.5k31933
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
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%2fstackoverflow.com%2fquestions%2f53315265%2ftranspose-column-and-add-separator-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
I think this may be the easiest way
split(textjoin("||",,Sheet1!A1:A30),"|",,false)
answered Nov 15 '18 at 12:16
Tom SharpeTom Sharpe
12.9k31225
1
Simple! Thank you!
– Max Makhrov
Nov 15 '18 at 15:38
add a comment |
I think this may be the easiest way
split(textjoin("||",,Sheet1!A1:A30),"|",,false)
answered Nov 15 '18 at 12:16
Tom SharpeTom Sharpe
12.9k31225
1
Simple! Thank you!
– Max Makhrov
Nov 15 '18 at 15:38
add a comment |
I think this may be the easiest way
split(textjoin("||",,Sheet1!A1:A30),"|",,false)
answered Nov 15 '18 at 12:16
Tom SharpeTom Sharpe
12.9k31225
I think this may be the easiest way
split(textjoin("||",,Sheet1!A1:A30),"|",,false)
answered Nov 15 '18 at 12:16
Tom SharpeTom Sharpe
12.9k31225
answered Nov 15 '18 at 12:16
Tom SharpeTom Sharpe
12.9k31225
answered Nov 15 '18 at 12:16
Tom SharpeTom Sharpe
12.9k31225
answered Nov 15 '18 at 12:16
Tom SharpeTom Sharpe
12.9k31225
12.9k31225
1
Simple! Thank you!
– Max Makhrov
Nov 15 '18 at 15:38
add a comment |
1
Simple! Thank you!
– Max Makhrov
Nov 15 '18 at 15:38
1
1
Simple! Thank you!
– Max Makhrov
Nov 15 '18 at 15:38
Simple! Thank you!
– Max Makhrov
Nov 15 '18 at 15:38
add a comment |
This answer is based on Toms answer:
split(textjoin("||",,Sheet1!A1:A30),"|",,false)
I like the solution because it is simple.
More general question would be:
How to add N extra separator columns with a formula
Here's the formula:
=TRANSPOSE(SPLIT(JOIN("|"&rept("|",1),A1:A30),"|",1,0))
where
- "|" is a rare char you do not have in your dataset
rept("|",1)is to get N separator columns. Change 1 to N.
The only problem with the formula is join function limit on 50000 characters.
The final function won't give the error with a large dataset.
Please try:
=TRANSPOSE(ArrayFormula(TRIM(SPLIT(QUERY(A1:A30&"|"&rept("|",1),,2^99),"|",1,0))))
queryreplacesjoinand have no limits
trimis needed because query creates spaces at the end of each line.
answered Nov 16 '18 at 7:24
Max MakhrovMax Makhrov
10.5k31933
add a comment |
This answer is based on Toms answer:
split(textjoin("||",,Sheet1!A1:A30),"|",,false)
I like the solution because it is simple.
More general question would be:
How to add N extra separator columns with a formula
Here's the formula:
=TRANSPOSE(SPLIT(JOIN("|"&rept("|",1),A1:A30),"|",1,0))
where
- "|" is a rare char you do not have in your dataset
rept("|",1)is to get N separator columns. Change 1 to N.
The only problem with the formula is join function limit on 50000 characters.
The final function won't give the error with a large dataset.
Please try:
=TRANSPOSE(ArrayFormula(TRIM(SPLIT(QUERY(A1:A30&"|"&rept("|",1),,2^99),"|",1,0))))
queryreplacesjoinand have no limits
trimis needed because query creates spaces at the end of each line.
answered Nov 16 '18 at 7:24
Max MakhrovMax Makhrov
10.5k31933
add a comment |
This answer is based on Toms answer:
split(textjoin("||",,Sheet1!A1:A30),"|",,false)
I like the solution because it is simple.
More general question would be:
How to add N extra separator columns with a formula
Here's the formula:
=TRANSPOSE(SPLIT(JOIN("|"&rept("|",1),A1:A30),"|",1,0))
where
- "|" is a rare char you do not have in your dataset
rept("|",1)is to get N separator columns. Change 1 to N.
The only problem with the formula is join function limit on 50000 characters.
The final function won't give the error with a large dataset.
Please try:
=TRANSPOSE(ArrayFormula(TRIM(SPLIT(QUERY(A1:A30&"|"&rept("|",1),,2^99),"|",1,0))))
queryreplacesjoinand have no limits
trimis needed because query creates spaces at the end of each line.
answered Nov 16 '18 at 7:24
Max MakhrovMax Makhrov
10.5k31933
This answer is based on Toms answer:
split(textjoin("||",,Sheet1!A1:A30),"|",,false)
I like the solution because it is simple.
More general question would be:
How to add N extra separator columns with a formula
Here's the formula:
=TRANSPOSE(SPLIT(JOIN("|"&rept("|",1),A1:A30),"|",1,0))
where
- "|" is a rare char you do not have in your dataset
rept("|",1)is to get N separator columns. Change 1 to N.
The only problem with the formula is join function limit on 50000 characters.
The final function won't give the error with a large dataset.
Please try:
=TRANSPOSE(ArrayFormula(TRIM(SPLIT(QUERY(A1:A30&"|"&rept("|",1),,2^99),"|",1,0))))
queryreplacesjoinand have no limits
trimis needed because query creates spaces at the end of each line.
answered Nov 16 '18 at 7:24
Max MakhrovMax Makhrov
10.5k31933
answered Nov 16 '18 at 7:24
Max MakhrovMax Makhrov
10.5k31933
answered Nov 16 '18 at 7:24
Max MakhrovMax Makhrov
10.5k31933
answered Nov 16 '18 at 7:24
Max MakhrovMax Makhrov
10.5k31933
10.5k31933
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53315265%2ftranspose-column-and-add-separator-column%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
