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

[coffeeman12]

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.

 

[PeteWright]

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]

MrExcel_coffeeman12_datacopy.xlsm
	A	B	C	D
1	Name	status	confidetial email	regular email
2	John Smith
3	Tim Miller
4	Michael Cain
5	Jennifer Abigail
6	Frank Stevens
7	Matthew Andrews
8	Sarah Brown
Sheet1

MrExcel_coffeeman12_datacopy.xlsm
	A	B	C	D
1	Name	status	confidetial email	regular email
2	Michael Cain		Michael.Cain@companyX.com	M7C4@confidential.net
3	Frank Stevens			F5S7@confidential.net
4	Sarah Brown		Sarah.Brown@companyX.com
5	John Smith			J4S5@confidential.net
6	Thomas Owens		Thomas.Owens@companyX.com	T6O5@confidential.net
7	Timothy Doyle			T7D5@confidential.net
8	Tim Miller			T3M6@confidential.net
9	Veronica Hopkins		Veronica.Hopkins@companyX.com	V8H7@confidential.net
10	Jennifer Abigail		Jennifer.Abigail@companyX.com
11	Matthew Andrews		Matthew.Andrews@companyX.com	M7A7@confidential.net
Sheet2

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]

Thanks for your help, I'll check this out later!

 

[coffeeman12]

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]

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:

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]

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:

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:

    For i = 2 To 10000
        For j = 2 To 10000

 

[coffeeman12]

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.