Forum Discussion

Infoman206's avatar
Infoman206
Copper Contributor
Jun 11, 2024

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

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.

Excel
Formulas and Functions
Macros and VBA
Office Scripts
  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Jun 11, 2024

    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);

     

     

    • Infoman206's avatar
      Infoman206
      Copper Contributor
      Jun 11, 2024
      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?

Resources