Populate missing entries from one sheet to another

Question

I have two Sheets in Excel. For this example, Sheet 1 and Sheet 2.

Sheet 1: 100 records with Name, Location, Gender

Sheet 2: 100 records with Name, Location, Age

The sources of the data are not synchronized, therefore, I have data that exist in Sheet 1 that doesn't exist in Sheet 2, I also have data in Sheet 2 that doesn't exist in Sheet 1 and I have data that exists in both sheets. So, there may be names in Sheet 1 that are not in Sheet 2 and vice versa.

I'm looking for an automated way to consolidate both sheets into a third sheet and populate the Name & Location and where available Age and Gender. I know this would be easy to do in MS Access, but due to my audience, I need to keep this in Excel and I'm a complete noob to VBA :S

What I have done: On sheet 3, I have made formulas to extract the data from Sheet 1 and in Sheet 2 I have added a field ("X"), which the formula will label the field with "1" if the field is not present in Sheet 1 and will label with "0" if the field is present in Sheet 1. In my mind, I'm looking for a way that will state: "If field "X" in Sheet 2 = "1", then extract that data and append it to Sheet 3, but am unsure on how to do that :(

Answer 1

this would require a not so difficult VBA script.

Here is the pseudo code

Create Array that contains All Names from Sheet B
For i = 0 to CellFromACount
 IF A.Name is not in ArrayB
   SheetB.AddLine (Item)
Next

In order the write the code, follow some tutorial by searching Excel vba tutorial on a search engine. Example here: VBA Tutorial

The things you need to do are:

1. Array of strings
2. Get Value From Cell
3. For Loop
4. If condition
5. Add new line at bottom of the sheet

Answer 2

This should work. Put in in a module:

Public Sub sheetmerge()
Dim wkb As Workbook
Dim wks, wks1, wks2 As Worksheet
Set wkb = ThisWorkbook
Set wks = wkb.Sheets(1)
Set wks1 = wkb.Sheets(2)
Set wks2 = wkb.Sheets(3)
emptyRow1 = False
i = 1
k = 1
' Loops while there is data on sheet 1 column 1
While emptyRow1 = False
    found = False
    ' extracts the name from column 1, row i
    name1 = wks.Cells(i, 1)
    If name1 <> "" Then
        'As it is not empty then loop through Sheet2 names
        emptyRow2 = False
        j = 1
        While emptyRow2 = False
            name2 = wks1.Cells(j, 1)
            If name2 <> "" Then
                'As name2 is not empty then compare name and name 2
                If name1 = name2 Then
                    'If name1 is the same as name2 then put the data on Sheet3
                    wks2.Cells(k, 1) = name1
                    wks2.Cells(k, 2) = wks.Cells(i, 2)
                    wks2.Cells(k, 3) = wks.Cells(i, 3)
                    wks2.Cells(k, 4) = wks1.Cells(j, 3)
                    found = True
                    k = k + 1
                    'stop looping sheet2 by setting emptyrow2 to True
                    emptyRow2 = True
                End If
            Else
                'name2 is empty, then stop looping sheet2
                emptyRow2 = True
            End If
            j = j + 1
        Wend
    Else
        'name1 is empty then stop looping Sheet1
        emptyRow1 = True
    End If
    'if after looping there was no coincidence of names then put the data from Sheet1
    If found = False Then
        If name1 <> "" Then
            wks2.Cells(k, 1) = name1
            wks2.Cells(k, 2) = wks.Cells(i, 2)
            wks2.Cells(k, 3) = wks.Cells(i, 3)
            k = k + 1
        End If
    End If
    i = i + 1
Wend
'This area loops through Sheet2 seeking for coincidences
emptyRow2 = False
j = 1
While emptyRow2 = False
    found = False
    name2 = wks1.Cells(j, 1)
    If name2 <> "" Then
        emptyRow1 = False
        i = 1
        While emptyRow1 = False
            name1 = wks.Cells(i, 1)
            If name1 <> "" Then
                If name2 = name1 Then
                    found = True
                    emptyRow1 = True
                End If
            Else
                emptyRow1 = True
            End If
            i = i + 1
        Wend
    Else
        emptyRow2 = True
    End If
    ' If there wasn't a coincidence it means this name is in sheet2 but is not on Sheet1 so, put it in Sheet3.
    If found = False Then
        wks2.Cells(k, 1) = name2
        wks2.Cells(k, 2) = wks1.Cells(j, 2)
        wks2.Cells(k, 4) = wks1.Cells(j, 3)
        k = k + 1
    End If
    j = j + 1
Wend
End Sub

It loops through rows on sheet1 putting them on sheet3 putting a fourth column if is there coincidence with sheet2.

After that it loops through sheet2 putting on sheet3 rows not found on sheet1.