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,081,903
Messages
5,361,972
Members
400,667
Latest member
cryptomike

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top