Combining Multiple Individual Functions Into One With Dynamic Lookup Capability

[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.

Please Login or Register  to view this content.

A2 = www. walmart.com/ip/33857317
B2 = WAL

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 appropriate function to use to yield the desired result (based on supplier URL).

Please Login or Register  to view this content.

A2 = www. walmart.com/ip/33857317

The arrays are found on the REFERENCE tab. 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.

Examples:

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

DESIRED OUTCOME
WAL-33857317
SAM-118235
COS-100355896

A sample workbook is attached.

Thanks for your input!

[FlameRetired]

As long as the SKUs don't exceed 15 characters in length this combines both parts in one formula.

Note the SKU extraction part of this formula is not my creation. I've borrowed shamelessly from someone named Lori. (I've lost the source thread.)

Array enter this in B8 and fill down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

[FlameRetired]

If the SKUs are longer than 15 characters you will need VBA.

[censo]

If the SKUs are longer than 15 characters you will need VBA.

Thanks, the SKUs can and usually do exceed 15 characters (up to 40). Hopeful there is a non-VBA solution, because that'll take me further down the rabbit hole which I am not familiar with.

[FlameRetired]

In that case please upload an Excel workbook more representative of what the source data actually looks like. There may be other exceptions to handle. I can include a user defined function and formula that shouldn't be much trouble to use.

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

[FlameRetired]

In the meantime pending any additional exceptions try this

Please Login or Register  to view this content.

[FlameRetired]

This is slightly shorter and does not have to be array entered.

Please Login or Register  to view this content.

[censo]

This is slightly shorter and does not have to be array entered.

Please Login or Register  to view this content.

Ok, I'll give it a shot and let you know my results. Thanks again

[FlameRetired]

This variation of Post #6 will return all the SKUs.

Please Login or Register  to view this content.

[censo]

This variation of Post #6 will return all the SKUs.

That did the trick and satisfies the need I had, thanks a lot!

[FlameRetired]

You're welcome. Thanks for the feedback and rep.