Data Verification Assistance

[Ngordyn]

Hi all,

I'm faced with an interesting data validation challenge from my manager, the typical (manual) process would be to compare 2 workbooks comparing ID numbers, and if the information in another column has changed.
If there was a change it would be copied to another workbook (I know 3 books ><) to follow up at a later point.

When my manager walked me through the process I straight away thought this could be simplified but I started the code, and well I got stuck.

So to elaborate on my goal in more detail the things I want to happen are:

• Find duplicated in column B
• If duplicate, then compare the values (of the same row) in column J (is it the same key information across the row)
  • I'm unsure if a 3rd verification step is required at this point
• If the data has changed, copy/paste the row with and without changes to secondary sheet

The reason I want to change my managers process is because a) I was asked to assist, and b) my manager now has over 40 workbooks of historical records. And by using the same workbook all of the data remains local in the event we have to go hunting for when an ID's value changes.

One of my attempts is below.

Sub Find_changes ()

Dim eventID As Range
Dim subtype As Range
Dim cell As Range
Dim LastRow As Long
Dim Reader As Worksheet
Dim Writer As Worksheet

Set Reader = ThisWorkbook.Worksheets(2)
Set Writer = ThisWorkbook.Worksheets(3)
Set eventID = Range("a:a")
Set subtype = Range("b:b")

Let LastRow = Writer.Cells.SpecialCells(xlCellTypeLastCell).Row

For Each cell In eventID
    If eventID = eventID And subtype <> subtype Then
        cell.EntireRow.Copy Destination:=Writer.Range(LastRow)
                
    End If
 Next
 
    
End Sub
    
End Sub

I know the code is very rough, but I think one of my biggest challenges is I am currently learning several code languages at the same time so I mix terminologies.

Any help would be greatly appreciated.

 

[6StringJazzer]

1. Your description says you have two workbooks to compare, but your code refers to two worksheets.
2. Your description says you write the results to a third workbook, but your code writes to the second worksheet.
3. Your description refers to column J but the code refers to column B.

Can you clarify what you actually need to do?

Your matching logic needs to be completely rewritten. I don't want to try to do that until I understand your actual requirements.

 

[Ngordyn]

Thanks for the reply, I will try to elaborate.

1. Currently the process is using 3 workbooks, however I want to utilise 1 worksheet (copy new report into the last row) and
2. Copy the results (duplicate in column b when the values in column J are not equal) into a new sheet.
3. You are completely correct I did have a typo for the column referencing in the code above, I was working with dummy data when I was working on the code .

An example of the data set is below as it may explain better.

Date	EventID	Event Date	Event time	Region	Site location	Name	Report Category	Report Type	Report Subtype
1/01/2022​	824466​	1/01/2022​	1:00:00 PM​	State A	Site location 2	John smith	1​	Type 1	SubType 1
1/02/2021​	1634676​	1/02/2021​	4:45:00 AM​	State B	Site location 1	Jane doe	2​	Type 3	SubType 3
4/05/2022​	1636777​	4/05/2022​	4:30:00 PM​	State B	Site location 5	Homer Simpson	1​	Type 4	SubType 4
6/05/2022​	1633678​	6/05/2022​	9:35:00 AM​	State B	Site location 3	Mr Burns		Type 3	SubType 3
1/01/2022​	824466​	1/01/2022​	1:00:00 PM​	State A	Site location 2	John smith	1​	Type 2	SubType 2

Ideally if the code:

• Read column B (EventID) to identify duplicates
• On duplicate (824466) compare values column J (Report SubType)
• If values are different (SubType 1 and SubType 2 is this example)
• Copy both rows to sperate sheet

I hope this clarifies and my code was at least on the right track.

Kindest regards