Excel VBA Searching two documents for prices against order references with criteria
Hey Guys
Hope you can help
Ive been asked to come with a way to price check invoices against what we have on our purchase order system
I have a report that pulls from our system with the prices that have been entered on there as well as the invoice from the supplier with their prices.
In theory, they should match but we are having some issues
Ive been trying to find a way in which to paste the internal report into Sheet 1 and the invoice into Sheet 2 and on Sheet 1, against the PO references, pull the prices from the invoice on Sheet 2.
I have tried INDEX and MATCHING, LOOK UP and ABS references but the problems I have are two-fold:-
1) The formatting can change in the way the purchase number is displayed on the invoice – some have the PO012345 and some have just the last five digits. This would mean that the solution would have to search using a wild card plus the unique last five digits across the two sheets.
2) Not all the invoices are the same in the way in which they are sent therefore, I have been trying to find a way to search the WHOLE sheet and not just a certain column etc, which has proven unsuccessful
I have attached a sample sheet. Sheet 1 has our internal report, Sheet 2 has the supplier invoice.
In column O, I have manually entered what should be the result of the formula / VBA. From this example you can see that O27 and O35 would not match and therefore would flag an anomaly between the two documents.
Ideally if there is anyone can suggest some VBA for this as it would make it easier for me to just paste in the two documents but any help would be greatly appreciated.
J
EE_Example_210617.xls
Hope you can help
Ive been asked to come with a way to price check invoices against what we have on our purchase order system
I have a report that pulls from our system with the prices that have been entered on there as well as the invoice from the supplier with their prices.
In theory, they should match but we are having some issues
Ive been trying to find a way in which to paste the internal report into Sheet 1 and the invoice into Sheet 2 and on Sheet 1, against the PO references, pull the prices from the invoice on Sheet 2.
I have tried INDEX and MATCHING, LOOK UP and ABS references but the problems I have are two-fold:-
1) The formatting can change in the way the purchase number is displayed on the invoice – some have the PO012345 and some have just the last five digits. This would mean that the solution would have to search using a wild card plus the unique last five digits across the two sheets.
2) Not all the invoices are the same in the way in which they are sent therefore, I have been trying to find a way to search the WHOLE sheet and not just a certain column etc, which has proven unsuccessful
I have attached a sample sheet. Sheet 1 has our internal report, Sheet 2 has the supplier invoice.
In column O, I have manually entered what should be the result of the formula / VBA. From this example you can see that O27 and O35 would not match and therefore would flag an anomaly between the two documents.
Ideally if there is anyone can suggest some VBA for this as it would make it easier for me to just paste in the two documents but any help would be greatly appreciated.
J
EE_Example_210617.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I found an error in my above formula that wasn't allowing it to find the correct PO Number every time. I have corrected it here:
IFERROR(IF(VLOOKUP(RIGHT(TRIM(B9),5),SearchPlace,10,TRUE)=K11,"Good","Bad"),"Bad")ASKER
HI Neil
Your example is superb and the way it works is exactly what I need - cant thank you enough for your help
Just one thing, would it be possible to modify the code so it just cross checks the UNIT prices not the subtotal as our system has the option to over or under receipt (for example, the purchase order may say 500 units but the supplier may send 505 if they have overproduced on a run to use up left over material)
Therefore, its the UNIT prices that I need to cross check - is there a way to modify the code to do this?
Your help is much appreciated Neil - thank you
J
Your example is superb and the way it works is exactly what I need - cant thank you enough for your help
Just one thing, would it be possible to modify the code so it just cross checks the UNIT prices not the subtotal as our system has the option to over or under receipt (for example, the purchase order may say 500 units but the supplier may send 505 if they have overproduced on a run to use up left over material)
Therefore, its the UNIT prices that I need to cross check - is there a way to modify the code to do this?
Your help is much appreciated Neil - thank you
J
ASKER
Thanks Neil
Sorry, Jane. Have been travelling for the past 10 days and only just seen this. Will take a look re the unit prices.
This version cross checks the unit prices instead of the total.
invoice_Example_210617-UNIT-PRICES.xlsm
invoice_Example_210617-UNIT-PRICES.xlsm
ASKER
Hey Neil
No worries
Thank you so much for responding - it looks perfect !!
J
No worries
Thank you so much for responding - it looks perfect !!
J
Finding the last 5 digits is not a problem, you can use a simple RIGHT(B11,5) to get that.
On the sample sheets, all the PO numbers start with "PO" so I don't see the problem anyhow.
What are you wanting to compare the total price or the price per quantity? The difference in the price per quantity is in the number of decimals (precision) being shown. If the supplier invoice would show to the same precision it might be the same. If it isn't then you will definitely need to work with them to have them use the proper precision on their invoicing.
Also, there is POo46409 which you have on the first sheet as having a price of 4.140 when on the Sheet1 it has 2 prices of 2.07. These don't match either. Is there a way to distinguish between them or are they supposed to be on the same invoice? If they are separate invoices then your system having a price of 4.14 while their system has a price of 2.07 is not correct. Both systems should use the same price and doing a compare on a doubled price is not accurate.
You can use a formula like this to do the compare without any need for VBA (as long as the format is the same):
Open in new window
I should qualify this by saying that I created a named range on Sheet1. I also inserted another column that would hold the last 5 digits of the PO for the named range.