How can I combine 2 lists of data in EXCEL

up vote
0
down vote

favorite

I have 2 lists of parameter data for multiple different machines. I am trying to combine the data into a single list, but not all of the parameters are present for all machines. Is this possible in EXCEL?

List 1

List 1

List 2

List 2

Combined List:

Combined List

asked Nov 27 at 0:03

clayton33

1235

Can 'Machine 3' also exist as one more column in List 2, despite it's already present in List 1? i.e. can a heading co-exist in both the lists at the same time?
– pat2015
Nov 27 at 2:17

Power Query also is a good choose.
– Lee
Nov 27 at 9:53

add a comment |

up vote
0
down vote

favorite

List 1

List 1

List 2

List 2

Combined List:

Combined List

asked Nov 27 at 0:03

clayton33

1235

Can 'Machine 3' also exist as one more column in List 2, despite it's already present in List 1? i.e. can a heading co-exist in both the lists at the same time?
– pat2015
Nov 27 at 2:17

Power Query also is a good choose.
– Lee
Nov 27 at 9:53

add a comment |

up vote
0
down vote

favorite

List 1

List 1

List 2

List 2

Combined List:

Combined List

asked Nov 27 at 0:03

clayton33

1235

List 1

List 1

List 2

List 2

Combined List:

Combined List

microsoft-excel

asked Nov 27 at 0:03

clayton33

1235

asked Nov 27 at 0:03

clayton33

1235

asked Nov 27 at 0:03

clayton33

1235

asked Nov 27 at 0:03

clayton33

1235

asked Nov 27 at 0:03

clayton33

1235

Can 'Machine 3' also exist as one more column in List 2, despite it's already present in List 1? i.e. can a heading co-exist in both the lists at the same time?
– pat2015
Nov 27 at 2:17

Power Query also is a good choose.
– Lee
Nov 27 at 9:53

add a comment |

Can 'Machine 3' also exist as one more column in List 2, despite it's already present in List 1? i.e. can a heading co-exist in both the lists at the same time?
– pat2015
Nov 27 at 2:17

Power Query also is a good choose.
– Lee
Nov 27 at 9:53

Can 'Machine 3' also exist as one more column in List 2, despite it's already present in List 1? i.e. can a heading co-exist in both the lists at the same time?
– pat2015
Nov 27 at 2:17

Power Query also is a good choose.
– Lee
Nov 27 at 9:53

add a comment |

2 Answers
2

active

oldest

votes

up vote
3
down vote

accepted

Assumptions.

You have only these two tables to manage

Little bit of manual table creation is suggested

Headings are unique between two lists. i.e. say Machine 4 can exist only either in List 1 or 2 but not both at the same time.

In this example List 1 is in cells C2:F8 and List 2 is at C10:D15

Now first manually create a structure of your table elsewhere. In this example it's created at H2 and thereafter.

Copy Col C (See the second snapshot) Put it in H, remove duplicates and sort it on Col H. Similarly Copy Headers from both Lists one next to other. So you have a structure in place like this.

enter image description here

Now in I3 put the following formula and drag it down below and across along the length and width of the new table or list.

=IFERROR(VLOOKUP($H3,$C$11:$D$15,MATCH(I$2,$C$10:$D$10,0),FALSE),IFERROR(VLOOKUP($H3,$C$3:$F$8,MATCH(I$2,$C$2:$F$2,0),FALSE),""))

enter image description here

answered Nov 27 at 2:23

pat2015

3,1992721

add a comment |

up vote
2
down vote

You can do this with an Index/Match.

Lets say the list in the first picture begins on A1, and list for the second picture begins on A9, then the formula that you would need to enter into cell E2 would be

=IFERROR(INDEX($B$10:$B$14,MATCH($A2,$A$10:$A$14,0)),"")

Here is a picture of the sample you provided

edited Nov 27 at 3:37

Scott

15.5k113789

answered Nov 27 at 0:17

Thomas Hayes

295

1

To lock cells within Excel you add $ signs at certain places. This allows you to apply formulas to large amounts of cells in an easier manner. For example, $A1 locks the columns, A$1 locks the rows, and $A$1 locks the columns and the rows.
– Thomas Hayes
Nov 27 at 0:29

Thanks for showing a real-life demonstration. I'll give you a +1, even though you missed the bit about creating a combined (merged) list, containing ''Par 3'' and ''Par 51''.
– Scott
Nov 27 at 3:37

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',
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');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1378593%2fhow-can-i-combine-2-lists-of-data-in-excel%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

