Combining Multiple Individual Functions Into One

[censo]

With the help of the online community, I've been piecing together solutions to a moderately complex solution. I'm hoping this post can tie them all together.


This first function identifies the name of a supplier based on its URL and creates a unique 3-character code based on a lookup table. This one function applies to any URL.

Book1
	A	B
1	SUPP URL	SUPP CODE
2	https://www.walmart.com/ip/33857317	WAL
3	https://www.samsclub.com/sams/118235.ip	SAM
4	https://www.costco.com/Aquaterra-Spas-Ventura-96-jet%2c-6-person-Spa.product.100355896.html	COS
DATA

Cell Formulas
Range	Formula
B2	=IF(A2<>"",VLOOKUP(IF(ISNUMBER(FIND("www.",A2)),MID(A2,FIND("www.",A2)+4,IF(ISNUMBER(FIND(".com",A2,9)),FIND(".com",A2,9),LEN(A2)+1)-FIND("www.",A2)-4),MID(A2,FIND("//",A2)+2,IF(ISNUMBER(FIND("/",A2,9)),FIND("/",A2,9),LEN(A2)+1)-FIND("//",A2)-2)),SUPP_PREFIX,2,0))

Named Ranges
Name	Refers To	Cells
SUPP_PREFIX	=REFERENCE!$A$1:$B$4

The next set of functions properly identifies the item number (a specific section of a website's URL). Note however that each website has its own unique syntax; therefore, for this function to dynamically work, it must reference an array that tells it which subsequent function to use to yield the desired result (based on supplier URL).

Book1
	D	E
1	SUPP URL	ITEM NUMBER
2	https://www.walmart.com/ip/33857317	33857317
3	https://www.samsclub.com/sams/118235.ip	118235
4	https://www.costco.com/Aquaterra-Spas-Ventura-96-jet%2c-6-person-Spa.product.100355896.html	100355896
DATA

Cell Formulas
Range	Formula
E2	=TRIM(RIGHT(SUBSTITUTE(D2,"/",REPT(" ",LEN(D2))),LEN(D2))+0)
E3	=TRIM(RIGHT(SUBSTITUTE(LEFT(D3,LEN(D3)-3),"/",REPT(" ",100)),100))
E4	=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(D4,".product.",REPT(" ",99)),98)),".html","")

The arrays are found on the REFERENCE tab

Book1
	A	B
1	SUPP NAME	SUPP CODE
2	walmart	WAL
3	samsclub	SAM
4	costco	COS
5
6	SUPP CODE	SUPP FUNCTION
7	WAL	TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",LEN(A1))),LEN(A1))+0)
8	SAM	TRIM(RIGHT(SUBSTITUTE(LEFT(A1,LEN(A1)-3),"/",REPT(" ",100)),100)),"")
9	COS	SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,".product.",REPT(" ",99)),98)),".html",""),"")
REFERENCE

The arrays are named ranges:


SUPP_PREFIX
SUPP_ITEM


A challenge I see is that in the SUPP_ITEM array, cell references are hard-coded into the listed functions (which worked when I used them individually because when I would pull the functions down, the cell references would auto-update). In this case however, how can instances of cell reference "A1" be made to vary or apply to the line in which it is being executed upon?


Tying the two main functions to generate a SKU dynamically based on a given supplier URL is the end goal.

Book1
	A	B
7	SUPP URL	DESIRED OUTCOME
8	https://www.walmart.com/ip/33857317	WAL-33857317
9	https://www.samsclub.com/sams/118235.ip	SAM-118235
10	https://www.costco.com/Aquaterra-Spas-Ventura-96-jet%2c-6-person-Spa.product.100355896.html	COS-100355896
DATA

Thanks for your input!

 

[jtakw]

Hi,

I've checked a couple of your other postings regarding the formulas you're using.

1. Based on the fact that the URLs will Always have "www" before the Supplier's name.
2. Your SUB CODE is Always the first 3 letters of the supplier's name.
3. The item number is Always the string of digits either at the End, or precedes either ".ip" or ".html" at the end.

