Comparing one list of numbers against another

  • Thread starter Thread starter Mike Cook
  • Start date Start date
M

Mike Cook

Hi , Can someone help me with this problem

I want to compare a list of tape media with another , The results should
show missing media and new media in separate columns

An example

First Inventory Second Inventory Missing Media New Media

TAPE01 TAPE01 TAPE07 TAPE11
TAPE02 TAPE02 TAPE08
TAPE03 TAPE03 TAPE09
TAPE04 TAPE04 TAPE10
TAPE05 TAPE05
TAPE06 TAPE06
TAPE07 TAPE11
TAPE08
TAPE09
TAPE10

Any help appreciated

Regards

Mike
 
Mike,

Try this! I whipped this up real quick for the Missing Media column. It
takes the First Inventory and compares against the Second Inventory to
arrive at Missing Media. I'll let you figure out the New Media.

Option Explicit

Sub MissingMedia()
Dim oFI As Range ' First Inventory
Dim oSI As Range ' Second Inventory
Dim oMM As Range ' Missing Media
Dim iRowCount As Integer
Dim i As Integer, x As Integer
Dim iMissCount As Integer
Dim sMissing() As Variant

iMissCount = 1
Set oFI = ActiveSheet.UsedRange.Columns(1)
Set oSI = ActiveSheet.UsedRange.Columns(2)
Set oMM = ActiveSheet.UsedRange.Columns(3)
iRowCount = ActiveSheet.UsedRange.Rows.Count
For i = 3 To iRowCount
For x = 3 To iRowCount
If oFI.Cells(i) = oSI.Cells(x) Then
' found a match, exit
Exit For
End If
If x = iRowCount And oFI.Cells(i) <> oSI.Cells(iRowCount) Then
' No match
ReDim Preserve sMissing(iMissCount)
sMissing(iMissCount) = oFI.Cells(i).Value
iMissCount = iMissCount + 1
End If
Next x
Next i

' write out the missing media
For i = 1 To UBound(sMissing)
oMM.Cells(i + 2) = sMissing(i)
Next i

' guess you could set the objects to "Nothing", but read somewhere that
it didn't really matter.
End Sub

Regards,

Chris
 
Another way is to use =match() to for common values.

Option Explicit
Sub testme()

Dim rngA As Range
Dim rngB As Range
Dim myCell As Range
Dim DestCell As Range

With ActiveSheet
'headers in row 1.
Set rngA = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
Set rngB = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))

'look through column A for matches in column B
Set DestCell = .Range("c2")
For Each myCell In rngA.Cells
If IsError(Application.Match(myCell.Value, rngB, 0)) Then
DestCell.Value = myCell.Value
Set DestCell = DestCell.Offset(1, 0)
End If
Next myCell

'look through column B for matches in column A
Set DestCell = .Range("d2")
For Each myCell In rngB.Cells
If IsError(Application.Match(myCell.Value, rngA, 0)) Then
DestCell.Value = myCell.Value
Set DestCell = DestCell.Offset(1, 0)
End If
Next myCell
End With
End Sub
 
Back
Top