Comparing data (conditionally) between two spreadsheets

[yord]

Hi, I'm hoping someone can help me with the following scenario.

Spreadsheet A has the following (always in Column A)

NCNTPASH1AP136
CUSNPRSJ1AP11
NJANPRSJ1AP03
JANBVSJ9798RR023



Spreadsheet B has the following (not always in the same columns but
always located next to each other. So, they could be column A and B
one week or column B and C the next.)

NCNTPASH1AP136 Ordered
NJANPRSJ1AP11 In Inventory
NJANPRSJ1AP03 In Inventory
JANPRSJ9798RR001 Deployed

Spreadsheet A's column is not titled, it's simply a list of server
names. Spreadsheet B has column names (a 9 Mb dump from a Remedy
database) and they are always ("CI Name" and "Status").

I'm interested in comparing server names in Spreadsheet A to those
that are in Spreadsheet B but only if they are marked "Deployed." Does
that make sense? I need to know which server names are common to both
spreadsheets and which servers are unique to each. Can someone help me
solve this both by using Excel's built-in functions as well as with a
VB script? I ask this because I genuinely want to understand and learn
this for myself. Thank you so much for any time and consideration you
can afford.

 

[Joel]

Is there a way of determining which is the first column in sheet B? Is there
a header in the column or row a\the will determine where the data is located.
the task isn't too hard once we know the start location. Can we use a
inputbox to determine the 1st cell?

 

[yord]

Is there a way of determining which is the first column in sheet B?  Isthere
a header in the column or row a\the will determine where the data is located.
 the task isn't too hard once we know the start location.  Can we usea
inputbox to determine the 1st cell?

Spreadsheet (workbook) A does not have a column header, thus the
starting cell will always be A1. Spreadsheet (a separate workbook) B
always has a header but currently holds within columns B and C (B2 and
C2 respectively). The column locations may change but it's not a big
deal since I can always modify the spreadsheet manually to accommodate
the script or vice versa. Therefore, it may not be important for the
script to know about the header names contained in Spreadsheet B ("CI
Name" and "Status"). I simply mentioned them in the hopes that a
script could search any two columns based on the header name rather
than column location. Does that make sense?

 

[Joel]

Try this code. It will allow you to select the two tables to compare. the
first is the one with the status and the 2nd is the one with the ID's to
compare. create a worksheet called Summary where the results will be placed.
the Deployed ID's will appear in column A. Column B will contain the ID's
from the compare table. where there is a match the two IDs will appear on
the same row. when a match doesn't occur there will not be an ID in both
columns.


Sub CompareData()

Set SummarySht = Sheets("Summary")
With SummarySht
SummarySht.Cells.ClearContents
'add headers
.Range("A1") = "Deployed CI Names"
.Range("A1") = "CI Names to Compare"
End With

Set StatusRange = Application.InputBox( _
prompt:="Select Column where Status Table starts", _
Type:=8)

Set StatusSht = StatusRange.Parent
statusCol = StatusRange.Column

Set CompareTable = Application.InputBox( _
prompt:="Select Column where Compare Table is located", _
Type:=8)

Set CompareSht = CompareTable.Parent
CompareCol = CompareTable.Column

With StatusSht
'add header row so autofilter works properly
.Rows(1).Insert Shift:=xlDown
.Cells(1, statusCol + 1) = "status"
LastRow = .Cells(Rows.Count, statusCol).End(xlUp).Row

'filter the deployed data
.Columns(statusCol + 1).AutoFilter
.Columns(statusCol + 1).AutoFilter _
Field:=1, _
Criteria1:="Deployed"

Set CINameRange = _
.Range(.Cells(2, statusCol), .Cells(LastRow, statusCol))

CINameRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=SummarySht.Range("A2")

'remove autofilter
.Cells.AutoFilter
End With

With SummarySht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
End With

With CompareTable
'compare each item in table with deployed items
RowCount = 2
Do While .Cells(RowCount, CompareCol) <> ""
CIName = .Cells(RowCount, CompareCol)
'search for CIName in deployed list
With SummarySht
Set c = .Columns("A").Find(what:=CIName, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Range("B" & NewRow) = CIName
NewRow = NewRow + 1
Else
c.Offset(0, 1) = CIName
End If
End With
RowCount = RowCount + 1
Loop
End With
End Sub