trying to match

J

joeloveszoe

Board Regular
Joined
Apr 24, 2014
Messages
102
Office Version
  1. 365
Platform
  1. MacOS
hi!
i have a ust of part numbers that generally look like this
manufacturerpartnumber​
10180​
10182​
10183​
10184​
10185​
10186​
10187​
10189​
10201​
10202​
10203​
10204​
10205​
10262​
10445​
10767​
10768​
10769​
3032-PST​
3047-GLE​

trying to match to a list of images that contain the part number in the image name

10180_1.jpg
10180_2.jpg
10180_3.jpg
10182_1.jpg
10182_2.jpg
10182_3.jpg
10182_4.jpg
10183_Calendar_StickerTabletPack.jpg
10183_CalendarA5StickerTablet_WEB.jpg
10184_1.jpg
10184_2.jpg
10184_3.jpg
10185_Week to View_inserts_packaging.jpg
10185_Week to View_inserts.jpg
10201.MAIN.jpg
10201.PS1.jpg
10201.PS2.jpg
10201.PS3.jpg
10201.PS4.jpg
10201.PS5.jpg
10202_WeeklyCovers.jpg
10202_WeeklyOpen.jpg
10203_DailyCovers.jpg
10203_DailyOpen.jpg
10204.MAIN.jpg
10204.PS1.jpg
10204.PS2.jpg
10204.PS3.jpg
10204.PS4.jpg
10204.PS5.jpg
10205_Back.jpg
10205_Main.jpg
10205_PS1.jpg
10205_PS2.jpg
10205_PS3.jpg
10205_PS4.jpg
10205_PS5.jpg
10262_GoodVibesA5StickerTablet_WEB.jpg
10263_HelloA5StickerTablet_WEB.jpg
10769_Main.jpg
10769_PT01.jpg
10769_PT02.jpg
10769_PT03.jpg
10769_PT04.jpg
10769_PT05.jpg
3032(BE)-PST - Pastel B5 Blue Inside Pages.jpg
3032(BE)-PST - Pastel B5 Blue Pocket.jpg
3032(BE)-PST - Pastel B5 Blue Waterproof.jpg
3032(BE)-PST_02.jpg
3032(PK)-PST - Pastel B5 Pink Inside Pages.jpg
3032(PK)-PST - Pastel B5 Pink Pocket.jpg
3032(PK)-PST - Pastel B5 Pink Waterproof.jpg
3032(PK)-PST_01.jpg

can i do an XMATCH or some matching function to look for the part number in the image name ?-
for example
find '10180'
to get these results, in separate cells ...
10180_1.jpg
10180_2.jpg
10180_3.jpg

and aslo find when the image name is different
so find 10183 and match with these image names
10183_Calendar_StickerTabletPack.jpg
10183_CalendarA5StickerTablet_WEB.jpg

