Automating a Reconciliation Task

[tirmizi]

Hello,

I am trying to automate a rather mundane task of reconciling past data with new datasets. I have attached a sample of what I am trying to do.

Step 1: Take two different ranges as input, past data and new data.

Step 2: Intermediate - What I am doing currently. Sort the data in ascending order w.r.t. Col B because that remains unchanged between the two datasets. Then using If statements to check the two ranges, highlighting changes in yellow and new data in red.

Step 3: Output - Output only the rows with any new data or changes.

I have written the following code so far:

Sub reconcile()
    Dim Report As Worksheet
    Dim myRange As Range
    Set myRange = Selection
    Dim i As Integer, j As Integer
    Dim lastRow As Integer

    Set Report = Excel.ActiveSheet
   
    lastRow = myRange.Rows.Count
    lastCol = myRange.Columns.Count

    Application.ScreenUpdating = False

    For i = 1 To lastCol
        For j = 1 To 5
            If InStr(1, myRange.Cells(j, i).Value, myRange.Cells(j + 5, i).Value, vbTextCompare) > 0 Then
            Else
                myRange.Cells(j, i).Interior.Color = RGB(255, 255, 0)
                myRange.Cells(j + 5, i).Interior.Color = RGB(255, 255, 0)
            End If
        Next j
    Next i

Application.ScreenUpdating = True
End Sub

I have also recorded a sorting macro that sorts the two ranges.

Issues where any help would be much appreciated:

1. Combine the sorting with Reconciling so that only one macro needs to be run.
2. My macro consists of hard coded numbers e.g. j+5, the data can have widely varying ranges so I need to figure out how to account for this. There is a blank line between the two ranges.
3. Also I want the new addition to be colored red. This code does not account for new additions but highlights rows in yellow in both ranges leading to a whole empty row highlighted in the past range.
4. Remove rows that have no changes and output the rows that have changes or new data in a new sheet.

Any help would be highly appreciated.

 

[Chihiro]

1. You can just have 2 subs and call one from the other

Call SubName()

2. You can use something like below to find next empty row to define range (if using just as variable, you don't need Select portion)

Range("A1").End(xlDown).Offset(1).Select

3. & 4. will see if I have time later to look at it.

 

[tirmizi]

Hi, thanks a lot for your suggestions. I would really appreciate it if you could look into 3 and 4 as well.

 

[Chihiro]

I just got handed huge project at work. I probably won't have time to look at it till next week.

Here's quick tips
To sort by Column B.

Columns("B:B").Sort Key1:=Range("B1"), Order1:=xlAscending, header:=xlyes

However, this will sort blank row to bottom. So you need to combine it with 2. to define range.

3. To highlight new data:
For each cell in Column B in 2nd Range, you need to look up that value in Column B of 1st Range and see if any of them returns not found, then you want to use that cell in 2nd Range as point of reference to select the row and set colour.

4. Since you have highlighted new data in specific colour you can select and copy based on colour.

 

[tirmizi]

Hello, thanks a lot for your help. I'll try to incorporate your suggestions into my code, will update soon.

 

[Chihiro]

Here's useful thread for highlighting new data
http://stackoverflow.com/questions/...-rows-that-do-not-exist-in-an-other-worksheet