Need help with formula to search and compare data in different col

[Michael]

Greetings!

I need help writing a formula to search through columns of data in two
spreadsheets, compare some values in each of the spreadsheets, and identify
any unpaired records.

Situation:

I have two spreadsheets that deal with the same entities.

In the first spreadsheet, I have 4 columns that make up a unique record:

PWSID (alpha num)
PWS Name (alpha num)
Facility Name (alpha num)
Approved Date (date)

In the second spreadsheet, I have 5 columns that make up a unique record:

PWSID (alpha num)
PWS Name (alpha num)
Facility Name (alpha num)
Actual Date (date)
Original Date (date)

What I need to do is the following:

For each unique record in the first spreadsheet, search the second
spreadsheet to find any records that match the following criteria:

1) PWSID and Facility names are identical; and 2) Either of the following is
true: " approved date" in the first spreadsheet is +/- 2 days from "actual
date" or "original date" in the second spreadsheet.

For each record in the first spreadsheet, if it finds a record in the second
spreadsheet that meets the search criteria, I would want an 'OK' to be placed
in the first spreadsheet (new column). If the search does not find a record
in the second, it should return 'No Record Found'.

Any help folks could provide would be greatly appreciated!

 

[Per Jessen]

Hi Michael

I think you will need a macro to do that.

You say you have two spreadsheets to compare are they in the same
workbook.

I assume data start in column A on both sheets

Post some sample data for test purpose.

Regards,
Per

 

[Michael]

Thanks for getting back to me!

Example of data in Spreadsheet 1 (including column headers):

PWSID PWS Name Plant Scheduled
OH2101412 DEL-CO DEL-CO OLENTANGY PLANT 5/16/2007

Example of Data in Spreadsheet 2 (including column headers):

PWSID PWS Name Plant Actual
Original
OH2101412 DEL-CO DEL-CO OLENTANGY PLANT 5/16/2008 5/12/2008

The data are in different workbooks, but I can copy one data set into a
separate tab in the other workbook if it makes it easier. The fields
(columns) shown in the Spreadsheet 1 example above are together (columns 2-5
in worksheet 1), but the data in the second worksheet would not be in
consecutive columns. Only the PWSID and PWS Name fields in the second
worksheet are next to each other (columns 2-3), and the 'Plant', 'Actual',
and 'Original' columns occur further to the right in worksheet 2, in
non-contiguous columns (not next to each other).

What I need to do is to determine for thousands of sets of data like example
1, whether there is corresponding data (in the second workbook) that meets
the criteria I spelled out. So for the data listed above, what I would want
the macro to do is look at each row of data in the first worksheet (like
example 1) and then check each row in the second worksheet to see if it finds
(in thousands of rows of data) a row which contains: the same PWSID and PWS
Name, and that has a date (either in the 'actual' or 'original' columns) that
is within +/- 2 days of the date listed in the first worksheet ('scheduled').

Once again, thank you kindly for your help!

 

[Per Jessen]

Hi Michael

To keep things simple, copy the data from workbook2 to sheet2 in
workbook1.

Past the code into a module and change PlantCol, AccDateCol and
OrgDateCol, to the desired column numbers in sheet2.

Sub MatchAndCheck()
Dim shA As Worksheet
Dim shB As Worksheet
Dim TargetCol As String
Dim FirstRow As Long
Dim LastRow As Long

Application.Screenupdating=False
Set shA = Worksheets("Sheet1")
Set shB = Worksheets("Sheet2")
TargetCol = "B"
PlantCol = 4 'Change to suit
AccDateCol = 5 ' Change to suit
OrgDateCol = 6 ' Change to suit

FirstRow = 2 ' Headings in row 1
LastRow = shA.Range(TargetCol & Rows.Count).End(xlUp).Row

For r = FirstRow To LastRow
PWSID = shA.Range(TargetCol & r).Value
Set f = shB.Columns(TargetCol).Find(what:=PWSID, _
After:=shB.Range(TargetCol & 1), lookat:=xlWhole)
Debug.Print f.Address
If Not f Is Nothing Then
If shA.Cells(r, 3).Value = shB.Cells(f.Row, PlantCol).Value
Then
'PWSID & Facility Name match
AppDate = shA.Cells(r, 5).Value
AccDate = shB.Cells(f.Row, AccDateCol).Value
OrgDate = shB.Cells(f.Row, OrgDateCol).Value
If Abs(AppDate - AccDate) <= 2 Or Abs(AppDate-OrgDate) <=
2 Then
shA.Cells(r, 6) = "OK"
End If
Else
Set fFirst = f
Do
Set f = shB.Columns(TargetCol).FindNext(f)
Debug.Print f.Address
If Not f Is Nothing Then
AppDate = shA.Cells(r, 5).Value
AccDate = shB.Cells(f.Row, AccDateCol).Value
OrgDate = shB.Cells(f.Row, OrgDateCol).Value
If Abs(AppDate - AccDate) <= 2 Or Abs(AppDate-
OrgDate) <= 2 Then
shA.Cells(r, 6) = "OK"
Exit Do
End If
End If
shA.Cells(r, 6) = "No Record Found"
Loop Until f.Address = fFirst.Address
End If
End If
Next
Application.Screenupdating=True
End Sub

Hopes this helps

 

[Michael]

Thanks, much!

I will give it a try tomorrow AM.