Try and see if this works for you, I'm not using your REFERENCE tab to look up SUPP CODE or SUPP FUNCTIONS:

	A	B
SUPP URL
https://www.walmart.com/ip/33857317	WAL-33857317
https://www.samsclub.com/sams/118235.ip	SAM-118235
https://www.costco.com/Aquaterra-Spas-Ventura-96-jet%2c-6-person-Spa.product.100355896.html	COS-100355896

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

</tbody>

Sheet47​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=UPPER(MID(A2,SEARCH("www.",A2)+4,3))&"-"&TRIM(<font< a="">RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,".ip",""),".html",""),".","/"),"/",REPT(" ",100)),100))</font<>[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[censo]

Try and see if this works for you, I'm not using your REFERENCE tab to look up SUPP CODE or SUPP FUNCTIONS:

Wow, way to think outside the box. I had never considered the points you bring up, which are valid and great ideas.

However, there are some assumptions that I would only use just these suppliers. Challenges would include:

- New suppliers may have different URL syntax not included in the function
- I may not always use the first 3 letters of the suppliers name for the SKU prefix (ex. I would not use "OVE" for Overstock, I would prefer "OVS" in that case)

Based on the challenges identified, it does not appear the proposed solution would scale well opposed to the named ranges approach originally presented. Would you agree?

 

[jtakw]

Well, basically, what I've provided in Post #2 is based on the samples you have given, and combined all the Functions/requirements into One formula.

This is what you can use since you say Supplier's name prefixes are Not Always the first three letters (we Will be using the SUPP_PREFIX table), and this formula Still covers many possible URLs (without anything behind the Item Number, with .ip, .html, .anything, /anything, etc.)

	A	B	C
SUPP URL
https://www.walmart.com/ip/33857317	WAL-33857317
https://www.samsclub.com/sams/118235.ip	SAM-118235
https://www.costco.com/Aquaterra-Spas-Ventura-96-jet%2c-6-person-Spa.product.100355896.html	COS-100355896
https://www.walmart.com/ip.2345678.this	WAL-2345678
https://www.overstock.com/bettermousetrap/12345678.other	OVS-12345678
https://www.overstock.com/widget.90123456789/something	OVS-90123456789

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet48​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=VLOOKUP(MID(A2,FIND("www.",A2)+4,FIND(".com",A2)-FIND("www.",A2)-4),SUPP_PREFIX,2,0)&"-"&TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(RIGHT(A2,1)+0),A2&"/",A2),".html/",".html"),".","/"),"/",REPT(" ",100)),200),100))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]SUPP_PREFIX[/TH]
[TD="align: left"]=REFERENCE!$A$2:$B$5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

	A	B
SUPP NAME	SUPP CODE
walmart	WAL
samsclub	SAM
costco	COS
overstock	OVS

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

</tbody>

REFERENCE​

 

[censo]

This is what you can use since you say Supplier's name prefixes are Not Always the first three letters (we Will be using the SUPP_PREFIX table), and this formula Still covers many possible URLs (without anything behind the Item Number, with .ip, .html, .anything, /anything, etc.)

You TOTALLY nailed it! Your solution has passed every single scenario I've thrown at it - except one. To be fair, it wasn't part of my original write-up and it's because I remembered (during the testing of your proposed solution) that this was a manual step I was doing and that is ...


quite regularly supplier websites will inject code into their URL that is locally significant to them (not all of them though). For instance, if I land on a product as a result of doing a keyword search or perhaps navigating to a product as a result of following a particular path, the website concatenates extra junk (what I call making it a dirty URL) that usually begins with a delimiting character of a question mark "?".


Can a simple modification to your function address this issue as well? Here are some examples and the results I get when using your function (which again, works wonders only when I manually delete the delimiting character and everything to the right as shown below):

Book1
	A	B