thanks so much!!!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
How about
Fluff.xlsm
ABCDEFGHIJ
1manufacturerpartnumber
21018010180_1.jpg10180_2.jpg10180_3.jpg10180_1.jpg
31018210182_1.jpg10182_2.jpg10182_3.jpg10182_4.jpg10180_2.jpg
41018310183_Calendar_StickerTabletPack.jpg10183_CalendarA5StickerTablet_WEB.jpg10180_3.jpg
51018410184_1.jpg10184_2.jpg10184_3.jpg10182_1.jpg
61018510185_Week to View_inserts_packaging.jpg10185_Week to View_inserts.jpg10182_2.jpg
710186 10182_3.jpg
810187 10182_4.jpg
910189 10183_Calendar_StickerTabletPack.jpg
101020110201.MAIN.jpg10201.PS1.jpg10201.PS2.jpg10201.PS3.jpg10201.PS4.jpg10201.PS5.jpg10183_CalendarA5StickerTablet_WEB.jpg
111020210202_WeeklyCovers.jpg10202_WeeklyOpen.jpg10184_1.jpg
121020310203_DailyCovers.jpg10203_DailyOpen.jpg10184_2.jpg
131020410204.MAIN.jpg10204.PS1.jpg10204.PS2.jpg10204.PS3.jpg10204.PS4.jpg10204.PS5.jpg10184_3.jpg
141020510205_Back.jpg10205_Main.jpg10205_PS1.jpg10205_PS2.jpg10205_PS3.jpg10205_PS4.jpg10205_PS5.jpg10185_Week to View_inserts_packaging.jpg
151026210262_GoodVibesA5StickerTablet_WEB.jpg10185_Week to View_inserts.jpg
1610445 10201.MAIN.jpg
1710767 10201.PS1.jpg
1810768 10201.PS2.jpg
191076910769_Main.jpg10769_PT01.jpg10769_PT02.jpg10769_PT03.jpg10769_PT04.jpg10769_PT05.jpg10201.PS3.jpg
203032-PST3032(BE)-PST - Pastel B5 Blue Inside Pages.jpg3032(BE)-PST - Pastel B5 Blue Pocket.jpg3032(BE)-PST - Pastel B5 Blue Waterproof.jpg3032(BE)-PST_02.jpg3032(PK)-PST - Pastel B5 Pink Inside Pages.jpg3032(PK)-PST - Pastel B5 Pink Pocket.jpg3032(PK)-PST - Pastel B5 Pink Waterproof.jpg3032(PK)-PST_01.jpg10201.PS4.jpg
213047-GLE 10201.PS5.jpg
2210202_WeeklyCovers.jpg
2310202_WeeklyOpen.jpg
2410203_DailyCovers.jpg
2510203_DailyOpen.jpg
2610204.MAIN.jpg
2710204.PS1.jpg
2810204.PS2.jpg
2910204.PS3.jpg
3010204.PS4.jpg
3110204.PS5.jpg
3210205_Back.jpg
3310205_Main.jpg
3410205_PS1.jpg
3510205_PS2.jpg
3610205_PS3.jpg
3710205_PS4.jpg
3810205_PS5.jpg
3910262_GoodVibesA5StickerTablet_WEB.jpg
4010263_HelloA5StickerTablet_WEB.jpg
4110769_Main.jpg
4210769_PT01.jpg
4310769_PT02.jpg
4410769_PT03.jpg
4510769_PT04.jpg
4610769_PT05.jpg
473032(BE)-PST - Pastel B5 Blue Inside Pages.jpg
483032(BE)-PST - Pastel B5 Blue Pocket.jpg
493032(BE)-PST - Pastel B5 Blue Waterproof.jpg
503032(BE)-PST_02.jpg
513032(PK)-PST - Pastel B5 Pink Inside Pages.jpg
523032(PK)-PST - Pastel B5 Pink Pocket.jpg
533032(PK)-PST - Pastel B5 Pink Waterproof.jpg
543032(PK)-PST_01.jpg
Data
Cell Formulas
RangeFormula
B2:D2,B20:I20,B19:G19,B14:H14,B13:G13,B11:C12,B10:G10,B7:B9,B15:B18,B21,B6:C6,B5:D5,B4:C4,B3:E3B2=LET(a,TEXTBEFORE(A2,"-",,,1),TOROW(FILTER($J$2:$J$100,LEFT($J$2:$J$100,LEN(a))=a,"")))
Dynamic array formulas.
 
