SOLVED

matching excel row data to filename based on matching data left of underscore

Copper Contributor

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.

6 Replies

@Infoman206 

If you have extracted name left of the first underscore,you can try MS Query join.

Alternatively,

online sql:

 

create temp table aa as
select f03,regexp2('^\d+',f03) 'fileId' from consolidateSheet where f02 like 'filename-values';
create temp table bb as
select f03,f04 fileId,f05 from consolidateSheet where f02 like 'AM-Object_List_MASTER';
select bb.*,aa.f03 `demo file path`
from bb left join aa using(fileId);

 

 

Screenshot_2024-06-11-17-02-12-250_com.mmbox.xbrowser.pro.jpg

Thank you for the quick feedback! You definitely have the right track, ideally I'd like to avoid having to export everything into anything external as we have several fields highlighted for different purposes and hate to lose that. I'm able to pull the number from the file name using "=LEFT(A3,FIND("_",A3)-1)" but I've never used the MS Query Join within Excel, could you point me in the right direction?
best response confirmed by Infoman206 (Copper Contributor)
Solution
If you like formular,I guess xlookup also available.
=xlookup(b2,filename-values!b2:b60,file-values!a1:a60)


https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
it took a bit of playing around but it looks like xLookup is just what I needed with the data formatted how I currently have it. Thank you so much for your help!
1 best response

Accepted Solutions
best response confirmed by Infoman206 (Copper Contributor)
Solution
If you like formular,I guess xlookup also available.
=xlookup(b2,filename-values!b2:b60,file-values!a1:a60)


https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929

View solution in original post