# Comparing and adding missing data to sheets

#### l KAUTION l

##### Board Regular
I have two sheets

On sheet 1 for example we have:

00102 101 02342 Tom
00102 101 43242 Jim
00102 101 66534 Larry
00102 101 54321 Rob

On sheet 2 for example we have:

00102 202 02342 Tom
00102 202 66534 Larry
00102 202 54321 Rob

On sheet 2 we are missing:

00102 202 43242 Jim

Is there any code for this to systematically update sheet 2 so the missing data will be added to the sheet?

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Also is there a way to get it inserted into the same row as sheet 1 has it?

For example:

00102 202 02342 Tom
00102 202 43242 Jim
00102 202 66534 Larry
00102 202 54321 Rob

Sorry I just found out that I need to add the rows that are missing from sheet 2 to a new sheet 3. Is this possible? I have been searching and have found similar things but am not for sure if they will work.

I have two sheets

On sheet 1 for example we have:

..........A......B......C......D
1.....00102 101 02342 Tom
2.....00102 101 43242 Jim
3.....00102 101 66534 Larry
4.....00102 101 54321 Rob

On sheet 2 for example we have:

..........A......B.......C......D
1.....00102 202 02342 Tom
2.....00102 202 66534 Larry
3.....00102 202 54321 Rob

On sheet 2 we are missing:

00102 202 43242 Jim

I am using this formula in sheet 1 and dragging it down:

=IF(OR(C2='Sheet2'!C1,C2='sheet2'!C2,C2='sheet2'!C3,C2='085'!C4,C2='sheet2'!C5),"Ok","Missing")

Instead of checking just a few cells, is there a way to see if c2 = any of the cells in sheet 2 column c?

Or is there an easier way than this? I am even explaining this correctly?

I just need to know how to compare a sheet2 cell to an entire sheet3 column and see if the column contains the data in the cell. The data in the column could be in the first row or the last row. It is not in a specific order. I need to know the data the is missing from sheet3.

077 = sheet2
085 = sheet3

This is the code I currently have for sheet2 cell C2:

=IF(OR(C2='085'!C1,C2='085'!C2,C2='085'!C3,C2='085'!C4,C2='085'!C5,C2='085'!C6,C2='085'!C7,C2='085'!C8,C2='085'!C9,C2='085'!C10),"Ok","Missing")

This is the code I currently have for sheet2 cell C3358:

=IF(OR(C3358='085'!C3339,C3358='085'!C3340,C3358='085'!C3341,C3358='085'!C3342,C3358='085'!C3343,C3358='085'!C3344,C3358='085'!C3345,C3358='085'!C3346,C3358='085'!C3347,C3358='085'!C3348,C3358='085'!C3349,C3358='085'!C3350,C3358='085'!C3351,C3358='085'!C3352,C3358='085'!C3353,C3358='085'!C3354,C3358='085'!C3355,C3358='085'!C3356,C3358='085'!C3357,C3358='085'!C3358,C3358='085'!C3359,C3358='085'!C3360,C3358='085'!C3361,C3358='085'!C3362,C3358='085'!C3363,C3358='085'!C3364,C3358='085'!C3365,C3358='085'!C3366,C3358='085'!C3367,C3358='085'!C3368),"Ok","Missing")

There has to be an easier way. As I go down farther I have to add a previous cell and a following cell.

You can use Vlookups perhaps.

In Sheet 3

A1:
=If(Isna(vlookup(sheet1!a1,Sheet2!\$A\$1:\$D\$100,1,0)),Sheet1!A1,"")

B1:
=If(Isna(vlookup(sheet1!a1,Sheet2!\$A\$1:\$D\$100,2,0)),Sheet1!b1,"")

C1:
=If(Isna(vlookup(sheet1!a1,Sheet2!\$A\$1:\$D\$100,3,0)),Sheet1!c1,"")

D1:
=If(Isna(vlookup(sheet1!a1,Sheet2!\$A\$1:\$D\$100,4,0)),Sheet1!d1,"")

Then use DATA|FILTER|AUTOFILTER for Non Blanks.
Copied down.

hi,

