help matching & joining

[joeloveszoe]

hi
i have a column of item numbers and another column of image names that include the item number

i am looking for an easy way to match the image to the item number

1 identify the main image in one cell (the main image name will contain '001'
2 combine the additional image names/location into a 2nd cell

the images are in a folder /Images/Shop-Vac/ - images names should be separate by ';'

any help to make this easy would be so greatly appreciated

thanks for helping!!!

 

[Kaper]

Try in C2:

=LET(a,FILTER("/Images/Shop-Vac/"&B:B,LEFT(B:B,8)=A2&"_"),b,SORT(HSTACK(RIGHT(a,7),a),,1),HSTACK(INDEX(b,1,2),TEXTJOIN(";",,DROP(b,1,1))))

so data is filtered to start with 7digit SKU and underscore (a)
then is stacked horizontally with just number and jpg and sorted on it (b)
and finally first elementy is placed in 1st cell and in second all but 1st are joined with semicolon as separator

Hope it does the job

 

[joeloveszoe]

Try in C2:

=LET(a,FILTER("/Images/Shop-Vac/"&B:B,LEFT(B:B,8)=A2&"_"),b,SORT(HSTACK(RIGHT(a,7),a),,1),HSTACK(INDEX(b,1,2),TEXTJOIN(";",,DROP(b,1,1))))

so data is filtered to start with 7digit SKU and underscore (a)
then is stacked horizontally with just number and jpg and sorted on it (b)
and finally first elementy is placed in 1st cell and in second all but 1st are joined with semicolon as separator

Hope it does the job

amazing!!! and soooo helpful, thnak you
when i drag the formula down - i notice it missed some matching - possibly bc the image file names are not consistent
any thoughts how to grab these too - highlighter in orange as an example

 

[Kaper]

Indeed, the sample was not representative.

In the orange cells in column B there are (mostly) underscore missed between SKU and the rest of file name.
Possible modification could look at first seven characters and compare with SKU and on last 4 and compare with ".jpg"

=LET(a,FILTER("/Images/Shop-Vac/"&B:B,(LEFT(B:B,7)=A2)*(RIGHT(B:B,4)=".jpg")),b,SORT(HSTACK(RIGHT(a,7),a),,1),HSTACK(INDEX(b,1,2),TEXTJOIN(";",,DROP(b,1,1))))

 

[joeloveszoe]

super, thanks very much for your help!!!