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?
 

l KAUTION l

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

l KAUTION l

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

l KAUTION l

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

l KAUTION l

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

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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.
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,493
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
 

l KAUTION l

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

l KAUTION l

Board Regular
Joined
Nov 3, 2005
Messages
90
Wow! :banghead:

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!
 

Forum statistics

Threads
1,077,868
Messages
5,336,861
Members
399,109
Latest member
gdcuk

Some videos you may like

This Week's Hot Topics

Top