Hello,
i am working on trying to automate one of the tasks that i have to complete at my job,
this consists of matching the charge rate on our system to a list of charge rates in a database,
the old way of doing this works but is very manual and takes a long time to complete.
the new way that i have come up with is one formula that indexes a unique code on another worksheet dependant on the variable listed below.
firstly needs to only return a direct match for Trust Code (which is a 6 digit code for each NHS Trust)
secondly the formula needs to find the closest charge rate to the rate on the invoice and return the unique code.
i have got the above formula to work but have realised the formula still needs to include a match for the description of the shift (E.G Day, Night, Saturday or Sunday)
all of these codes are on the Trust Rates Tab on the attached spreadsheet where i have created a version of the spreadsheet i am working on but without any of the sensitive data that im not allowed to share.
i have also included different variations of formulas that i have tried along with annotations to the spreadsheet to try and give as much information as possible in the hope someone can help me solve this problem
Unfortunately i am unable to attach anything to this forum so i have attached the document on the below forum if you could follow the link you will be able to see the example spreadsheet.
https://www.excelforum.com/excel-fo...est-match-on-a-third-criteri.html#post5218796
Thank you
i am working on trying to automate one of the tasks that i have to complete at my job,
this consists of matching the charge rate on our system to a list of charge rates in a database,
the old way of doing this works but is very manual and takes a long time to complete.
the new way that i have come up with is one formula that indexes a unique code on another worksheet dependant on the variable listed below.
firstly needs to only return a direct match for Trust Code (which is a 6 digit code for each NHS Trust)
secondly the formula needs to find the closest charge rate to the rate on the invoice and return the unique code.
i have got the above formula to work but have realised the formula still needs to include a match for the description of the shift (E.G Day, Night, Saturday or Sunday)
all of these codes are on the Trust Rates Tab on the attached spreadsheet where i have created a version of the spreadsheet i am working on but without any of the sensitive data that im not allowed to share.
i have also included different variations of formulas that i have tried along with annotations to the spreadsheet to try and give as much information as possible in the hope someone can help me solve this problem
Unfortunately i am unable to attach anything to this forum so i have attached the document on the below forum if you could follow the link you will be able to see the example spreadsheet.
https://www.excelforum.com/excel-fo...est-match-on-a-third-criteri.html#post5218796
Thank you
