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]

 

[hiker95]

Calltech,

I received the same message with your latest workbook.

Here is a macro solution for you to consider, based on your original posted screenshots, that uses three arrays in memory, and, will adjust to the varying number of raw data rows in worksheet List B, and, to the varying number of raw data rows, and, columns in worksheet List A.

Sample worksheets:

Excel 2007
	A	B
1	d(4)	d(4) Yeah!
2	e(5)	e(5) No!
3	f(6)	f(6) Ok
4
List B

Excel 2007
	A	B	C	D	E	F	G	H
1	The bucket e(5) was d(4) full	other content	other content	other content	other content
2	The broom stood	other content	other content	other content	The broom f(6)
3	The call came d(4)	other content	other content	other content	other content
4
List A

And, after the macro:

Excel 2007
	A	B	C	D	E	F	G	H
1	d(4) Yeah! e(5) No!	The bucket e(5) was d(4) full	other content	other content	other content	other content
2	f(6) Ok	The broom stood	other content	other content	other content	The broom f(6)
3	d(4) Yeah!	The call came d(4)	other content	other content	other content	other content
4
List A

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub CompareListsV4()
' hiker95, 12/06/2015, ME759006
Dim b As Variant, i As Long
Dim a As Variant, o As Variant
Dim r As Long, lr As Long, c As Long, lc As Long, t As String
Application.ScreenUpdating = False
With Sheets("List B")
  b = .Range("A1:B" & .Range("A" & Rows.Count).End(xlUp).Row).Value
End With
With Sheets("List A")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  ReDim o(1 To lr, 1 To 1)
  For r = 1 To lr
    t = ""
    For c = 1 To lc Step 1
      For i = LBound(b, 1) To UBound(b, 1)
        If Not a(r, c) = vbEmpty Then
          If InStr(a(r, c), b(i, 1)) Then
            If t = "" Then
              t = b(i, 2)
            Else
              t = t & " " & b(i, 2)
            End If
          End If
        End If
      Next i
    Next c
    o(r, 1) = t
  Next r
  .Columns(1).Insert
  .Cells(1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Columns(1).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the CompareListsV4 macro.

 

[Calltech]

hiker95,

Yes! That's amazing! You helped a lot! This is what I was looking for. I forgot to say that there may be repetitions of the columns. So many times I receive the earned value as many times in a row. However, I found it a macro in the forum, which helped disappear duplication. I combinated the two codes.





I Share to see if anyone else needs disappear duplications in Column A cells:

Sub Combine_CompareListsV4_and_remDup()
CompareListsV4
remDup
End Sub
Sub CompareListsV4()
' hiker95, 12/06/2015, ME759006 <- Thank You!
Dim b As Variant, i As Long
Dim a As Variant, o As Variant
Dim r As Long, lr As Long, c As Long, lc As Long, t As String
Application.ScreenUpdating = False
With Sheets("List B")
  b = .Range("A1:B" & .Range("A" & Rows.Count).End(xlUp).Row).Value
End With
With Sheets("List A")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  ReDim o(1 To lr, 1 To 1)
  For r = 1 To lr
    t = ""
    For c = 1 To lc Step 1
      For i = LBound(b, 1) To UBound(b, 1)
        If Not a(r, c) = vbEmpty Then
          If InStr(a(r, c), b(i, 1)) Then
            If t = "" Then
              t = b(i, 2)
            Else
              t = t & " " & b(i, 2)
            End If
          End If
        End If
      Next i
    Next c
    o(r, 1) = t
  Next r
  .Columns(1).Insert
  .Cells(1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Columns(1).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Sub remDup()
'Richard Schollar, 08/06/2009, ME407809 <- Thank You!
Dim dic As Object, cell As Range, temp As Variant
Dim i As Long
Set dic = CreateObject("scripting.dictionary")
With dic
    For Each cell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        .RemoveAll
        If Len(cell.Value) > 0 Then
            temp = Split(cell.Value, " ")
            For i = 0 To UBound(temp)
                If Not .Exists(temp(i)) Then .Add temp(i), temp(i)
            Next i
            cell.Value = Join(.Keys, " ")
        End If
    Next cell
    Columns("A:A").EntireColumn.AutoFit
End With
        
End Sub

(Note to users: Only click the "Combine_CompareListsV4_andremDup macro". This run both of them together.)

Thank you again!
Have a nice day,
Calltech

 

[hiker95]

Calltech,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[Calltech]

Hello again hiker95!

The code what you wrote still great. I had to make some changes to my excel. And I should need to change the LIST B.
Can you give me some help, how to change LIST B to:

Add +4 columns like A column.
https://photouploads.com/image/yK4

So, if find A,B,C,D or E data from LIST B in LIST A, change it to F.

Thank you in advance!
Have a nice day!

 

[hiker95]

Hello again hiker95!

The code what you wrote still great. I had to make some changes to my excel. And I should need to change the LIST B.
Can you give me some help, how to change LIST B to:

Add +4 columns like A column.
https://photouploads.com/image/yK4

So, if find A,B,C,D or E data from LIST B in LIST A, change it to F.

Thank you in advance!
Have a nice day!

Calltech,

I can not use the image.


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


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

https://dropbox.com

 

[Dharmesh Thakkar]

Hi Calltech,

Many thanks work really good but would be grateful, if you could change code as below

After macro , text need to be updated in column B instead of inserting column and update text/copy from Sheet List B to Sheet List A (wherever data in any cell of List B column B)


work

 

[Calltech]

Hi Calltech,

Many thanks work really good but would be grateful, if you could change code as below

After macro , text need to be updated in column B instead of inserting column and update text/copy from Sheet List B to Sheet List A (wherever data in any cell of List B column B)


work

Dear Dharmesh Thakkar!

Are you think to a simple "Find and Replace" function? I don't know this macro. This is not my merit. It was made by hiker95.
I can do that in the LIST A the complete Column A will be add instead of Column B. If that's what you mean, I'm very happy to do it.

 

[Calltech]

Dear hiker95!

I uploaded two workbook. This original is, what you made a few months ago (The macro is added to this, and working "Combine_CompareListsV5_and_remDup"). And this is how should need to look.

The changes is that added +4 rows to the LIST B.
Now if somewhere in LIST A find data, which is in LIST B -> A-E Columns, add Column F to LIST A first row like in original.

It would be great, if you can fix it to me. Dense thanks in advance for your trouble. Have a nice day.

 

[hiker95]

Dear hiker95!

I uploaded two workbook. This original is, what you made a few months ago (The macro is added to this, and working "Combine_CompareListsV5_and_remDup"). And this is how should need to look.

The changes is that added +4 rows to the LIST B.
Now if somewhere in LIST A find data, which is in LIST B -> A-E Columns, add Column F to LIST A first row like in original.

It would be great, if you can fix it to me. Dense thanks in advance for your trouble. Have a nice day.

Calltech,

When I downloaded/opened your workbooks, I received the following:

Privacy warning: This document contains macros, ActiveX controls, XML expansion pack information, or Web components. These may include personal information that cannot be removed by the Document Inspector.

I have never seen this before, and, I do not understand it.

I will not be able to assist you any further.

 

[Calltech]

I hope fixed the warning error. Here is the new links: original , should need to look.

Thank You the feedback.