This macro will copy non-matching rows from sheet1 to sheet3:
Code:
``````Sub xxx()
Dim iCol As Integer
Dim lRow As Long
Dim R As Range, rComp1 As Range, rComp2 As Range
Dim vResult As Variant, vData(1 To 4) As Variant
Dim wsComp1 As Worksheet, wsComp2 As Worksheet, wsTo As Worksheet

Set wsComp1 = Sheets("Sheet1")
Set wsComp2 = Sheets("Sheet2")
Set wsTo = Sheets("Sheet3")
Set rComp2 = wsComp2.Range("C1:C" & wsComp2.Cells(Rows.Count, "C").End(xlUp).Row)
wsTo.Cells.ClearContents

lRow = 0
For Each R In wsComp1.Range("C1:C" & wsComp1.Cells(Rows.Count, "C").End(xlUp).Row)
vResult = "*"
On Error Resume Next
vResult = WorksheetFunction.Match(R.Value, rComp2, 0)
On Error GoTo 0
If IsNumeric(vResult) = False Then
lRow = lRow + 1
For iCol = 1 To 4
vData(iCol) = wsComp1.Cells(R.Row, iCol).Value
Next iCol
wsTo.Range("A" & lRow & ":D" & lRow).Value = vData
End If
Next R

End Sub``````

Sorry for my ignorance but where do I put that code? I put it in the VB editor and changed the sheet names:

Sub xxx()
Dim iCol As Integer
Dim lRow As Long
Dim R As Range, rComp1 As Range, rComp2 As Range
Dim vResult As Variant, vData(1 To 4) As Variant
Dim wsComp1 As Worksheet, wsComp2 As Worksheet, wsTo As Worksheet

Set wsComp1 = Sheets("Sheet2")
Set wsComp2 = Sheets("Sheet3")
Set wsTo = Sheets("Sheet4")

Set rComp2 = wsComp2.Range("C1:C" & wsComp2.Cells(Rows.Count, "C").End(xlUp).Row)
wsTo.Cells.ClearContents

lRow = 0
For Each R In wsComp1.Range("C1:C" & wsComp1.Cells(Rows.Count, "C").End(xlUp).Row)
vResult = "*"
On Error Resume Next
vResult = WorksheetFunction.Match(R.Value, rComp2, 0)
On Error GoTo 0
If IsNumeric(vResult) = False Then
lRow = lRow + 1
For iCol = 1 To 4
vData(iCol) = wsComp1.Cells(R.Row, iCol).Value
Next iCol
wsTo.Range("A" & lRow & ":D" & lRow).Value = vData
End If
Next R

End Sub

When I run the Macro I get a message box stating "Subscript out of range".

Wow!

I was having the ID10T error!

Sub xxx()
Dim iCol As Integer
Dim lRow As Long
Dim R As Range, rComp1 As Range, rComp2 As Range
Dim vResult As Variant, vData(1 To 4) As Variant
Dim wsComp1 As Worksheet, wsComp2 As Worksheet, wsTo As Worksheet

Set wsComp1 = Sheets("077")
Set wsComp2 = Sheets("085")
Set wsTo = Sheets("Sheet1")

Set rComp2 = wsComp2.Range("C1:C" & wsComp2.Cells(Rows.Count, "C").End(xlUp).Row)
wsTo.Cells.ClearContents

lRow = 0
For Each R In wsComp1.Range("C1:C" & wsComp1.Cells(Rows.Count, "C").End(xlUp).Row)
vResult = "*"
On Error Resume Next
vResult = WorksheetFunction.Match(R.Value, rComp2, 0)
On Error GoTo 0
If IsNumeric(vResult) = False Then
lRow = lRow + 1
For iCol = 1 To 4
vData(iCol) = wsComp1.Cells(R.Row, iCol).Value
Next iCol
wsTo.Range("A" & lRow & ":D" & lRow).Value = vData
End If
Next R

End Sub

I belive this is working properly thank you very much. I really appreciate!

Replies
1
Views
140
Replies
1
Views
162
Replies
1
Views
220
Replies
1
Views
214
Replies
2
Views
355

1,203,462
Messages
6,055,565
Members
444,799
Latest member
CraigCrowhurst

### 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.

### Which adblocker are you using?

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

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