Compare two lists and paste content if match is found

[jeffsdan]

I need to search the content from one list (List A, Column A) for the mention of items from another list (List B, Column A), and if any matches are found, I need to publish in Column B of List A the data from Column B of the associated matching item of List B. Can anyone help?
Tables below to help clarify what I'm asking.



List A (The list being searched)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]List A[/TD]
[TD]Column B (blank)[/TD]
[/TR]
[TR]
[TD]The bucket e(5) was full[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]The broom stood[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]The call came d(4)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


List B (The reference list - e.g., "does list A contain any of these items")

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]List B[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]d(4)[/TD]
[TD]d(4) Yeah[/TD]
[/TR]
[TR]
[TD]e(5)[/TD]
[TD]e(5) No![/TD]
[/TR]
[TR]
[TD]f(6)[/TD]
[TD]f(6) Ok[/TD]
[/TR]
</tbody>[/TABLE]


What I need the final product to look like:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]List A[/TD]
[TD]Added text from List B, Column B[/TD]
[/TR]
[TR]
[TD]The bucket e(5) was full[/TD]
[TD]e(5) No![/TD]
[/TR]
[TR]
[TD]The broom stood[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]The call came d(4)[/TD]
[TD]d(4) Yeah[/TD]
[/TR]
</tbody>[/TABLE]

 

[Calltech]

hiker95!

This CompareListsV3 macro is amazing! Thank you for your efforts! I would like to ask for your help. I need to search the content from any number of list (in "List A" Sheet) and put the result before A column in "List A" Sheet.


Here is my example:

Excel 2007

	A	B

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]d(4)[/TD]
[TD="bgcolor: #FFFFFF"]d(4) Yeah![/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]e(5)[/TD]
[TD="bgcolor: #FFFFFF"]e(5) No![/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]f(6)[/TD]
[TD="bgcolor: #FFFFFF"]f(6) Ok[/TD]

