How to compare weekly data output automatically?

[mrSupernoob]

Hi everybody.

This might be a silly question but I have not yet found an answer with googling. Might be that I have incorrect search terms etc. but I really need some help

I have an assignment that requires me to produce a excel tool for comparing several attributes / differences in a data set that is produced each week. A sort of customer account data that needs to be checked for changes and one column of data needs to be extracted and produced to a list differentiated with commas and empty spaces removed.

These are account numbers where there is a number on column A and other customer information on other columns (from B to I).

So I have these steps that I need to do:

1. Every week I have to compare the data output from this week to the last to see if there are changes. The customer accounts can be "closed" and this is marked in a separate column. These rows that have a date marked on one column have to be located and deleted from the data set.
2. I have to see if there are new accounts added to the list. These have to be added to the list with just the account numbers.
3. I also have a list of client names (on a seperate excel file). I have to cross check the list so that no client has been dropped completely from the list / or if there is a new account on the account name list but they don't have any accounts on the account list (my main excel file).

I would strongly prefer a solution that would need as little as possible manual work. The file has about 1500 rows of data so any manual effort will be quite uhm... inconvenient.

Hopefully I have explained the problem so that it makes even slightly sense and I'll gladly answer to any questions about more details. Unfortunately I cannot post you the file since it contains sensitive client information. Thank you in advance!

Best regards,

mrSupernoob

 

[portews]

What do you want to do if you do find a difference: highlight the cell on the new sheet, the old sheet or both; put the old info in a different column on the new sheet; autofilter the original sheet to show only the rows with changes; put both rows on another sheet or something else?

Which worksheet has the closed date, the latest file or the previous file? Are you deleting the record that month (when the date first occurs in the new file), or the next month (when it sees a date in the old month, it deletes the new sheet record)? What column has the date? Does your client name list retain the names after the account name is closed? How do you differentiate between a name not on the main or client list because it was missed or an intentionally closed account? How do you want discrepancies reported?

If you can at least give the sheet names and show column headings with no information, that will help correlate the columns in the multiple worksheets. I find the tabelizer listed in my signature very handy for that sort of thing.

 

[mrSupernoob]

Hey Bill!

Thank you for your response.

I'll try to give you more details. Down below is the overall look and all the cells in my sheet tablerized.

The close date column is in all the sheets. What I need to do is locate those that have something on that column and delete them before exporting to the "Number" column into the list I mentioned. So basically, when I get this rolling, the previous week's data should have nothing in "Close date" column, only the new set.

Intentionally closed accounts should have a closed date and also marked on the Open / Close column (O is open, there are few other letters that signify closure - ). If the account is accidentally missing, it would just not appear on the list at all.

The way discrepancies are highlighted does not really matter that much. Just so that closed accounts are easy to locate and delete + presumably missing accounts on this weeks data are also easily located on the last weeks sheet. I was thinking that the missing accounts could be checked with simply taking a look at the number of rows but with this if there is something missing, I wont know which row it is...


Hope this helps to explain what I'm trying to accomplish. And thank you so much for your help.

<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>Number</th><th>Company name</th><th>area code (letters)</th><th>Address</th><th>Address2</th><th>CITY</th><th>ZIP Code</th><th>Open/closed</th><th>Identiefier</th><th>Close date</th></tr></thead><tbody>
<tr><td>123456789</td><td>Company A</td><td>AAA</td><td>Address 1</td><td> </td><td>NEW YORK</td><td>12363</td><td>O</td><td>025</td><td> </td></tr>
<tr><td>456789123</td><td>Company B</td><td>BBB</td><td>Address 2</td><td> </td><td>PARIS</td><td>45646</td><td>O</td><td>025</td><td> </td></tr>
<tr><td>789456123</td><td>Company A</td><td>CCC</td><td>Address 3</td><td> </td><td>LONDON</td><td>8796464</td><td>O</td><td>025</td><td> </td></tr>
<tr><td>321654987</td><td>Company C</td><td>DDD</td><td>Address 4</td><td> </td><td>TAIPEI</td><td>1561651</td><td>O</td><td>P91</td><td>14.12.2016</td></tr>
</tbody></table>

 

[portews]

Try this:

You select the last months sheet, it and the Customer Database will both open. If there's a close date in the latest sheet, Set C in O/C column and the customer db sheet and delete the date in the latest sheet. The customer number will be checked against last months sheet. If it finds a customer number match with last month, it checks each column for a corresponding match. Mismatches are made yellow. If no match is found, it's copied to the customer db and the latest month row is set to red.

Sub CompareBooks()Dim Wb As Workbook
Dim WbCust As Workbook
Dim Rng As Range
Dim RngCust As Range
Dim FindString As String
Dim LastRow As Integer
Dim LastRowCust As Integer
Dim i As Integer
Dim j As Integer
Dim CustDB As String


'fixed path for the Customer Database
CustDB = "C:\Users\Bill\customer.xls"


Set Wb = Nothing
'select and open last's months workbook
Call MyFileOpenSub("Open Last Months Workbook", Wb)
'open the Customer Database
Workbooks.Open (CustDB)
'set the name of the Customer database
Set WbCust = Workbooks(Right(CustDB, Len(CustDB) - InStrRev(CustDB, "\")))
'find the last row of this latest workbook (this one)
LastRow = ThisWorkbook.Sheets(1).Cells(Sheets(1).Rows.Count, "A").End(xlUp).Row


'for current workbook, sheet1, all the rows,
For i = 2 To LastRow
'get the number
FindString = ThisWorkbook.Sheets(1).Cells(i, 1)
    'check to see if there is anything in the Close Date column
    If Not IsEmpty(ThisWorkbook.Sheets(1).Cells(i, 10)) Then
        With WbCust.Sheets(1).Range("A:A")
        Set RngCust = .Find(What:=FindString, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        'if you find a match in the customer db
        If Not RngCust Is Nothing Then
            'copy the close date from the current sheet to the cutsomer db
            WbCust.Sheets(1).Cells(RngCust.Row, 10) = ThisWorkbook.Sheets(1).Cells(i, 10)
            'set the Close column
            WbCust.Sheets(1).Cells(RngCust.Row, 8) = "C"
            'clear the close date on the current sheet
            ThisWorkbook.Sheets(1).Cells(i, 10).ClearContents
            'set the Close column
            ThisWorkbook.Sheets(1).Cells(i, 8) = "C"
        End If
        End With
    End If
    'search in last months sheet for the same number
    If Trim(FindString) <> "" Then
        With Wb.Sheets(1).Range("A:A")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            'if you find a match
            If Not Rng Is Nothing Then
                'go thru each column
                For j = 2 To 9
                    'if the two don't match
                    If Not ThisWorkbook.Sheets(1).Cells(i, j) = Wb.Sheets(1).Cells(Rng.Row, j) Then
                        'set the interior color to yellow
                        ThisWorkbook.Sheets(1).Cells(i, j).Interior.ColorIndex = 6
                    End If
                Next j
            'if there is no match
            Else
                'copy the row
                ThisWorkbook.Sheets(1).Cells(i, 1).EntireRow.Copy
                'find the last row of the customer db
                LastRowCust = WbCust.Sheets(1).Cells(Sheets(1).Rows.Count, "A").End(xlUp).Row + 1
                'paste the customer info in the last row of the customer db
                WbCust.Sheets(1).Cells(LastRowCust, 1).PasteSpecial
                'color the whole row of the lastest db red
                ThisWorkbook.Sheets(1).Cells(i, 1).EntireRow.Interior.ColorIndex = 3
            End If
        End With
    End If
Next i
End Sub




Sub MyFileOpenSub(Title As String, Wb As Workbook)
Dim intChoice As Integer
Dim strPath As String


'only allow the user to select one file
Application.FileDialog(msoFileDialogOpen).Title = Title
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogOpen).Show
'determine what choice the user made
If intChoice <> 0 Then
    'get the file path selected by the user
    strPath = Application.FileDialog( _
        msoFileDialogOpen).SelectedItems(1)
On Error Resume Next
'See if the workbook is open, if it isn't, it will error
Set Wb = Workbooks(strPath)
'reset the error handling
On Error GoTo 0
    'if Wb variable is empty, the workbook isn't open
    If Wb Is Nothing Then
        '...so open it.
        Set Wb = Workbooks.Open(strPath)
    End If
End If
End Sub