Combining Data From Rows into Columns, Excel 2016
1
So, In column A, I have a lot of repeating typeId's, with different data in columns C/D. I would like to take any row that has a duplicate typeID, take the data in column C/D and move it to the next two available columns, and delete the duplicates. Essentially condense the rows into columns creating a little bit more formula-friendly workspace for later use.
If the MaterialID's can be placed in order from lowest-highest left-right, that would be preferable. But, not too worried about that.
I would do it by hand, but I am working with nearly 50,000 lines, and around 16,000 typeid's, so thank you to anyone who has a solution and saves me that pain.
microsoft-excel
edited Jan 16 at 0:20
teylyn
17.3k22539
asked Jan 15 at 23:07
user3082908user3082908
61
add a comment |
1
So, In column A, I have a lot of repeating typeId's, with different data in columns C/D. I would like to take any row that has a duplicate typeID, take the data in column C/D and move it to the next two available columns, and delete the duplicates. Essentially condense the rows into columns creating a little bit more formula-friendly workspace for later use.
If the MaterialID's can be placed in order from lowest-highest left-right, that would be preferable. But, not too worried about that.
I would do it by hand, but I am working with nearly 50,000 lines, and around 16,000 typeid's, so thank you to anyone who has a solution and saves me that pain.
microsoft-excel
edited Jan 16 at 0:20
teylyn
17.3k22539
asked Jan 15 at 23:07
user3082908user3082908
61
Question Clarification: What is the end goal? You mention wanting to expand on the data through formulas. What will those formulas do? I think you're running into trouble pursuing this particular data structure. Perhaps you need it in this format to be parsed by some external program, but it sounds like you want to do further analysis in Excel, and this structure will cause heartache. -- Are there more than one Activity Type? Will there ever be? -- Are the material types guaranteed to be only a few? Will that ever change? -- I say "ever" on purpose.
– Haakon Dahl
Jan 16 at 2:32
1
The data in its original format is normalized, which is a rare gift in "found" sources. I don't know what particular formulas you would like to apply to the data (see clarifying question above). Based on the column names, it looks as though your data has actually been extracted from a database. Due to the multi-valued nature of the data itself, you should work with the database. The database can do the work you need done, and still export a spreadsheet to use with the presumably more familiar Excel tools.
– Haakon Dahl
Jan 16 at 2:36
@HaakonDahl I lilke the "rare gift" words.
– teylyn
Jan 16 at 4:06
The data will be used to make calculations. It is for a video game known as Eve Online. Each Material will have a "price" pulled from a separate sheet using a API (sort of). The data you are seeing are InputMaterials required to build the Outputs. The types of materials are never going to change, but sadly their are about 200 different material inputs possible.
– user3082908
Jan 19 at 4:44
add a comment |
1
1
1
So, In column A, I have a lot of repeating typeId's, with different data in columns C/D. I would like to take any row that has a duplicate typeID, take the data in column C/D and move it to the next two available columns, and delete the duplicates. Essentially condense the rows into columns creating a little bit more formula-friendly workspace for later use.
If the MaterialID's can be placed in order from lowest-highest left-right, that would be preferable. But, not too worried about that.
I would do it by hand, but I am working with nearly 50,000 lines, and around 16,000 typeid's, so thank you to anyone who has a solution and saves me that pain.
microsoft-excel
edited Jan 16 at 0:20
teylyn
17.3k22539
asked Jan 15 at 23:07
user3082908user3082908
61
So, In column A, I have a lot of repeating typeId's, with different data in columns C/D. I would like to take any row that has a duplicate typeID, take the data in column C/D and move it to the next two available columns, and delete the duplicates. Essentially condense the rows into columns creating a little bit more formula-friendly workspace for later use.
If the MaterialID's can be placed in order from lowest-highest left-right, that would be preferable. But, not too worried about that.
I would do it by hand, but I am working with nearly 50,000 lines, and around 16,000 typeid's, so thank you to anyone who has a solution and saves me that pain.
microsoft-excel
microsoft-excel
edited Jan 16 at 0:20
teylyn
17.3k22539
asked Jan 15 at 23:07
user3082908user3082908
61
edited Jan 16 at 0:20
teylyn
17.3k22539
asked Jan 15 at 23:07
user3082908user3082908
61
edited Jan 16 at 0:20
teylyn
17.3k22539
edited Jan 16 at 0:20
teylyn
17.3k22539
edited Jan 16 at 0:20
teylyn
17.3k22539
17.3k22539
asked Jan 15 at 23:07
user3082908user3082908
61
asked Jan 15 at 23:07
user3082908user3082908
61
asked Jan 15 at 23:07
user3082908user3082908
61
61
Question Clarification: What is the end goal? You mention wanting to expand on the data through formulas. What will those formulas do? I think you're running into trouble pursuing this particular data structure. Perhaps you need it in this format to be parsed by some external program, but it sounds like you want to do further analysis in Excel, and this structure will cause heartache. -- Are there more than one Activity Type? Will there ever be? -- Are the material types guaranteed to be only a few? Will that ever change? -- I say "ever" on purpose.
– Haakon Dahl
Jan 16 at 2:32
1
The data in its original format is normalized, which is a rare gift in "found" sources. I don't know what particular formulas you would like to apply to the data (see clarifying question above). Based on the column names, it looks as though your data has actually been extracted from a database. Due to the multi-valued nature of the data itself, you should work with the database. The database can do the work you need done, and still export a spreadsheet to use with the presumably more familiar Excel tools.
– Haakon Dahl
Jan 16 at 2:36
@HaakonDahl I lilke the "rare gift" words.
– teylyn
Jan 16 at 4:06
The data will be used to make calculations. It is for a video game known as Eve Online. Each Material will have a "price" pulled from a separate sheet using a API (sort of). The data you are seeing are InputMaterials required to build the Outputs. The types of materials are never going to change, but sadly their are about 200 different material inputs possible.
– user3082908
Jan 19 at 4:44
add a comment |
Question Clarification: What is the end goal? You mention wanting to expand on the data through formulas. What will those formulas do? I think you're running into trouble pursuing this particular data structure. Perhaps you need it in this format to be parsed by some external program, but it sounds like you want to do further analysis in Excel, and this structure will cause heartache. -- Are there more than one Activity Type? Will there ever be? -- Are the material types guaranteed to be only a few? Will that ever change? -- I say "ever" on purpose.
– Haakon Dahl
Jan 16 at 2:32
1
The data in its original format is normalized, which is a rare gift in "found" sources. I don't know what particular formulas you would like to apply to the data (see clarifying question above). Based on the column names, it looks as though your data has actually been extracted from a database. Due to the multi-valued nature of the data itself, you should work with the database. The database can do the work you need done, and still export a spreadsheet to use with the presumably more familiar Excel tools.
– Haakon Dahl
Jan 16 at 2:36
@HaakonDahl I lilke the "rare gift" words.
– teylyn
Jan 16 at 4:06
The data will be used to make calculations. It is for a video game known as Eve Online. Each Material will have a "price" pulled from a separate sheet using a API (sort of). The data you are seeing are InputMaterials required to build the Outputs. The types of materials are never going to change, but sadly their are about 200 different material inputs possible.
– user3082908
Jan 19 at 4:44
Question Clarification: What is the end goal? You mention wanting to expand on the data through formulas. What will those formulas do? I think you're running into trouble pursuing this particular data structure. Perhaps you need it in this format to be parsed by some external program, but it sounds like you want to do further analysis in Excel, and this structure will cause heartache. -- Are there more than one Activity Type? Will there ever be? -- Are the material types guaranteed to be only a few? Will that ever change? -- I say "ever" on purpose.
– Haakon Dahl
Jan 16 at 2:32
Question Clarification: What is the end goal? You mention wanting to expand on the data through formulas. What will those formulas do? I think you're running into trouble pursuing this particular data structure. Perhaps you need it in this format to be parsed by some external program, but it sounds like you want to do further analysis in Excel, and this structure will cause heartache. -- Are there more than one Activity Type? Will there ever be? -- Are the material types guaranteed to be only a few? Will that ever change? -- I say "ever" on purpose.
– Haakon Dahl
Jan 16 at 2:32
1
1
The data in its original format is normalized, which is a rare gift in "found" sources. I don't know what particular formulas you would like to apply to the data (see clarifying question above). Based on the column names, it looks as though your data has actually been extracted from a database. Due to the multi-valued nature of the data itself, you should work with the database. The database can do the work you need done, and still export a spreadsheet to use with the presumably more familiar Excel tools.
– Haakon Dahl
Jan 16 at 2:36
The data in its original format is normalized, which is a rare gift in "found" sources. I don't know what particular formulas you would like to apply to the data (see clarifying question above). Based on the column names, it looks as though your data has actually been extracted from a database. Due to the multi-valued nature of the data itself, you should work with the database. The database can do the work you need done, and still export a spreadsheet to use with the presumably more familiar Excel tools.
– Haakon Dahl
Jan 16 at 2:36
@HaakonDahl I lilke the "rare gift" words.
– teylyn
Jan 16 at 4:06
@HaakonDahl I lilke the "rare gift" words.
– teylyn
Jan 16 at 4:06
The data will be used to make calculations. It is for a video game known as Eve Online. Each Material will have a "price" pulled from a separate sheet using a API (sort of). The data you are seeing are InputMaterials required to build the Outputs. The types of materials are never going to change, but sadly their are about 200 different material inputs possible.
– user3082908
Jan 19 at 4:44
The data will be used to make calculations. It is for a video game known as Eve Online. Each Material will have a "price" pulled from a separate sheet using a API (sort of). The data you are seeing are InputMaterials required to build the Outputs. The types of materials are never going to change, but sadly their are about 200 different material inputs possible.
– user3082908
Jan 19 at 4:44
add a comment |
1 Answer
1
active
oldest
votes
1
Your desired result duplicates the material ID in the columns. Do you really want that duplication?
Have you considered a pivot table instead, where the material IDs are in the headings? Super easy to do with just a few clicks, and you can reference the data with lookup formulas like Index/Match.
answered Jan 16 at 0:26
teylynteylyn
17.3k22539
This answer produces a different result than that requested, which in this case I think is a Good Thing. Note that in the OP's desired result, different types of material are listed in the same column. This is difficult to do from the original dataset, and will only introduce other difficulties later on, based on the OP's stated desire to expand on the data with formulas. This answer (Pivot Table) is for now the best with two alternatives -- Perfect World:write a query in the original database, or Hell World: Re-create an RDBMS using formulas in Excel.
– Haakon Dahl
Jan 16 at 2:27
The idea of a pivot table being used would make things easier and cleaner, as above mentioned. And while it could lead to problems in future, I think that be overcame with a few ugly methods.. Going to go test that now
– user3082908
Jan 19 at 4:46
add a comment |
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
});
}
});
draft saved
draft discarded
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%2f1394729%2fcombining-data-from-rows-into-columns-excel-2016%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
1
Your desired result duplicates the material ID in the columns. Do you really want that duplication?
Have you considered a pivot table instead, where the material IDs are in the headings? Super easy to do with just a few clicks, and you can reference the data with lookup formulas like Index/Match.
answered Jan 16 at 0:26
teylynteylyn
17.3k22539
This answer produces a different result than that requested, which in this case I think is a Good Thing. Note that in the OP's desired result, different types of material are listed in the same column. This is difficult to do from the original dataset, and will only introduce other difficulties later on, based on the OP's stated desire to expand on the data with formulas. This answer (Pivot Table) is for now the best with two alternatives -- Perfect World:write a query in the original database, or Hell World: Re-create an RDBMS using formulas in Excel.
– Haakon Dahl
Jan 16 at 2:27
The idea of a pivot table being used would make things easier and cleaner, as above mentioned. And while it could lead to problems in future, I think that be overcame with a few ugly methods.. Going to go test that now
– user3082908
Jan 19 at 4:46
add a comment |
1
Your desired result duplicates the material ID in the columns. Do you really want that duplication?
Have you considered a pivot table instead, where the material IDs are in the headings? Super easy to do with just a few clicks, and you can reference the data with lookup formulas like Index/Match.
answered Jan 16 at 0:26
teylynteylyn
17.3k22539
This answer produces a different result than that requested, which in this case I think is a Good Thing. Note that in the OP's desired result, different types of material are listed in the same column. This is difficult to do from the original dataset, and will only introduce other difficulties later on, based on the OP's stated desire to expand on the data with formulas. This answer (Pivot Table) is for now the best with two alternatives -- Perfect World:write a query in the original database, or Hell World: Re-create an RDBMS using formulas in Excel.
– Haakon Dahl
Jan 16 at 2:27
The idea of a pivot table being used would make things easier and cleaner, as above mentioned. And while it could lead to problems in future, I think that be overcame with a few ugly methods.. Going to go test that now
– user3082908
Jan 19 at 4:46
add a comment |
1
1
1
Your desired result duplicates the material ID in the columns. Do you really want that duplication?
Have you considered a pivot table instead, where the material IDs are in the headings? Super easy to do with just a few clicks, and you can reference the data with lookup formulas like Index/Match.
answered Jan 16 at 0:26
teylynteylyn
17.3k22539
Your desired result duplicates the material ID in the columns. Do you really want that duplication?
Have you considered a pivot table instead, where the material IDs are in the headings? Super easy to do with just a few clicks, and you can reference the data with lookup formulas like Index/Match.
answered Jan 16 at 0:26
teylynteylyn
17.3k22539
answered Jan 16 at 0:26
teylynteylyn
17.3k22539
answered Jan 16 at 0:26
teylynteylyn
17.3k22539
answered Jan 16 at 0:26
teylynteylyn
17.3k22539
17.3k22539
This answer produces a different result than that requested, which in this case I think is a Good Thing. Note that in the OP's desired result, different types of material are listed in the same column. This is difficult to do from the original dataset, and will only introduce other difficulties later on, based on the OP's stated desire to expand on the data with formulas. This answer (Pivot Table) is for now the best with two alternatives -- Perfect World:write a query in the original database, or Hell World: Re-create an RDBMS using formulas in Excel.
– Haakon Dahl
Jan 16 at 2:27
The idea of a pivot table being used would make things easier and cleaner, as above mentioned. And while it could lead to problems in future, I think that be overcame with a few ugly methods.. Going to go test that now
– user3082908
Jan 19 at 4:46
add a comment |
This answer produces a different result than that requested, which in this case I think is a Good Thing. Note that in the OP's desired result, different types of material are listed in the same column. This is difficult to do from the original dataset, and will only introduce other difficulties later on, based on the OP's stated desire to expand on the data with formulas. This answer (Pivot Table) is for now the best with two alternatives -- Perfect World:write a query in the original database, or Hell World: Re-create an RDBMS using formulas in Excel.
– Haakon Dahl
Jan 16 at 2:27
The idea of a pivot table being used would make things easier and cleaner, as above mentioned. And while it could lead to problems in future, I think that be overcame with a few ugly methods.. Going to go test that now
– user3082908
Jan 19 at 4:46
This answer produces a different result than that requested, which in this case I think is a Good Thing. Note that in the OP's desired result, different types of material are listed in the same column. This is difficult to do from the original dataset, and will only introduce other difficulties later on, based on the OP's stated desire to expand on the data with formulas. This answer (Pivot Table) is for now the best with two alternatives -- Perfect World:write a query in the original database, or Hell World: Re-create an RDBMS using formulas in Excel.
– Haakon Dahl
Jan 16 at 2:27
This answer produces a different result than that requested, which in this case I think is a Good Thing. Note that in the OP's desired result, different types of material are listed in the same column. This is difficult to do from the original dataset, and will only introduce other difficulties later on, based on the OP's stated desire to expand on the data with formulas. This answer (Pivot Table) is for now the best with two alternatives -- Perfect World:write a query in the original database, or Hell World: Re-create an RDBMS using formulas in Excel.
– Haakon Dahl
Jan 16 at 2:27
The idea of a pivot table being used would make things easier and cleaner, as above mentioned. And while it could lead to problems in future, I think that be overcame with a few ugly methods.. Going to go test that now
– user3082908
Jan 19 at 4:46
The idea of a pivot table being used would make things easier and cleaner, as above mentioned. And while it could lead to problems in future, I think that be overcame with a few ugly methods.. Going to go test that now
– user3082908
Jan 19 at 4:46
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f1394729%2fcombining-data-from-rows-into-columns-excel-2016%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
Question Clarification: What is the end goal? You mention wanting to expand on the data through formulas. What will those formulas do? I think you're running into trouble pursuing this particular data structure. Perhaps you need it in this format to be parsed by some external program, but it sounds like you want to do further analysis in Excel, and this structure will cause heartache. -- Are there more than one Activity Type? Will there ever be? -- Are the material types guaranteed to be only a few? Will that ever change? -- I say "ever" on purpose.
– Haakon Dahl
Jan 16 at 2:32
1
The data in its original format is normalized, which is a rare gift in "found" sources. I don't know what particular formulas you would like to apply to the data (see clarifying question above). Based on the column names, it looks as though your data has actually been extracted from a database. Due to the multi-valued nature of the data itself, you should work with the database. The database can do the work you need done, and still export a spreadsheet to use with the presumably more familiar Excel tools.
– Haakon Dahl
Jan 16 at 2:36
@HaakonDahl I lilke the "rare gift" words.
– teylyn
Jan 16 at 4:06
The data will be used to make calculations. It is for a video game known as Eve Online. Each Material will have a "price" pulled from a separate sheet using a API (sort of). The data you are seeing are InputMaterials required to build the Outputs. The types of materials are never going to change, but sadly their are about 200 different material inputs possible.
– user3082908
Jan 19 at 4:44