Thread: Merge Data from rows which have a column that matches.

Hello, I would like to create an Excel macro that compares column B on each row of a master worksheet with Column B on a temp worksheet and when a match is found, the remaining columns C-D be updated on the Master worksheet with the data from the matching row on the temp work sheet. If no match is found I would like the entire row from the temp worksheet added to the master worksheet.

For example:

Master

HTML Code:

         A        B          C                D
1      id        email      number     description
2    501      a@xyz.com     12345        sometext1
3    502      b@xyz.com     10101        sometext2
4    503      c@xyz.com     98765        sometext3

Temp

HTML Code:

         A        B          C                D
1      id        email      number     description
2             g@xyz.com     98989        sometext4
3             f@xyz.com     55555        sometext5
4             b@xyz.com     22222        sometext6

Resulting Worksheet:

HTML Code:

         A        B          C                D
1      id        email      number     description
2    501      a@xyz.com     12345        sometext1
3    502      b@xyz.com     22222        sometext6
4    503      c@xyz.com     98765        sometext3
5             g@xyz.com      98989        sometext4
6             f@xyz.com       55555        sometext5

As you can see, the information for b@xyz.com was updated and g@xyz.com & f@xyz.com were added.

Preferable I would like the the Master worksheet to be updated directly, but alternatively, the results can be put in a new worksheet. The order of the rows of data does not matter.

Thank you in advance for any help you can provide.

Kurt

This macro will do what you ask, make sure the sheets are named Master and Temp:

Code:

removed...see below for latest version

This macro goes in a regular module (Insert > Module), not in a sheet module.

Thanks, JBeaucaire. The first test ran successfully, but the second did not.

My master was:

HTML Code:

id	name		location	age
501	a@xyz.com	Colorado	34
502	b@xyz.com	Broomfield	37
503	c@xyz.com	Broomfield	10
504	d@xyz.com	Broomfield	8

and my temp was:

HTML Code:

id	name		location	age
	d@xyz.com	Broomfield	8
	a@xyz.com	Colorado	34
	e@xyz.com	Aurora		5
	f@xyz.com	Aurora		4
	c@xyz.com	Colorado	11

The resulting Master worksheet was:

HTML Code:

id	name		location	age
501	a@xyz.com	Aurora		4
502	b@xyz.com	Broomfield	37
503	c@xyz.com	Colorado	11
504	d@xyz.com	Broomfield	8

The results I expected are:

HTML Code:

id	name		location	age
501	a@xyz.com	Colorado	34
502	b@xyz.com	Broomfield	37
503	c@xyz.com	Colorado	11
504	d@xyz.com	Broomfield	8
	e@xyz.com	Aurora		5
	f@xyz.com	Aurora		4

It updated "a@abc.com" with the information for "f@abc.com" and didn't add e@abc.com at all. It did update c@xyz.com successfully. Again, row order doesn't matter.

Thanks again for your help.

Let's see the workbook. Click GO ADVANCED and use the paperclip icon to post up your workbook.

My bad, I forgot to reset the dRow after it was used each time.

Code:

Option Explicit

Sub UpdateMaster()
Dim dRow As Long, RNG As Range, cell As Range
Application.ScreenUpdating = False

Set RNG = Sheets("Temp").Range("B2:B" & Rows.Count).SpecialCells(xlCellTypeConstants)
On Error Resume Next

For Each cell In RNG
    With Sheets("Master")
        dRow = .Range("B:B").Find(cell, After:=.[B1], LookIn:=xlValues, LookAt:=xlPart, _
            SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
        Debug.Print cell.Address
        If dRow > 0 Then
            cell.Offset(0, 1).Resize(1, 2).Copy
            .Range("C" & dRow).PasteSpecial xlPasteValues
            dRow = 0
        Else
            cell.Resize(1, 3).Copy
            .Range("B" & .Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        End If
    End With
Next cell

Set RNG = Nothing
Application.ScreenUpdating = True
End Sub

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

This will create a new sheet with today's date and put the new list on that sheet. This work?

Code:

removed...see below

Apologies, I misread your updated request, try this:

Code:

code removed...see below

Hmm, try this:

Code:

Option Explicit

Sub UpdateMaster()
Dim dRow As Long, RNG As Range, cell As Range
Dim wsNew As Worksheet
Application.ScreenUpdating = False

Set RNG = Sheets("Temp").Range("B2:B" & Rows.Count).SpecialCells(xlCellTypeConstants)

If Not Evaluate("ISREF('New-" & Format(Date, "MMDDYY") & "'!A1)") Then _
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "New-" & Format(Date, "MMDDYY")
    
Set wsNew = Sheets("New-" & Format(Date, "MMDDYY"))
wsNew.Cells.Clear
Sheets("Master").Rows(1).Copy wsNew.Range("A1")

On Error Resume Next

For Each cell In RNG
    dRow = Sheets("Master").Cells.Find(cell, After:=Sheets("Master").[B1], LookIn:=xlValues, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
    If dRow > 0 Then
        cell.Resize(1, 3).Copy
        wsNew.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        dRow = 0
    Else
        cell.Resize(1, 3).Copy
        wsNew.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    End If
Next cell

Set RNG = Nothing
Application.ScreenUpdating = True
End Sub