up vote
3
down vote

accepted

Assumptions.

You have only these two tables to manage

Little bit of manual table creation is suggested

Headings are unique between two lists. i.e. say Machine 4 can exist only either in List 1 or 2 but not both at the same time.

In this example List 1 is in cells C2:F8 and List 2 is at C10:D15

Now first manually create a structure of your table elsewhere. In this example it's created at H2 and thereafter.

Copy Col C (See the second snapshot) Put it in H, remove duplicates and sort it on Col H. Similarly Copy Headers from both Lists one next to other. So you have a structure in place like this.

enter image description here

Now in I3 put the following formula and drag it down below and across along the length and width of the new table or list.

=IFERROR(VLOOKUP($H3,$C$11:$D$15,MATCH(I$2,$C$10:$D$10,0),FALSE),IFERROR(VLOOKUP($H3,$C$3:$F$8,MATCH(I$2,$C$2:$F$2,0),FALSE),""))

enter image description here

answered Nov 27 at 2:23

pat2015

3,1992721

add a comment |

up vote
3
down vote

accepted

Assumptions.

You have only these two tables to manage

Little bit of manual table creation is suggested

Headings are unique between two lists. i.e. say Machine 4 can exist only either in List 1 or 2 but not both at the same time.

In this example List 1 is in cells C2:F8 and List 2 is at C10:D15

Now first manually create a structure of your table elsewhere. In this example it's created at H2 and thereafter.

Copy Col C (See the second snapshot) Put it in H, remove duplicates and sort it on Col H. Similarly Copy Headers from both Lists one next to other. So you have a structure in place like this.

enter image description here

Now in I3 put the following formula and drag it down below and across along the length and width of the new table or list.

=IFERROR(VLOOKUP($H3,$C$11:$D$15,MATCH(I$2,$C$10:$D$10,0),FALSE),IFERROR(VLOOKUP($H3,$C$3:$F$8,MATCH(I$2,$C$2:$F$2,0),FALSE),""))

enter image description here

answered Nov 27 at 2:23

pat2015

3,1992721

add a comment |

up vote
3
down vote

accepted

Assumptions.

You have only these two tables to manage

Little bit of manual table creation is suggested

Headings are unique between two lists. i.e. say Machine 4 can exist only either in List 1 or 2 but not both at the same time.

In this example List 1 is in cells C2:F8 and List 2 is at C10:D15

Now first manually create a structure of your table elsewhere. In this example it's created at H2 and thereafter.

Copy Col C (See the second snapshot) Put it in H, remove duplicates and sort it on Col H. Similarly Copy Headers from both Lists one next to other. So you have a structure in place like this.

enter image description here

Now in I3 put the following formula and drag it down below and across along the length and width of the new table or list.

=IFERROR(VLOOKUP($H3,$C$11:$D$15,MATCH(I$2,$C$10:$D$10,0),FALSE),IFERROR(VLOOKUP($H3,$C$3:$F$8,MATCH(I$2,$C$2:$F$2,0),FALSE),""))

enter image description here

answered Nov 27 at 2:23

pat2015

3,1992721

Assumptions.

You have only these two tables to manage

Little bit of manual table creation is suggested

Headings are unique between two lists. i.e. say Machine 4 can exist only either in List 1 or 2 but not both at the same time.

In this example List 1 is in cells C2:F8 and List 2 is at C10:D15

Now first manually create a structure of your table elsewhere. In this example it's created at H2 and thereafter.

Copy Col C (See the second snapshot) Put it in H, remove duplicates and sort it on Col H. Similarly Copy Headers from both Lists one next to other. So you have a structure in place like this.

enter image description here

Now in I3 put the following formula and drag it down below and across along the length and width of the new table or list.

=IFERROR(VLOOKUP($H3,$C$11:$D$15,MATCH(I$2,$C$10:$D$10,0),FALSE),IFERROR(VLOOKUP($H3,$C$3:$F$8,MATCH(I$2,$C$2:$F$2,0),FALSE),""))

enter image description here

answered Nov 27 at 2:23

pat2015

3,1992721

answered Nov 27 at 2:23

pat2015

3,1992721

answered Nov 27 at 2:23

pat2015

3,1992721

answered Nov 27 at 2:23

pat2015

3,1992721

add a comment |

up vote
2
down vote

You can do this with an Index/Match.

Lets say the list in the first picture begins on A1, and list for the second picture begins on A9, then the formula that you would need to enter into cell E2 would be

