Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,551
    Thanks
    145
    Thanked 7 Times in 7 Posts

    Extract Account numbers based on certain criteria

    I have a source workbook called "Data Account number Import.xlsm " (Sheet “Data import”)

    I need to extract the account numbers in Col G and paste these in Col M on workbook ‘Sales by Branch.xlsm” based on the following criteria


    1) (positive values) -The ref No. in Col A and the value in Col J (positive values) “Data Account number Import.xlsm” must match the Ref No. in Col G and the value in Col I (positive values) and Col H must not be zero
    2) Negative values --The ref No. in Col A and the value in Col K “Data Account number Import.xlsm” (The values in col K, although not shown as negative are all negative as the column heading is called “Credit” ) must match the Ref No. in Col G and the value in Col I (negative Values), and Col H must not be zero



    I have attached sample data files and also shown the manual result in Col N

    The current code is extracting some of the account numbers, based on the above criteria but there is a few not being extracted which I have highlighted

    It would be appreciated if someone would test my macro on workbook “Sales by Branch.xlsm” based on the above criteria and amend my code accordingly

    I have also posted on the link below today


    https://www.ozgrid.com/forum/forum/h...rtain-criteria
    Attached Files Attached Files
    Last edited by HowardC; 2018-07-10 at 21:41.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,992
    Thanks
    157
    Thanked 780 Times in 710 Posts
    Howard,

    Looks like you have duplicate values in Column J in the Data Account number Import workbook. You are using the Find method for the J:J column which finds only the first instance of the value. The first instance in the column has a reference that is different between the two workbooks.

    Using your first occurrence of an error in line 6 of the Sales by Branch workbook, the the value of 7756.938 has a reference of KL-661, whereas the Find method resulted line 3 in J:J for the same value in the Data Account number Import workbook but the reference on that line is KL-2661. Therefore, the account number is not copied over to column N.

    However, If the Find Method found the second occurrence of 7756.938 on line 15, the reference numbers would have matched. This is either a case of being a little clumsy when setting up some test data or you need to search in a column with unique values.

    HTH,
    Maud

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,551
    Thanks
    145
    Thanked 7 Times in 7 Posts
    Hi Maud

    Thanks for the reply. One can have instances where the values are the same

    The criteria is a follows

    1) Where the account no. in G and the positive value in Col I in "Sales by Branch.xlsm" sheet "Imported Data" matches the ref no in Col A and the Value in Col J on workbook "Data Account number Import.xlsm" sheet "Data Import" and the value in Col H on workbook "Sales by Branch.xlsm" sheet "Imported Data" is not zero, then the account number from Col G is to be copied from "Data Account number Import.xlsm" sheet "Data Import" and pasted in Col M in the destination workbook in the same row as the ref no. and value that matches
    2) Where the account no. in G and the negative value in Col I in "Sales by Branch.xlsm" sheet "Imported Data" matches the ref no in Col A and the Value in Col K (these are shown as positive values , but they are in a column with the heading credit and are credit balances although shown as positive, hence the use of ABS in my code) on workbook "Data Account number Import.xlsm" sheet "Data Import" and the value in Col H on workbook "Sales by Branch.xlsm" sheet "Imported Data" is not zero, then the account number from Col G is to be copied from "Data Account number Import.xlsm" sheet "Data Import" and pasted in Col M in the destination workbook in the same row as the ref no. and value that matches


    It would be appreciated if you would kindly amend my code regarding the above, as it is beyond my VBA knowledge

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,992
    Thanks
    157
    Thanked 780 Times in 710 Posts
    Hi Howard,

    My response in post 2 indicated why your code fails. It is because the find method will find the first occurrence in a range. Since your values in the search range are not unique, you are always using the first occurrence as the row for comparison.

    The easiest solution would be to loop through each row in the Sales by Branch workbook and compare the criteria with each row in the Data Account number Import workbook using an inner loop. If all the criteria match then copy over the account number from Col G.

    Another solution would be to use the FindNext method when searching down column J but is a little more intense but faster execution.

    Maud

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,551
    Thanks
    145
    Thanked 7 Times in 7 Posts
    Thanks for your input Maud

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,992
    Thanks
    157
    Thanked 780 Times in 710 Posts
    Sure Howard. Let me know if you need help with that.

    Maud

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,551
    Thanks
    145
    Thanked 7 Times in 7 Posts
    Would appreciate it, it you could amend my code to do as what you recommended

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,992
    Thanks
    157
    Thanked 780 Times in 710 Posts
    Hi Howard,

    Replace your code with the following

    Code:
    Sub Extract_Account_Numbers()
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    '--------------------------------------
    'DECLARE AND SET VARIABLES
    Dim sh1 As Worksheet, sh2 As Worksheet
    Set sh1 = Workbooks("Sales by Branch_Rev1.xlsm").Sheets("Imported Data")
    Set sh2 = Workbooks("Data Account number Import.xlsm").Sheets("Data Import")
    '--------------------------------------
    'GET LAST ROW OF EACH WORKBOOK
    LastRow = sh1.Cells(Rows.Count, "I").End(xlUp).Row
    EndRow1 = sh2.Cells(Rows.Count, "J").End(xlUp).Row
    Endrow2 = sh2.Cells(Rows.Count, "J").End(xlUp).Row
    EndRow = WorksheetFunction.Max(EndRow1, Endrow2)
    '--------------------------------------
    'LOOP THROUGH RECORDS ON BOTH SHEETS
    For I = 2 To LastRow
        For J = 2 To EndRow
    '--------------------------------------
    'CRITERIA 1: COL I VALUE IS POSITIVE
            If sh1.Cells(I, "I") > 0 Then
                If sh1.Cells(I, "G") = sh2.Cells(J, "A") And _
                   sh1.Cells(I, "I") = sh2.Cells(J, "J") And _
                   sh1.Cells(I, "H") <> 0 Then
                      sh1.Cells(I, "M") = sh2.Cells(J, "G")
                      GoTo nextrecord
                End If
    '--------------------------------------
    'CRITERIA 1: COL I VALUE IS NEGATIVE
            Else
                If sh1.Cells(I, "G") = sh2.Cells(J, "A") And _
                   Abs(sh1.Cells(I, "I")) = sh2.Cells(J, "K") And _
                   sh1.Cells(I, "H") <> 0 Then
                      sh1.Cells(I, "M") = sh2.Cells(J, "G")
                      GoTo nextrecord
                End If
            End If
        Next J
    nextrecord:
    Next I
    '--------------------------------------
    'CLEANUP
    Set sh1 = Nothing
    Set sh2 = Nothing
    '--------------------------------------
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    End Sub
    HTH,
    Maud

  9. The Following User Says Thank You to Maudibe For This Useful Post:

    HowardC (2018-07-15)

  10. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,551
    Thanks
    145
    Thanked 7 Times in 7 Posts
    Thanks Maud

    Your code works perfectly

  11. #10
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,551
    Thanks
    145
    Thanked 7 Times in 7 Posts
    Hi Maud

    Thanks once again for the help

    I have tested some more data and have the following additional spec's

    The Source File is BR1 Raw data 1.1.xlsx and destination file is Sales Data Report.xlsm



    1) Where the ref no. in Col G matches Col A (Source file) and Value in Col I is positive and value in Col H <> 0 and there is no match in value for same ref, then find the value in Col J that matches Col H that is a few rows above it and extract the Account no. from Col G in same row and paste it in Col M
    2) Where the ref in Col G (destination File) does not Match the Ref No. in Col A (Source File) and the value in Col I is positive and col H <> 0 then find value in Col J that is the closest to it and extract the account number in Col G and paste it in Col M (destination file)

    3) Where the ref in Col G (destination File) does not Match the Ref No. in Col A (Source File) and the value in Col I is negative and col H <> 0 then find value in Col K that is the closest to it and extract the account number in Col G and paste it in Col M (destination file)

    4) Where the ref No. appears as a date in Col G (destination file) for eg 16-Oct (is actually 16/10/2018) , then exclude the year when matching ref No.to Col A in source file and then match the Value in Col I if positive and value in Col H <> 0 with Value in Col J and extract account no. from Col G in same row as match and paste in Col M (source File)

    5) Where the ref No. appears as a date in Col G (destination file) for eg 16-Oct (is actually 16/10/2018) , then exclude the year when matching ref No.to Col A in source file and then match the Value in Col I if negative and value in Col H <> 0 with Value in Col K and extract account no. from Col G in same row as match and paste in Col M (source File)




    I have attached some more sample data


    It would be appreciated if you could assist me in resolving this
    Attached Files Attached Files
    Last edited by HowardC; 2018-07-17 at 20:22.

  12. #11
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    1,151
    Thanks
    100
    Thanked 135 Times in 120 Posts
    It is time that you paid Maud for developing the software with which you run your business !

    Just sayin'

  13. #12
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,551
    Thanks
    145
    Thanked 7 Times in 7 Posts
    Have no problem with that

  14. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,992
    Thanks
    157
    Thanked 780 Times in 710 Posts
    Martin,
    You are too funny!

    Howard,
    Are you using 24bit encryption in the body of your posts? You have my head spinning!

    Using my previous code as a template, it should be fairly within your scope to amend the code. Give it a go and we will be happy to help if you encounter an obstacle.

    Maud
    Last edited by Maudibe; 2018-07-18 at 07:59.

  15. #14
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,551
    Thanks
    145
    Thanked 7 Times in 7 Posts
    Will attempt to amend the code 7 let you know if i need further help

  16. #15
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,551
    Thanks
    145
    Thanked 7 Times in 7 Posts
    Hi Maud

    Have had a look at your code and it makes sense to me. However, the additional spec is too difficult for me to write write the code to do what I want it to do


    It would be appreciated if you could kindly write this for me


    Thanks


    Howard

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •