Forum Discussion
Solved
matching excel row data to filename based on matching data left of underscore
https://avroland.ca/museum/Working_5C_test-for-Excel_scripts1.xlsx Hoping somebody can help with what I believe can be done either using Power Query or VBA. I have an Excel file that includes...
- 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
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);
- 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?
- 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!