=IFERROR(INDEX($B$10:$B$14,MATCH($A2,$A$10:$A$14,0)),"")

Here is a picture of the sample you provided

edited Nov 27 at 3:37

Scott

15.5k113789

answered Nov 27 at 0:17

Thomas Hayes

295

1

To lock cells within Excel you add $ signs at certain places. This allows you to apply formulas to large amounts of cells in an easier manner. For example, $A1 locks the columns, A$1 locks the rows, and $A$1 locks the columns and the rows.
– Thomas Hayes
Nov 27 at 0:29

Thanks for showing a real-life demonstration. I'll give you a +1, even though you missed the bit about creating a combined (merged) list, containing ''Par 3'' and ''Par 51''.
– Scott
Nov 27 at 3:37

add a comment |

up vote
2
down vote

You can do this with an Index/Match.

Lets say the list in the first picture begins on A1, and list for the second picture begins on A9, then the formula that you would need to enter into cell E2 would be

=IFERROR(INDEX($B$10:$B$14,MATCH($A2,$A$10:$A$14,0)),"")

Here is a picture of the sample you provided

edited Nov 27 at 3:37

Scott

15.5k113789

answered Nov 27 at 0:17

Thomas Hayes

295

1

To lock cells within Excel you add $ signs at certain places. This allows you to apply formulas to large amounts of cells in an easier manner. For example, $A1 locks the columns, A$1 locks the rows, and $A$1 locks the columns and the rows.
– Thomas Hayes
Nov 27 at 0:29

Thanks for showing a real-life demonstration. I'll give you a +1, even though you missed the bit about creating a combined (merged) list, containing ''Par 3'' and ''Par 51''.
– Scott
Nov 27 at 3:37

add a comment |

up vote
2
down vote

You can do this with an Index/Match.

Lets say the list in the first picture begins on A1, and list for the second picture begins on A9, then the formula that you would need to enter into cell E2 would be

=IFERROR(INDEX($B$10:$B$14,MATCH($A2,$A$10:$A$14,0)),"")

Here is a picture of the sample you provided

edited Nov 27 at 3:37

Scott

15.5k113789

answered Nov 27 at 0:17

Thomas Hayes

295

You can do this with an Index/Match.

Lets say the list in the first picture begins on A1, and list for the second picture begins on A9, then the formula that you would need to enter into cell E2 would be

=IFERROR(INDEX($B$10:$B$14,MATCH($A2,$A$10:$A$14,0)),"")

Here is a picture of the sample you provided

edited Nov 27 at 3:37

Scott

15.5k113789

answered Nov 27 at 0:17

Thomas Hayes

295

edited Nov 27 at 3:37

Scott

15.5k113789

edited Nov 27 at 3:37

Scott

15.5k113789

edited Nov 27 at 3:37

Scott

15.5k113789

answered Nov 27 at 0:17

Thomas Hayes

295

answered Nov 27 at 0:17

Thomas Hayes

295

answered Nov 27 at 0:17

Thomas Hayes

295

1

To lock cells within Excel you add $ signs at certain places. This allows you to apply formulas to large amounts of cells in an easier manner. For example, $A1 locks the columns, A$1 locks the rows, and $A$1 locks the columns and the rows.
– Thomas Hayes
Nov 27 at 0:29

Thanks for showing a real-life demonstration. I'll give you a +1, even though you missed the bit about creating a combined (merged) list, containing ''Par 3'' and ''Par 51''.
– Scott
Nov 27 at 3:37

add a comment |

1

To lock cells within Excel you add $ signs at certain places. This allows you to apply formulas to large amounts of cells in an easier manner. For example, $A1 locks the columns, A$1 locks the rows, and $A$1 locks the columns and the rows.
– Thomas Hayes
Nov 27 at 0:29

Thanks for showing a real-life demonstration. I'll give you a +1, even though you missed the bit about creating a combined (merged) list, containing ''Par 3'' and ''Par 51''.
– Scott
Nov 27 at 3:37

To lock cells within Excel you add $ signs at certain places. This allows you to apply formulas to large amounts of cells in an easier manner. For example, $A1 locks the columns, A$1 locks the rows, and $A$1 locks the columns and the rows.
– Thomas Hayes
Nov 27 at 0:29

Thanks for showing a real-life demonstration. I'll give you a +1, even though you missed the bit about creating a combined (merged) list, containing ''Par 3'' and ''Par 51''.
– Scott
Nov 27 at 3:37

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.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

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');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Argthtjtr