How do I separate values into seperate columns?
I have a list containing two bits of data, let's say fruit and date. What I want to do is have a second sheet that has fruit as headings and have the date pulled across into the correct column every time one is sold on the other sheet (which will be entered by the user). Essentially i'm trying to get it to search for the fruit and every time it finds a match, add the date to the second sheet in the correct column automatically
Apple 28/01/2019
Apple 01/08/2019
Pear 02/08/2019
Pear 04/05/2019
Mango 01/02/2019
Peach 08/09/2019
I need the data presented columnar, under one header for each fruit.
I've tried this array, but I can't get it right:
=IFERROR(INDEX($C$5:$C$104, SMALL(IF('Sheet 2'!B$5=$B$5:$B$, ROW($C$5:$C$104)-6,""), ROW()-6)),"")
microsoft-excel
edited Jan 29 at 15:44
cybernetic.nomad
1,726312
asked Jan 28 at 22:05
Fred WeatherbyFred Weatherby
11
add a comment |
I have a list containing two bits of data, let's say fruit and date. What I want to do is have a second sheet that has fruit as headings and have the date pulled across into the correct column every time one is sold on the other sheet (which will be entered by the user). Essentially i'm trying to get it to search for the fruit and every time it finds a match, add the date to the second sheet in the correct column automatically
Apple 28/01/2019
Apple 01/08/2019
Pear 02/08/2019
Pear 04/05/2019
Mango 01/02/2019
Peach 08/09/2019
I need the data presented columnar, under one header for each fruit.
I've tried this array, but I can't get it right:
=IFERROR(INDEX($C$5:$C$104, SMALL(IF('Sheet 2'!B$5=$B$5:$B$, ROW($C$5:$C$104)-6,""), ROW()-6)),"")
microsoft-excel
edited Jan 29 at 15:44
cybernetic.nomad
1,726312
asked Jan 28 at 22:05
Fred WeatherbyFred Weatherby
11
1
Can you please edit your question to provi9de sample data, expected result and what you've tried so far?
– cybernetic.nomad
Jan 28 at 22:48
Hi Cybernetic, I made a silly mistake, got the row start wrong. I appreciate you trying to help me correctly format my question.
– Fred Weatherby
Jan 28 at 23:18
1
How does your example data relate to the cell references in your formula? Are you aware of the Text to Columns feature? You can have Excel split the data into columns based on the space character in one step. It does it "in-place", so if you need to preserve the original data, copy and paste it to a scratch location.
– fixer1234
Jan 28 at 23:22
Could you provide a sample about your problem?
– Lee
Jan 29 at 8:56
add a comment |
I have a list containing two bits of data, let's say fruit and date. What I want to do is have a second sheet that has fruit as headings and have the date pulled across into the correct column every time one is sold on the other sheet (which will be entered by the user). Essentially i'm trying to get it to search for the fruit and every time it finds a match, add the date to the second sheet in the correct column automatically
Apple 28/01/2019
Apple 01/08/2019
Pear 02/08/2019
Pear 04/05/2019
Mango 01/02/2019
Peach 08/09/2019
I need the data presented columnar, under one header for each fruit.
I've tried this array, but I can't get it right:
=IFERROR(INDEX($C$5:$C$104, SMALL(IF('Sheet 2'!B$5=$B$5:$B$, ROW($C$5:$C$104)-6,""), ROW()-6)),"")
microsoft-excel
edited Jan 29 at 15:44
cybernetic.nomad
1,726312
asked Jan 28 at 22:05
Fred WeatherbyFred Weatherby
11
I have a list containing two bits of data, let's say fruit and date. What I want to do is have a second sheet that has fruit as headings and have the date pulled across into the correct column every time one is sold on the other sheet (which will be entered by the user). Essentially i'm trying to get it to search for the fruit and every time it finds a match, add the date to the second sheet in the correct column automatically
Apple 28/01/2019
Apple 01/08/2019
Pear 02/08/2019
Pear 04/05/2019
Mango 01/02/2019
Peach 08/09/2019
I need the data presented columnar, under one header for each fruit.
I've tried this array, but I can't get it right:
=IFERROR(INDEX($C$5:$C$104, SMALL(IF('Sheet 2'!B$5=$B$5:$B$, ROW($C$5:$C$104)-6,""), ROW()-6)),"")
microsoft-excel
microsoft-excel
edited Jan 29 at 15:44
cybernetic.nomad
1,726312
asked Jan 28 at 22:05
Fred WeatherbyFred Weatherby
11
edited Jan 29 at 15:44
cybernetic.nomad
1,726312
asked Jan 28 at 22:05
Fred WeatherbyFred Weatherby
11
edited Jan 29 at 15:44
cybernetic.nomad
1,726312
edited Jan 29 at 15:44
cybernetic.nomad
1,726312
edited Jan 29 at 15:44
cybernetic.nomad
1,726312
1,726312
asked Jan 28 at 22:05
Fred WeatherbyFred Weatherby
11
asked Jan 28 at 22:05
Fred WeatherbyFred Weatherby
11
asked Jan 28 at 22:05
Fred WeatherbyFred Weatherby
11
11
1
Can you please edit your question to provi9de sample data, expected result and what you've tried so far?
– cybernetic.nomad
Jan 28 at 22:48
Hi Cybernetic, I made a silly mistake, got the row start wrong. I appreciate you trying to help me correctly format my question.
– Fred Weatherby
Jan 28 at 23:18
1
How does your example data relate to the cell references in your formula? Are you aware of the Text to Columns feature? You can have Excel split the data into columns based on the space character in one step. It does it "in-place", so if you need to preserve the original data, copy and paste it to a scratch location.
– fixer1234
Jan 28 at 23:22
Could you provide a sample about your problem?
– Lee
Jan 29 at 8:56
add a comment |
1
Can you please edit your question to provi9de sample data, expected result and what you've tried so far?
– cybernetic.nomad
Jan 28 at 22:48
Hi Cybernetic, I made a silly mistake, got the row start wrong. I appreciate you trying to help me correctly format my question.
– Fred Weatherby
Jan 28 at 23:18
1
How does your example data relate to the cell references in your formula? Are you aware of the Text to Columns feature? You can have Excel split the data into columns based on the space character in one step. It does it "in-place", so if you need to preserve the original data, copy and paste it to a scratch location.
– fixer1234
Jan 28 at 23:22
Could you provide a sample about your problem?
– Lee
Jan 29 at 8:56
1
1
Can you please edit your question to provi9de sample data, expected result and what you've tried so far?
– cybernetic.nomad
Jan 28 at 22:48
Can you please edit your question to provi9de sample data, expected result and what you've tried so far?
– cybernetic.nomad
Jan 28 at 22:48
Hi Cybernetic, I made a silly mistake, got the row start wrong. I appreciate you trying to help me correctly format my question.
– Fred Weatherby
Jan 28 at 23:18
Hi Cybernetic, I made a silly mistake, got the row start wrong. I appreciate you trying to help me correctly format my question.
– Fred Weatherby
Jan 28 at 23:18
1
1
How does your example data relate to the cell references in your formula? Are you aware of the Text to Columns feature? You can have Excel split the data into columns based on the space character in one step. It does it "in-place", so if you need to preserve the original data, copy and paste it to a scratch location.
– fixer1234
Jan 28 at 23:22
How does your example data relate to the cell references in your formula? Are you aware of the Text to Columns feature? You can have Excel split the data into columns based on the space character in one step. It does it "in-place", so if you need to preserve the original data, copy and paste it to a scratch location.
– fixer1234
Jan 28 at 23:22
Could you provide a sample about your problem?
– Lee
Jan 29 at 8:56
Could you provide a sample about your problem?
– Lee
Jan 29 at 8:56
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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%2fsuperuser.com%2fquestions%2f1399432%2fhow-do-i-separate-values-into-seperate-columns%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f1399432%2fhow-do-i-separate-values-into-seperate-columns%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
1
Can you please edit your question to provi9de sample data, expected result and what you've tried so far?
– cybernetic.nomad
Jan 28 at 22:48
Hi Cybernetic, I made a silly mistake, got the row start wrong. I appreciate you trying to help me correctly format my question.
– Fred Weatherby
Jan 28 at 23:18
1
How does your example data relate to the cell references in your formula? Are you aware of the Text to Columns feature? You can have Excel split the data into columns based on the space character in one step. It does it "in-place", so if you need to preserve the original data, copy and paste it to a scratch location.
– fixer1234
Jan 28 at 23:22
Could you provide a sample about your problem?
– Lee
Jan 29 at 8:56