AUD $100 - Compare columns in two workbooks and remove row if match is negative

fess67 · Apr 22nd 2015

Hi,

Worksheets 222222 and cost code (attached)

222222 contains historical data. cost code is current data.

I need to compare the invoice numbers in col A of 222222 with the invoice numbers in col B of cost code.

If the invoice does not exist in cost code then remove row from 222222 else leave it there.

I need to do this over multiple workbooks with different names but 1 at a time. 222222 is simply a sanitised cost code, so the next sheet could be 449281.

I am happy to name each sheet 'report', execute the code and then change it to the proper name (in this instance 222222)

Thanks in advance.

Paypal payment has been made to Ozgrid.
forum.ozgrid.com/index.php?attachment/64879/
forum.ozgrid.com/index.php?attachment/64880/

**S O** · Apr 22nd 2015

Re: AUD $100 - Compare columns in two workbooks and remove row if match is negative

Hi fess67 - when do you need this done by?

John_w · Apr 22nd 2015

Re: AUD $100 - Compare columns in two workbooks and remove row if match is negative

I will look at this. Some questions:

1. Do the invoice numbers always start at A7 in the historical data worksheets? Or is there another way to consistently identify the starting row?

2. For the multiple workbooks, it may not be necessary for you to rename the relevant worksheet in each workbook to 'report' if the relevant worksheet is always in the same place in the workbook (e.g. it is always the 1st worksheet in the workbook, or always the 2nd, etc.), or if the relevant worksheet name is the same as the workbook name (e.g. sheet 222222 is in 222222.xlsx, sheet 449281 is in 449281.xlsx, etc.).

3. Are the multiple workbooks all in the same folder?

4. After the rows are deleted from 222222 should the TOTAL row be updated with the correct totals? Would SUM formulas be ok to calculate these totals (currently they are just raw values)?

John_w · Apr 22nd 2015

Re: AUD $100 - Compare columns in two workbooks and remove row if match is negative

S O responded first - so over to him.

fess67 · Apr 23rd 2015

Re: AUD $100 - Compare columns in two workbooks and remove row if match is negative

I will answer both posts:

Timeframe - happy for it to be done in a couple of weeks.

John:

1. Yes, always A7. That report is the result of some code I have developed and ran prior to this request.
2. I have a list of 30 or so cost codes. Some may or may not be present in that weeks output. When I download the original report from the database it arrives as a worksheet named report. I open it, run my code then manually compare it with the cost code report, removing entries that are not present in the cost code report.
3. Yes. I create a folder for each week ending date.
4. Yes. Again this is something I go in and do manually at the moment. I think I can work out the code to sum it using xldown.select and a range function but if you can do it then it would be nice

I can show my current code if that will help, although it does not address the issue here.

Here is my current workflow:

Create both reports from the database (I have no control over the formatting and it is terrible)
Save cost code as 'full cost code'
open each of the invoice reports one at a time
run my code to put them into the format shown in the example 2222222
manually compare invoice v cost code to remove the old invoices
save as the invoice cost code - in this case 222222
Once I have done this process with all the workbooks I copy them into a single workbook as separate work sheets and present to the client.

Thanks for taking the time to respond

**S O** · Apr 23rd 2015

Re: AUD $100 - Compare columns in two workbooks and remove row if match is negative

Okay seems reasonable. I'll have a look at this for you.

fess67 · Apr 24th 2015

Re: AUD $100 - Compare columns in two workbooks and remove row if match is negative

thanks

**S O** · Apr 27th 2015

Re: AUD $100 - Compare columns in two workbooks and remove row if match is negative

Hi fess67, can you try the below code and let me know how you get on please:

Code

Sub SO()


Dim costCentreWorkbookName  As String
Dim folderLocation          As String
Dim costCentreWorkbook      As Excel.Workbook
Dim costCodeWorkbook        As Excel.Workbook
Dim formulaRange            As Excel.Range


Application.ScreenUpdating = False


folderLocation = GetFolder()


Set costCodeWorkbook = Workbooks("cost code.xlsx") '// <--- change to whatever it's called, or comment out altogether and use line below:


If costCodeWorkbook Is Nothing Then Set costCodeWorkbook = Workbooks.Open(FindWorkbook())