[TD="align: center"]4[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

</tbody>

List B
​

Excel 2007

	A	B	C	D	E	F	G	H

<tbody>
[TD="align: center"]1[/TD]
[TD="align: left"]The bucket e(5) was d(4) full[/TD]
[TD="align: left"]other content[/TD]
[TD="align: left"]other content[/TD]
[TD="align: left"]other content[/TD]
[TD="align: left"]other content[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: center"]2[/TD]
[TD="align: left"]The broom stood[/TD]
[TD="bgcolor: #FFFFFF, align: left"]other content[/TD]
[TD="bgcolor: #FFFFFF, align: left"]other content[/TD]
[TD="bgcolor: #FFFFFF, align: left"]other content[/TD]
[TD="bgcolor: #FFFFFF, align: left"] The broom f(6)[/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]

[TD="align: center"]3[/TD]
[TD="align: left"]The call came d(4)[/TD]
[TD="bgcolor: #FFFFFF"]other content[/TD]
[TD="bgcolor: #FFFFFF, align: left"]other content[/TD]
[TD="bgcolor: #FFFFFF, align: left"]other content[/TD]
[TD="bgcolor: #FFFFFF, align: left"]other content[/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]

[TD="align: center"]4[/TD]
[TD="align: left"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]

</tbody>

List A​

Excel 2007

	A	B	C	D	E	F	G	H

<tbody>
[TD="align: center"]1[/TD]
[TD="align: left"]d(4) Yeah! e(5) No![/TD]
[TD="align: left"]The bucket e(5) was d(4) full[/TD]
[TD="align: left"]other content[/TD]
[TD="align: left"]other content[/TD]
[TD="align: left"]other content[/TD]
[TD="align: left"]other content[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: center"]2[/TD]
[TD="align: left"]f(6) Ok[/TD]
[TD="bgcolor: #FFFFFF, align: left"]The broom stood[/TD]
[TD="bgcolor: #FFFFFF, align: left"]other content[/TD]
[TD="bgcolor: #FFFFFF, align: left"]other content[/TD]
[TD="bgcolor: #FFFFFF, align: left"]other content[/TD]
[TD="bgcolor: #FFFFFF, align: left"]The broom f(6)[/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]

[TD="align: center"]3[/TD]
[TD="align: left"]d(4) Yeah![/TD]
[TD="bgcolor: #FFFFFF"]The call came d(4)[/TD]
[TD="bgcolor: #FFFFFF, align: left"]other content[/TD]
[TD="bgcolor: #FFFFFF, align: left"]other content[/TD]
[TD="bgcolor: #FFFFFF, align: left"]other content[/TD]
[TD="bgcolor: #FFFFFF, align: left"]other content[/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]

[TD="align: center"]4[/TD]
[TD="align: left"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]

</tbody>

List A​

Thank you in advance for your help!

 

[Calltech]

Or 20 columns, if any isn't possible. Thank you.

 

[hiker95]

Calltech,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

I am confused by your screenshots, and, descriptions, in your last two replies.

In order to continue, and, so that I can get it right on the first try, I would like to see your actual raw data workbook/worksheets, and, what the results (manually formatted by you) should look like.

You can post your actual raw data workbook/worksheets to the following free site (sensitive data changed), and provide us with a link to your workbook:

https://dropbox.com

 

[Calltech]

I use Microsoft Office Professional Plus 2013 64bit and Windows 8 Enterprise in PC.

Here is my example:
<a href=http://www.filedropper.com/comparetwolistsandpastecontentifmatchisfound><img src=http://www.filedropper.com/download_button.png width=127 height=145 border=0/></a><br /><div style=font-size:9px;font-family:Arial, Helvetica, sans-serif;width:127px;font-color:#44a854;> <a href=http://www.filedropper.com >file storage</a></div>

Thank you in advance for your help!

 

[hiker95]

Calltech,

Your link is asking me to Sign In, and/or, create an account?????

You can post your workbook/worksheets to the following free site (sensitive data changed), and provide us with a link to your workbook:

https://dropbox.com

 

[Dharmesh Thakkar]

And how about my solution?

Dear Hiker95

Great.
Its useful to me as well. But problem is, if data in column B already exist , then macro is it adding the text in earlier data instead of overwriting and giving some alert (e.g. replace and change colour to RED)

many thanks in advance

 

[hiker95]

Dear Hiker95

Great.
Its useful to me as well. But problem is, if data in column B already exist , then macro is it adding the text in earlier data instead of overwriting and giving some alert (e.g. replace and change colour to RED)

many thanks in advance

Dharmesh Thakkar,

This thread is becoming very difficult to follow, with everyone having different raw data, and, worksheets.

Please start a new thread for your question and be sure to give it a search friendly title that accurately describes your need.

In your New Thread include:

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

To start off, and, so that we can get it right on the first try:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) for the results that you are looking for?

To post a small screen shot (NOT a graphic, or, picture, or, PNG/IMG file, or, flat text) try one of the following:

There are several methods. Here are 3 for you to investigate.

Excel Jeanie
Download

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

MrExcel HTML Maker
If you do not know how to install and how to use HTML Mr.Excel Maker
https://www.youtube.com/watch?v=JycvgGppxt0&feature=youtu.be

To test the above:
Test Here

Or, you can post your workbook/worksheets to the following free site (sensitive data changed), and provide us with a link to your workbook:

https://dropbox.com

Then send me a Private Message, with a link to your New Thread, and, I will have a look.

 

[Calltech]

Here is the link:

https://www.dropbox.com/s/spgddo0dx...and paste content if match is found.xlsx?dl=0

(Microsoft Office Professional Plus 2013 64bit and Windows 8 Enterprise in PC)

 

[hiker95]

Calltech,

When I attempted to download/open your workbook in your reply #18, I received the following message:

Excel found unreadable content in 'Compare two lists and paste content if match is found.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.

Please try again with a new copy of your workbook.

 

[Calltech]

hiker95,

I apologize for the wrong uploads. I really hope, that is the final: Link

Thank you.