Filling in id number that are missing from two spreadsheets

**tantcu** · 01-20-2013, 09:04 PM

Hello,

I'm working to insert new data to sheet 2. All the data is available in Sheet 1. I can use Vlookup function for Sheet2 to get value from column B of sheet 1. However, do you know any formula that I can use to find out any id of sheet 2 is missing from sheet 1 and add them to the id column of sheet 2 and add them to the next row of the last row instead.

Attached is the file Sample.xlsx . Please help me on this and thank you in advance.

**niladri2005** · 01-21-2013, 01:55 AM

Hi Tantcu,

Please find the attached file. I have taken the help of Macro. In sheet 2 you will get a box. click that box and Not available codes will be added in sheet 2.

**:) Sixthsense :)** · 01-21-2013, 01:55 AM

AUTOMATICALLY INSERTING OF DATA not possible in worksheet function so I have given VBA code solution. I have attached your workbook with the below code and in sheet2 I made a button to get the MISSING DATA on click.

Sub CheckAndAddData()
Dim nEndRw As Long, x As Long, i As Long, c As Long
Dim wSh1 As Worksheet, wSh2 As Worksheet

Application.ScreenUpdating = False

Set wSh1 = Sheets("Sheet1")
Set wSh2 = Sheets("Sheet2")
x = wSh2.Cells(Rows.Count, "A").End(xlUp).Row
c = x
nEndRw = wSh1.Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To nEndRw
    With wSh1
        If .Cells(i, "A").Value <> "" Then
            If WorksheetFunction.CountIf(wSh2.Range("A1:A" & x), .Cells(i, "A").Value) = 0 Then
                wSh2.Cells(x + 1, "A") = .Cells(i, "A")
                x = wSh2.Cells(Rows.Count, "A").End(xlUp).Row
            End If
        End If
    End With
Next i

Application.ScreenUpdating = True

With wSh2.Range("A" & c & ":A" & x)
    .Interior.ColorIndex = 6
    .Select
End With

MsgBox "Data Appended Successfully. Check End of Column-A With YELLOW BACKGROUND" _
    & " for Newly Added Data", vbInformation, "Task Completed"

End Sub