1	(DIRTY) SUPP URL	SKU
2	https://www.samsclub.com/sams/hadley-jar-7-inch/prod15680245.ip?xid=plp:product:1:1	SAM-ip?xid=plp:product:1:1
3	https://www.samsclub.com/sams/floralife-crystal-clear-fresh-cut-flower-food-300-powder-10-pound-pail/prod21365761.ip?athcpid=prod21365761&athena=true&athpgid=pdp&athmtid=VaV&athznid=sams_ip_VaV&parentpid=prod15680245&xid=pdp:carousel:people-who-viewed-this-item-also-viewed:2	SAM-thznid=sams_ip_VaV&parentpid=prod15680245&xid=pdp:carousel:people-who-viewed-this-item-also-viewed:2
4	https://www.walmart.com/ip/Better-Homes-and-Gardens-Modern-Farmhouse-TV-Stand-for-TVs-up-to-60-Rustic-Gray-Finish/191535084?athcpid=191535084&athpgid=athenaItemPage&athcgid=null&athznid=tic&athieid=v0&athstid=CS002&athguid=466001f5-5530865c-68f7ce0ccc504ea8&athena=true	WAL-191535084?athcpid=191535084&athpgid=athenaItemPage&athc
5	https://www.walmart.com/ip/Skylake-5-Shelf-Bookcase/55566779?athcpid=55566779&athpgid=athenaItemPage&athcgid=null&athznid=PWVAV&athieid=v0&athstid=CS002&athguid=466001f5-929ea9f1-eeda549491cc833a&athena=true	WAL-55566779?athcpid=55566779&athpgid=athenaItemPage&athcgi
6
7	(CLEAN) SUPP URL	SKU
8	https://www.samsclub.com/sams/hadley-jar-7-inch/prod15680245.ip	SAM-prod15680245
9	https://www.samsclub.com/sams/floralife-crystal-clear-fresh-cut-flower-food-300-powder-10-pound-pail/prod21365761.ip	SAM-prod21365761
10	https://www.walmart.com/ip/Better-Homes-and-Gardens-Modern-Farmhouse-TV-Stand-for-TVs-up-to-60-Rustic-Gray-Finish/191535084	WAL-191535084
11	https://www.walmart.com/ip/Skylake-5-Shelf-Bookcase/55566779	WAL-55566779
for sales tracker

Cell Formulas
Range	Formula
B2	=VLOOKUP(MID(A2,FIND("www.",A2)+4,FIND(".com",A2)-FIND("www.",A2)-4),SUPP_PREFIX,2,0)&"-"&TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(RIGHT(A2,1)+0),A2&"/",A2),".html/",".html"),".","/"),"/",REPT(" ",100)),200),100))

Named Ranges
Name	Refers To	Cells
SUPP_PREFIX	=REFERENCE!$A$1:$B$4

 

[jtakw]

Hi,

This updated formula will work for your Dirty And Clean URLs.
One question, is the "prod" in front of some of the Numbers Wanted? Or should they be eliminated?

	A	B
(DIRTY) SUPP URL
https://www.samsclub.com/sams/hadley-jar-7-inch/prod15680245.ip?xid=plproduct:1:1	SAM-prod15680245
https://www.samsclub.com/sams/floralife-crystal-clear-fresh-cut-flower-food-300-powder-10-pound- pail/prod21365761.ip?athcpid=prod21365761&athena=true&athpgid=pdp&athmtid=VaV&athznid=sams _ip_VaV&parentpid=prod15680245&xid=pdp:carouseleople-who-viewed-this-item-also-viewed:2	SAM-prod21365761
https://www.walmart.com/ip/Better-Homes-and-Gardens-Modern-Farmhouse-TV-Stand-for-TVs-up-to-60- Rustic-Gray-Finish/191535084?athcpid=191535084&athpgid=athenaItemPage&athcgid=null&athznid=tic&athieid=v0&athstid= CS002&athguid=466001f5-5530865c-68f7ce0ccc504ea8&athena=true	WAL-191535084
https://www.walmart.com/ip/Skylake-5-Shelf-Bookcase/55566779?athcpid=55566779&athpgid= athenaItemPage&athcgid=null&athznid=PWVAV&athieid=v0&athstid=CS002&athguid=466001f5-929ea9f1-eeda549491cc833a&athena=true	WAL-55566779
SUPP URL
https://www.walmart.com/ip/33857317	WAL-33857317
https://www.samsclub.com/sams/118235.ip	SAM-118235
https://www.costco.com/Aquaterra-Spas-Ventura-96-jet%2c-6-person-Spa.product.100355896.html	COS-100355896
https://www.walmart.com/ip.2345678.this	WAL-2345678
https://www.overstock.com/bettermousetrap/12345678.other	OVS-12345678
https://www.overstock.com/widget.90123456789/something	OVS-90123456789

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

