Comparing one list of numbers against another

[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

[cb]

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



"Mike Cook" <mjcook@ntlworld.com> wrote in message
news:rK9Ie.26694$Ag3.19237@newsfe4-gui.ntli.net...
> 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
>
>

[Dave Peterson]

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



Mike Cook wrote:
>
> 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

--

Dave Peterson

[Dave Peterson]

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

Dave Peterson wrote:
>
> Another way is to use =match() to for common values.

<snipped>