matching excel row data to filename based on matching data left of underscore
Hoping somebody can help with what I believe can be done either using Power Query or VBA.
I have an Excel file that includes thousands of entries – each entry has an object number and SOME objects will have an associated file on the hard drive (or SharePoint / remote server). I would like the script to be able to review the data from the file name left of the first underscore to match with the object number and record the filename in the correct field “demo file path” in the attached sample file. E.g. object number 123 is related to file 123_has-extra_from_3733-AREA_RULE_Progress_Report_Feb._231955.pdf
I am OK if the system needs to place a message in the related entry that states “no match found”, it is a bonus but not required.
Hopefully with the sample data that includes a second tab of the file that are in the existing folder and the above details somebody can help out.
If the solution cannot read directly from a specific folder / site and needs a worksheet with all the file names, I can easily work with that solution.
Thank you.
Sample File Hoping somebody can help with what I believe can be done either using Power Query or VBA. I have an Excel file that includes thousands of entries – each entry has an object number and SOME objects will have an associated file on the hard drive (or SharePoint / remote server). I would like the script to be able to review the data from the file name left of the first underscore to match with the object number and record the filename in the correct field “demo file path” in the attached sample file. E.g. object number 123 is related to file 123_has-extra_from_3733-AREA_RULE_Progress_Report_Feb._231955.pdf I am OK if the system needs to place a message in the related entry that states “no match found”, it is a bonus but not required. Hopefully with the sample data that includes a second tab of the file that are in the existing folder and the above details somebody can help out. If the solution cannot read directly from a specific folder / site and needs a worksheet with all the file names, I can easily work with that solution. Thank you. Read More