</tbody>

Sheet48​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=VLOOKUP(<font color="blue">MID(A2,FIND("www.",A2)+4,FIND(<font< a="">".com",A2</font<>)-FIND("www.",A2)-4),SUPP_PREFIX,2,0)&"-"&TRIM(<font< a="">LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(RIGHT(LEFT(A2,FIND("?",A2&"?")-1),1)+0),LEFT(A2,FIND("?",A2&"?")-1)&"/",LEFT(A2,FIND("?",A2&"?")-1)),".html/",".html"),".","/"),"/",REPT(" ",100)),200),100))</font<>[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]SUPP_PREFIX[/TH]
[TD="align: left"]=REFERENCE!$A$2:$B$5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[censo]

This updated formula will work for your Dirty And Clean URLs.
One question, is the "prod" in front of some of the Numbers Wanted? Or should they be eliminated?

Superb work yet again. My goodness, that is quite the function. I'm curious, do you use some sort of function builder to help you keep track of every comma, dot, parenthesis, and argument or do you really build this by hand? Nevertheless it's quite impressive and I extend my sincere thanks for your assistance.

The "prod" most definitely stays because when you attempt to search for the item number without it, the page won't load but when you include the "prod" together with the item number, the page loads fine.

 

[jtakw]

You're welcome, glad it's working for you.

It's all done in the brain and by hand.

 

[shlomek12]

Hi my helper....

How you will you make this one from Sams Club? I bold some samples


[TABLE="width: 1029"]
<tbody>[TR]
[TD="width: 1029"]http://www.samsclub.com/sams/nutri-ninja-blender-bl681a/prod20410492.ip?xid=plp_product_1_2
[TABLE="width: 1029"]
<colgroup><col></colgroup><tbody>[TR]
[TD]http://www.samsclub.com/sams/shark-ion-robot-rv720/prod22210179.ip?xid=plp_product_1_1[/TD]
[/TR]
[TR]
[TD]http://www.samsclub.com/sams/richmond-foosball-58-inch-soccer/prod21930008.ip?xid=plp_product_1_2[/TD]
[/TR]
[TR]
[TD]http://www.samsclub.com/sams/shower-chair-with-armrests/prod20052125.ip?xid=plp_product_1_3[/TD]
[/TR]
[TR]
[TD]http://www.samsclub.com/sams/transfer-bench/prod20132231.ip?xid=plp_product_1_5[/TD]
[/TR]
[TR]
[TD]http://www.samsclub.com/sams/twst-clrpencil-ast30-twistables-colored/prod19260226.ip?xid=plp_product_1_5[/TD]
[/TR]
[TR]
[TD]http://www.samsclub.com/sams/cyo-wash-chalk-48ast-wshbl-sidewalk-chalk/prod18520296.ip?xid=plp_product_1_1[/TD]
[/TR]
[TR]
[TD]http://www.samsclub.com/sams/4-geartrack-2-pack-panels-trim/prod16820488.ip?xid=plp_product_1_1[/TD]
[/TR]
[TR]
[TD]http://www.samsclub.com/sams/storage-bin-holder-wall-components/prod16820491.ip?xid=plp_product_1_54[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

shlomek12,

Please refer back to your Post here: https://www.mrexcel.com/forum/excel-questions/1057397-how-extract-specific-data-cell-3.html