costCentreWorkbookName = Dir(folderLocation, vbNormal)
    
    While Not costCentreWorkbookName = vbNullString
        
        Set costCentreWorkbook = Workbooks.Open(folderLocation & "\" & costCentreWorkbookName)
        
        With costCentreWorkbook.Sheets(1)
            .Columns(1).EntireColumn.Insert shift:=xlToRight
            
            Set formulaRange = .Range("B8:B" & .Cells(.Rows.Count, 3).End(xlUp).Row).Offset(-1, -1)
            
            formulaRange.FormulaR1C1 = "=IFERROR(MATCH(""Total for INV ""&RC[1]&""*"",'[" & costCodeWorkbook.Name & "]cost code'!C2,0),""DELETE"")"
            .Range("A6:G6").AutoFilter field:=1, Criteria1:="DELETE"
            
            If formulaRange.SpecialCells(12).Count > 0 Then formulaRange.SpecialCells(12).EntireRow.Delete shift:=xlUp
            
            .AutoFilterMode = False
            
                With .Cells(.Rows.Count, 4).End(xlUp).Resize(1, 3)
                    .FormulaR1C1 = "=SUM(R7C:R[-1]C)"
                    .Value = .Value
                End With
            
            .Columns(1).EntireColumn.Delete shift:=xlToLeft
        End With
        
        costCentreWorkbook.Close SaveChanges:=True
        
        Set costCentreWorkbook = Nothing
        
        costCentreWorkbookName = Dir()
    Wend


costCodeWorkbook.Close SaveChanges:=False


Set costCodeWorkbook = Nothing


Application.ScreenUpdating = True


MsgBox "Check complete.", vbInformation


End Sub


Private Function FindWorkbook() As String
    tempString$ = Application.GetOpenFilename("Excel Files(*.xls*), *.xls*")
    FindWorkbook = IIf(CStr(UCase(tempStr)) = "FALSE", vbNullString, tempString)
End Function


Private Function GetFolder() As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = CurDir$
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function

Display More

fess67 · Apr 28th 2015

Re: AUD $100 - Compare columns in two workbooks and remove row if match is negative

Hi S O,

I have run the code. It does not do as expected in that no rows are deleted from the 'target' workbook, in this case 222222.

I guess I need to qualify that I am operating it as you intended so, some questions:

Which workbooks should I have open? cost code, 222222 or none?
Which workbook should be the active one when the code is run?

When I ran the code it asked me to select a folder, then to select a file then it ran and gave a message box "Check complete". When I checked the files I saw no change to either file.

Cheers

**S O** · Apr 28th 2015

Re: AUD $100 - Compare columns in two workbooks and remove row if match is negative

Sorry, you are right I was missing basic instructions!

Quote

Which workbooks should I have open? cost code, 222222 or none?

You don't need any workbooks to be open when this is run.

Quote

Which workbook should be the active one when the code is run?

The code refers to the workbook objects directly, so there is no need to activate.

So I had only the "cost code.xlsx" workbook open to run this code. Then the steps that follow should be:

1. Code asks you to browse for a folder, this should be the folder containing all of the cost centre reports (the code should loop through all files in this folder and execute the macro on them)

2. If a workbook called "cost code.xlsx" is already open, this is set as the cost code workbook.
2a. If it's not found, the code will prompt you to manually browse and select the workbook to use as the cost code workbook

3. Code does required lookups and deletes rows/updates totals in cost centre report, then closes and opens the next file in the folder until all files have been processed.

4. Code closes both workbooks on final run and prompts to say check has been completed.

fess67 · Apr 29th 2015

Re: AUD $100 - Compare columns in two workbooks and remove row if match is negative

Thanks

I have tried again with the same result in that it runs but does not remove the unmatched entries. My gut feel is there is an issue with the if(match( statement. It would appear that the identification, filtering and deletion of required entries is not working. I tried stepping through the code with ScreenUpdating off to see if I could figure out where it might be going wrong but that part of it just loops to the end. Of course I may be way off track as well

Referring to the files supplied in the original post this is what should happen when the code is run:

in file '222222' - all rows from 7 to 136 should be deleted.
- This is because the first match is invoice 18872 in A137 of '222222' - found in 'cost code' B23 or B25 (looks like your code references B25 but either works.)
in file '222222' - the next row to be deleted is row 153
- This is because invoice 19340 is not present in col B of the 'cost code' file.

Is that how it is working at your end?

Cheers

**S O** · Apr 29th 2015

Re: AUD $100 - Compare columns in two workbooks and remove row if match is negative

I'd forgotten a crucial part of the Dir() function! I wrote this on a mac and until now have not been able to test properly as I didn't have my windows machine available. I've tweaked it to the following and appears to be working:

Code

Sub SO()
     
    Dim costCentreWorkbookName  As String
    Dim folderLocation          As String
    Dim costCentreWorkbook      As Excel.Workbook
    Dim costCodeWorkbook        As Excel.Workbook
    Dim formulaRange            As Excel.Range
     
    Application.ScreenUpdating = False
     
    folderLocation = GetFolder()
     
    Set costCodeWorkbook = Workbooks.Open(FindWorkbook)
     
    costCentreWorkbookName = Dir(folderLocation & "\*.*", vbNormal)
     
    While Not costCentreWorkbookName = vbNullString
         
        Set costCentreWorkbook = Workbooks.Open(folderLocation & "\" & costCentreWorkbookName)
         
        With costCentreWorkbook.Sheets(1)
            .Columns(1).EntireColumn.Insert Shift:=xlToRight
             
            Set formulaRange = .Range("B8:B" & .Cells(.Rows.count, 3).End(xlUp).Row).Offset(-1, -1)
             
            formulaRange.FormulaR1C1 = "=IFERROR(MATCH(""Total for INV ""&RC[1]&""*"",'[" & costCodeWorkbook.Name & "]cost code'!C2,0),""DELETE"")"
            .Range("A6:G6").AutoFilter field:=1, Criteria1:="DELETE"
             
            On Error Resume Next
                If formulaRange.SpecialCells(12).count > 0 Then formulaRange.SpecialCells(12).EntireRow.Delete Shift:=xlUp
                Err.Clear
            On Error GoTo 0
            
            .AutoFilterMode = False
             
            With .Cells(.Rows.count, 4).End(xlUp).Resize(1, 3)
                .FormulaR1C1 = "=SUM(R7C:R[-1]C)"
                .Value = .Value
            End With
             
            .Columns(1).EntireColumn.Delete Shift:=xlToLeft
        End With
         
        costCentreWorkbook.Close SaveChanges:=True
         
        Set costCentreWorkbook = Nothing
         
        costCentreWorkbookName = Dir()
    Wend
     
    costCodeWorkbook.Close SaveChanges:=False
     
    Set costCodeWorkbook = Nothing
     
    Application.ScreenUpdating = True
     
    MsgBox "Check complete.", vbInformation
     
End Sub
 
Private Function FindWorkbook() As String
    MsgBox "Please select the cost cose workbook.", vbInformation
    tempString$ = Application.GetOpenFilename("Excel Files(*.xls*), *.xls*")
    FindWorkbook = IIf(CStr(UCase(tempStr)) = "FALSE", vbNullString, tempString)
End Function
 
Private Function GetFolder() As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .TITLE = "Select folder containing cost centre reports"
        .AllowMultiSelect = False
        .InitialFileName = CurDir$
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem
    Set fldr = Nothing
End Function

Display More

fess67 · Apr 29th 2015

Re: AUD $100 - Compare columns in two workbooks and remove row if match is negative

Run time error 1004

formulaRange.FormulaR1C1 = "=IFERROR(MATCH(""Total for INV ""&RC[1]&""*"",'[" & costCodeWorkbook.Name & "]cost code'!C2,0),""DELETE"")"

[ATTACH=CONFIG]65008[/ATTACH]

**S O** · Apr 29th 2015

Re: AUD $100 - Compare columns in two workbooks and remove row if match is negative

Hi fess67

Is this happening when using the 222222.xlsx workbook? Also - do you actually use commas as the delimiter for formulas or semi-colons?

fess67 · Apr 30th 2015

Re: AUD $100 - Compare columns in two workbooks and remove row if match is negative

Yes the 222222 workbook.

Sorry I do not understand the comment about commas or semi-colons.

Here is the workflow I am trying:

Open 'cost code' spreadsheet. It is in the same folder as the '222222' There is also another sheet there called '40405'
Open the VBA project through the Developer tab in Excel
Run the code
It firsts asks me to select the folder
I am then asked to select the 'cost cose (sic) workbook' - I take this to mean cost code. I select that and I get the run time error.
I tried it by opening '222222' in step 5. Still get run time error
I tried running it with no sheet open - same result.
The only things in the folder are 'cost code', '222222', and '40405'.

I am happy to open the worksheet manually and run the code on each individual one if that will help.

Thanks

**S O** · Apr 30th 2015

Re: AUD $100 - Compare columns in two workbooks and remove row if match is negative

OK, if the cost code workbook is already open it will cause an error. Also the code is designed to go through the folder under the assumption that only the cost centre worksheets are in there. I'll make a couple more tweaks to handle those issues and get it back over to you shortly

**S O** · May 1st 2015

Re: AUD $100 - Compare columns in two workbooks and remove row if match is negative

Hi fess67,

Try having all workbooks closed, then use this to run the code instead: forum.ozgrid.com/index.php?attachment/65043/

Hopefully that resolves the issues!

fess67 · May 4th 2015

Re: AUD $100 - Compare columns in two workbooks and remove row if match is negative

Still getting the run time error

formulaRange.FormulaR1C1 = Replace("=IFERROR(MATCH(""Total for INV ""&RC[1]&""*"",'[" & costCodeWorkbook.Name & "]cost code'!C2,0),""DELETE"")", ",", Application.International(xlListSeparator))
.Range("A6:G6").AutoFilter field:=1, Criteria1:="DELETE"

This happens when I press the GO button after selecting the cost code sheet and folder in steps 1 and 2. The cost code sheet is in the parent folder and the cost centre sheets are in a sub folder. There are no other files in the sub folder other than 2 cost centre sheets

**S O** · May 4th 2015

Re: AUD $100 - Compare columns in two workbooks and remove row if match is negative

okay, I can't seem to replicate this using the samples you provided - can you send me the actual file that you are using when the error occurs?

fess67 · May 5th 2015

Re: AUD $100 - Compare columns in two workbooks and remove row if match is negative

Well, I think we are getting somewhere BUT it has me beat as well - Let me explain

I can get it to work. It works with one of the attached cost code files (cost code test) but does not work with the other file (newSave)

I am not aware of having done anything different with cost code test. However, the newSave file is how it comes from our reporting system. Whatever I did to create that I need to try to replicate. I can see no difference in the two files.

Is there a way I can PM you the full files rather than post them publically - Then I can give you the full original rather than stripping info?

AUD $100 - Compare columns in two workbooks and remove row if match is negative

Participate now!

Share