Upvote 1
Solution
Fluff said:
How about
Fluff.xlsm
ABCDEFGHIJ
1manufacturerpartnumber
21018010180_1.jpg10180_2.jpg10180_3.jpg10180_1.jpg
31018210182_1.jpg10182_2.jpg10182_3.jpg10182_4.jpg10180_2.jpg
41018310183_Calendar_StickerTabletPack.jpg10183_CalendarA5StickerTablet_WEB.jpg10180_3.jpg
51018410184_1.jpg10184_2.jpg10184_3.jpg10182_1.jpg
61018510185_Week to View_inserts_packaging.jpg10185_Week to View_inserts.jpg10182_2.jpg
710186 10182_3.jpg
810187 10182_4.jpg
910189 10183_Calendar_StickerTabletPack.jpg
101020110201.MAIN.jpg10201.PS1.jpg10201.PS2.jpg10201.PS3.jpg10201.PS4.jpg10201.PS5.jpg10183_CalendarA5StickerTablet_WEB.jpg
111020210202_WeeklyCovers.jpg10202_WeeklyOpen.jpg10184_1.jpg
121020310203_DailyCovers.jpg10203_DailyOpen.jpg10184_2.jpg
131020410204.MAIN.jpg10204.PS1.jpg10204.PS2.jpg10204.PS3.jpg10204.PS4.jpg10204.PS5.jpg10184_3.jpg
141020510205_Back.jpg10205_Main.jpg10205_PS1.jpg10205_PS2.jpg10205_PS3.jpg10205_PS4.jpg10205_PS5.jpg10185_Week to View_inserts_packaging.jpg
151026210262_GoodVibesA5StickerTablet_WEB.jpg10185_Week to View_inserts.jpg
1610445 10201.MAIN.jpg
1710767 10201.PS1.jpg
1810768 10201.PS2.jpg
191076910769_Main.jpg10769_PT01.jpg10769_PT02.jpg10769_PT03.jpg10769_PT04.jpg10769_PT05.jpg10201.PS3.jpg
203032-PST3032(BE)-PST - Pastel B5 Blue Inside Pages.jpg3032(BE)-PST - Pastel B5 Blue Pocket.jpg3032(BE)-PST - Pastel B5 Blue Waterproof.jpg3032(BE)-PST_02.jpg3032(PK)-PST - Pastel B5 Pink Inside Pages.jpg3032(PK)-PST - Pastel B5 Pink Pocket.jpg3032(PK)-PST - Pastel B5 Pink Waterproof.jpg3032(PK)-PST_01.jpg10201.PS4.jpg
213047-GLE 10201.PS5.jpg
2210202_WeeklyCovers.jpg
2310202_WeeklyOpen.jpg
2410203_DailyCovers.jpg
2510203_DailyOpen.jpg
2610204.MAIN.jpg
2710204.PS1.jpg
2810204.PS2.jpg
2910204.PS3.jpg
3010204.PS4.jpg
3110204.PS5.jpg
3210205_Back.jpg
3310205_Main.jpg
3410205_PS1.jpg
3510205_PS2.jpg
3610205_PS3.jpg
3710205_PS4.jpg
3810205_PS5.jpg
3910262_GoodVibesA5StickerTablet_WEB.jpg
4010263_HelloA5StickerTablet_WEB.jpg
4110769_Main.jpg
4210769_PT01.jpg
4310769_PT02.jpg
4410769_PT03.jpg
4510769_PT04.jpg
4610769_PT05.jpg
473032(BE)-PST - Pastel B5 Blue Inside Pages.jpg
483032(BE)-PST - Pastel B5 Blue Pocket.jpg
493032(BE)-PST - Pastel B5 Blue Waterproof.jpg
503032(BE)-PST_02.jpg
513032(PK)-PST - Pastel B5 Pink Inside Pages.jpg
523032(PK)-PST - Pastel B5 Pink Pocket.jpg
533032(PK)-PST - Pastel B5 Pink Waterproof.jpg
543032(PK)-PST_01.jpg
Data
Cell Formulas
RangeFormula
B2:D2,B20:I20,B19:G19,B14:H14,B13:G13,B11:C12,B10:G10,B7:B9,B15:B18,B21,B6:C6,B5:D5,B4:C4,B3:E3B2=LET(a,TEXTBEFORE(A2,"-",,,1),TOROW(FILTER($J$2:$J$100,LEFT($J$2:$J$100,LEN(a))=a,"")))
Dynamic array formulas.
Click to expand...
THANKS so much!
i think that works!!
yay =)
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
Fluff said:
You're welcome & thanks for the feedback.
Click to expand...
have a follow up question - need some additional guidance please why did these match
9384-DIV​
#SPILL!​
9385-DIV​
9385-DIV.PS4.jpg​
9386-DIV​
9386-DIV.PS5.jpg​
9387-DIV​
9387-DIV.PS2.jpg​
9388-DIV​
9388-DIV.PS4.jpg​
9390-DIV​
9390-DIV.PS4.jpg​
but one has a #SPILL! error?

my part number list is in A and has 252 entires
my image list is in J and goes to line 1651
i adjusted your formula - to this
=LET(a,TEXTBEFORE(A2,"-",,,1),TOROW(FILTER($J$2:$J$1700,LEFT($J$2:$J$1700,LEN(a))=a,"")))
 
Upvote 0
That suggests that you have data to the right of the formula that is preventing it from spilling.
 
Upvote 1
You must log in or register to reply here.

Forum statistics

Threads
1,215,480
Messages
6,125,053
Members
449,206
Latest member
Healthydogs

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top