VBA to extract account number where ref and amount matches

I retested the data and need a change that I overlooked when advising you what my requirements were

The only change is where Col H in the workbook "sales by region.xls has a zero value , even where there is a match in my requirements in Post # 1, then in that instance no account number to be extracted

I have attached my workbook, showing what the final result should look like-see col L "manual result. I have also tried to explain below in some detail





where the values in Col H is zero in the workbook "sales by region.xls" on sheets "imported data , and the ref in Col G and the positive Value in Col I matches the Ref in Col A and Value in Col J in the the workbook "Data Sales Import.xlsm", then the account number must not be extracted

The same will apply to the negative values i.e where the values in Col H is zero in the workbook "sales by region.xls" on sheets "imported data , and the ref in Col G and the and the negative Value in Col I matches the Ref in Col A and Value in Col K in the the workbook "Data Sales Import.xlsm", then the account number must not be extracted


Your assistance in amending the code is most appreciated






Where the ref no. in Col G and values in Col I on Workbook "sales by region.xlsm" matches

thanks for the help and for indicating the change in red

I have retested the code with some additional data and the account numbers highlighted in yellow in the column N "Manual Extraction" are not being extracted


Please test & kindly amend your code

I am a friend of Flupsie. He advised me to let you know on his behalf that your amended code is working perfectly.

He is unable to access this site for some unknown reason. Je jhas emailed the administrators and waiting for a reponse

Hi JLJ

Flupsie is still unable to access the Forum and is getting an IT person to come over this week to come have a look

He said what you have done so far is working 100%, but when testing another workbook he found a few exceptions to his spec and he neds the code to be amended based on the following


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 (destination File)
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)


He said when you get a chance, if you kindly amend the code per the above. If too comlex, then he said he wil manually have to extractthe account number for the additional items where the acciunt number is not being automatically extracted

Fully Under JLG