VBA to match up names on two different sheets and copy data based on name match

coffeeman12

New Member
Joined
Sep 19, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. MacOS
Hard to articulate in a subject line. The examples I've found so far all use Scripting Dictionary, which doesn't work on a Mac. I'm on Mac Excel 16.16.27.

I have two data sheets:
Sheet1 is a list of names (from a particular data set) in column A.
Sheet2 is another list of names (also column A), which should include all the names in Sheet1, and probably more. Sheet2 has email addresses in columns C and D. For some reason this data set includes a "confidential email" and a regular email address. For some names, both will have entries, for other names there will be only one entry.

I'd like Excel to match names from sheet1 to the names in sheet2, and when matched, copy the cells in columns C and D to the same columns in sheet1.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

PeteWright

New Member
Joined
Dec 20, 2020
Messages
23
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
Hi @coffeeman12,

Check out the Excel Workbook I have uploaded to my OneDrive: MrExcel_coffeeman12_datacopy.xlsm
You must download the Excel file to your computer and enable macros, if you trust me. Otherwise wait a second and I will post the code here...
 

PeteWright

New Member
Joined
Dec 20, 2020
Messages
23
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
MrExcel_coffeeman12_datacopy.xlsm
ABCD
1Namestatusconfidetial emailregular email
2John Smith
3Tim Miller
4Michael Cain
5Jennifer Abigail
6Frank Stevens
7Matthew Andrews
8Sarah Brown
Sheet1


MrExcel_coffeeman12_datacopy.xlsm
ABCD
1Namestatusconfidetial emailregular email
2Michael CainMichael.Cain@companyX.comM7C4@confidential.net
3Frank StevensF5S7@confidential.net
4Sarah BrownSarah.Brown@companyX.com
5John SmithJ4S5@confidential.net
6Thomas OwensThomas.Owens@companyX.comT6O5@confidential.net
7Timothy DoyleT7D5@confidential.net
8Tim MillerT3M6@confidential.net
9Veronica HopkinsVeronica.Hopkins@companyX.comV8H7@confidential.net
10Jennifer AbigailJennifer.Abigail@companyX.com
11Matthew AndrewsMatthew.Andrews@companyX.comM7A7@confidential.net
Sheet2


VBA Code:
Private Sub CopyData()
    Dim i, j As Integer
    For i = 2 To 99
        For j = 2 To 99
            If Sheet2.Cells(j, 2).Value <> "checked" Then
                If Sheet1.Cells(i, 1).Value <> "" Then
                    If Sheet1.Cells(i, 1).Value = Sheet2.Cells(j, 1).Value Then
                        Sheet2.Cells(j, 2).Value = "checked"
                        Sheet1.Cells(i, 2).Value = "found"
                        Sheet1.Cells(i, 3).Value = Sheet2.Cells(j, 3).Value
                        Sheet1.Cells(i, 4).Value = Sheet2.Cells(j, 4).Value
                        Exit For
                    Else
                        Sheet1.Cells(i, 2).Value = "N/A"
                    End If
                End If
            End If
        Next j
    Next i
End Sub


To make my sample VBA code work, you need to copy the contents of sheet1 and sheet2 exactly as shown, paste the code into Sheet1 and launch it.
 

coffeeman12

New Member
Joined
Sep 19, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. MacOS

ADVERTISEMENT

This worked, but only for the first 15 lines. Do I need to adjust it for the length of the list?

There are 9368 rows on my Sheet1 list.
 

PeteWright

New Member
Joined
Dec 20, 2020
Messages
23
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
This worked, but only for the first 15 lines. Do I need to adjust it for the length of the list?

There are 9368 rows on my Sheet1 list.
Yes, it was only an example, because I didn't know how many lines / rows of data you have got. So I created a simple table with only 10 lines of data.




Now I need to know a little bit more about your data.
  1. Does your data set have a fixed number of entries? Or is it dynamic?
  2. You need to know how the code works. How deep is your knowledge of VBA and programming?
    What I have done is a nested FOR-LOOP which iterates two times from 2 to 99.
    That means 98 x 98 = 9604 calculation steps.
    The bigger your list, the more calculation steps are needed (obviously)
    Let's say you have 9999 entries, that makes max. 9999 x 9999 = 99 980 001 calculations.

    VBA takes the name in the first line and compares it to ALL entries until a match is found (max 9999).
    Then it takes the name in the second line and also compares it to ALL entries except the one that was already found.
    The math behind this would be:
    1. line: 1 x 9999
    2. line: 1 x 9998
    3. line: 1 x 9997
    and so on...
    That actually makes 9999 + 9998 + 9997 + ... = 49 995 000 calculations

    The problem is that you don't know the difference between data set 1 and data set 2.
    There may be only a few more entries in data set 2 or there may be a lot more.
    That is why I have chosen this coding method
Excel has certain limits: Excel specifications and limits.
1,048,576 rows by 16,384 columns
Now, if you have the maximum possible amount of data, that would be:
1. line: 1 x 1,048,576
2. line: 1 x 1,048,575
3. line: 1 x 1,048,574
and so on...
This would sum up to: 1,048,576 x 1,048,575 x 1,048,574 x ... = 549 756 338 176

I think you get the picture.

Now let us just assume the data does not exceed 9999 lines.
The VBA code would then look like this:

VBA Code:
Private Sub DataCopy()
    Dim i, j As Integer
    For i = 2 To 999
        For j = 2 To 999
            If Sheet2.Cells(j, 2).Value <> "checked" Then
                If Sheet1.Cells(i, 1).Value <> "" Then
                    If Sheet1.Cells(i, 1).Value = Sheet2.Cells(j, 1).Value Then
                        Sheet2.Cells(j, 2).Value = "checked"
                        Sheet1.Cells(i, 2).Value = "found"
                        Sheet1.Cells(i, 3).Value = Sheet2.Cells(j, 3).Value
                        Sheet1.Cells(i, 4).Value = Sheet2.Cells(j, 4).Value
                        Exit For
                    Else
                        Sheet1.Cells(i, 2).Value = "N/A"
                    End If
                End If
            End If
        Next j
    Next i
End Sub

The downside of this code is that it could take like forever to iterate through all these lines and depending on your machine Excel could hang itself.

I am currently thinking of something different to solve the problem, but until that you'll have to deal with it.
 

PeteWright

New Member
Joined
Dec 20, 2020
Messages
23
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
Yes, it was only an example, because I didn't know how many lines / rows of data you have got. So I created a simple table with only 10 lines of data.




Now I need to know a little bit more about your data.
  1. Does your data set have a fixed number of entries? Or is it dynamic?
  2. You need to know how the code works. How deep is your knowledge of VBA and programming?
    What I have done is a nested FOR-LOOP which iterates two times from 2 to 99.
    That means 98 x 98 = 9604 calculation steps.
    The bigger your list, the more calculation steps are needed (obviously)
    Let's say you have 9999 entries, that makes max. 9999 x 9999 = 99 980 001 calculations.

    VBA takes the name in the first line and compares it to ALL entries until a match is found (max 9999).
    Then it takes the name in the second line and also compares it to ALL entries except the one that was already found.
    The math behind this would be:
    1. line: 1 x 9999
    2. line: 1 x 9998
    3. line: 1 x 9997
    and so on...
    That actually makes 9999 + 9998 + 9997 + ... = 49 995 000 calculations

    The problem is that you don't know the difference between data set 1 and data set 2.
    There may be only a few more entries in data set 2 or there may be a lot more.
    That is why I have chosen this coding method
Excel has certain limits: Excel specifications and limits.

Now, if you have the maximum possible amount of data, that would be:
1. line: 1 x 1,048,576
2. line: 1 x 1,048,575
3. line: 1 x 1,048,574
and so on...
This would sum up to: 1,048,576 x 1,048,575 x 1,048,574 x ... = 549 756 338 176

I think you get the picture.

Now let us just assume the data does not exceed 9999 lines.
The VBA code would then look like this:

VBA Code:
Private Sub DataCopy()
    Dim i, j As Integer
    For i = 2 To 999
        For j = 2 To 999
            If Sheet2.Cells(j, 2).Value <> "checked" Then
                If Sheet1.Cells(i, 1).Value <> "" Then
                    If Sheet1.Cells(i, 1).Value = Sheet2.Cells(j, 1).Value Then
                        Sheet2.Cells(j, 2).Value = "checked"
                        Sheet1.Cells(i, 2).Value = "found"
                        Sheet1.Cells(i, 3).Value = Sheet2.Cells(j, 3).Value
                        Sheet1.Cells(i, 4).Value = Sheet2.Cells(j, 4).Value
                        Exit For
                    Else
                        Sheet1.Cells(i, 2).Value = "N/A"
                    End If
                End If
            End If
        Next j
    Next i
End Sub

The downside of this code is that it could take like forever to iterate through all these lines and depending on your machine Excel could hang itself.

I am currently thinking of something different to solve the problem, but until that you'll have to deal with it.
Sorry...
The VBA for 9999 entries should be changed:

VBA Code:
    For i = 2 To 10000
        For j = 2 To 10000
 

coffeeman12

New Member
Joined
Sep 19, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. MacOS
Thanks again for your help.

I'm a novice at VBA. I've been playing around with it for about 8 months now. Mostly just copying code, editing it as much as I can, pasting blocks together to do what I want. I don't know enough to write anything start to finish on my own, although I try to pick something up from experts like you from projects like this one.

I got the code to work by:
1) Cleaning up the data list (Sheet1) by removing duplicate rows. That pared the data down to 157 unique rows*.
2) Changing i = 157, and j = 10047 (the full length of the "dictionary" of names and emails).

(I just saw your update, thanks!)

This copied all the emails over to the list in Sheet1.

*A problem I ran into was in running the duplicate row remover is that it appears to have deleted all entries that had multiple instances, not just the extra instances! Have to find some code to do that differently.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,239
Messages
5,595,002
Members
413,958
Latest member
SBM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top