Comparing and adding missing data to sheets

l KAUTION l

Board Regular
Joined
Nov 3, 2005
Messages
90
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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Can someone please help me?

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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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".
 
Upvote 0
Wow! :oops:

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!
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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
Back
Top