Combining Multiple Individual Functions Into One

censo · May 15, 2018

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 · May 15, 2018

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
1	SUPP URL
2	https://www.walmart.com/ip/33857317	WAL-33857317
3	https://www.samsclub.com/sams/118235.ip	SAM-118235
4	https://www.costco.com/Aquaterra-Spas-Ventura-96-jet%2c-6-person-Spa.product.100355896.html	COS-100355896

Sheet47

Worksheet Formulas

Cell	Formula
B2	=UPPER(MID(A2,SEARCH("www.",A2)+4,3))&"-"&TRIM(<font< a="">RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,".ip",""),".html",""),".","/"),"/",REPT(" ",100)),100))</font<>

censo · May 15, 2018

jtakw said:
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 · May 16, 2018

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	C
1	SUPP URL
2	https://www.walmart.com/ip/33857317	WAL-33857317
3	https://www.samsclub.com/sams/118235.ip	SAM-118235
4	https://www.costco.com/Aquaterra-Spas-Ventura-96-jet%2c-6-person-Spa.product.100355896.html	COS-100355896
5	https://www.walmart.com/ip.2345678.this	WAL-2345678
6	https://www.overstock.com/bettermousetrap/12345678.other	OVS-12345678
7	https://www.overstock.com/widget.90123456789/something	OVS-90123456789

Sheet48

Worksheet Formulas

Cell	Formula
C2	=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))

Workbook Defined Names

Name	Refers To
SUPP_PREFIX	=REFERENCE!$A$2:$B$5

	A	B
1	SUPP NAME	SUPP CODE
2	walmart	WAL
3	samsclub	SAM
4	costco	COS
5	overstock	OVS

REFERENCE

censo · May 18, 2018

jtakw said:
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 · May 18, 2018

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
1	(DIRTY) SUPP URL
2	https://www.samsclub.com/sams/hadley-jar-7-inch/prod15680245.ip?xid=plproduct:1:1	SAM-prod15680245
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:carouseleople-who-viewed-this-item-also-viewed:2	SAM-prod21365761
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
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
6
7	SUPP URL
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
11	https://www.walmart.com/ip.2345678.this	WAL-2345678
12	https://www.overstock.com/bettermousetrap/12345678.other	OVS-12345678
13	https://www.overstock.com/widget.90123456789/something	OVS-90123456789

Sheet48

Worksheet Formulas

Cell	Formula
B2	=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<>

Workbook Defined Names

Name	Refers To
SUPP_PREFIX	=REFERENCE!$A$2:$B$5

censo · May 18, 2018

jtakw said:
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 · May 18, 2018

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

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

shlomek12 · Jun 25, 2018

Hi my helper....

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

http://www.samsclub.com/sams/nutri-ninja-blender-bl681a/prod20410492.ip?xid=plp_product_1_2

http://www.samsclub.com/sams/shark-ion-robot-rv720/prod22210179.ip?xid=plp_product_1_1

http://www.samsclub.com/sams/richmond-foosball-58-inch-soccer/prod21930008.ip?xid=plp_product_1_2

http://www.samsclub.com/sams/shower-chair-with-armrests/prod20052125.ip?xid=plp_product_1_3

http://www.samsclub.com/sams/transfer-bench/prod20132231.ip?xid=plp_product_1_5

http://www.samsclub.com/sams/twst-clrpencil-ast30-twistables-colored/prod19260226.ip?xid=plp_product_1_5

http://www.samsclub.com/sams/cyo-wash-chalk-48ast-wshbl-sidewalk-chalk/prod18520296.ip?xid=plp_product_1_1

http://www.samsclub.com/sams/4-geartrack-2-pack-panels-trim/prod16820488.ip?xid=plp_product_1_1

http://www.samsclub.com/sams/storage-bin-holder-wall-components/prod16820491.ip?xid=plp_product_1_54

jtakw · Jun 25, 2018

shlomek12,

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

Combining Multiple Individual Functions Into One

censo

Board Regular

Excel Facts

jtakw

Well-known Member

censo

Board Regular

jtakw

Well-known Member

censo

Board Regular

jtakw

Well-known Member

censo

Board Regular

jtakw

Well-known Member

shlomek12

Board Regular

jtakw

Well-known Member

Similar threads

Forum statistics

Share this page

Combining Multiple Individual Functions Into One

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock