String of text representing an array in one cell, vlookup each item, create new string

Hello,

I'm a basic user of excel and I wondered if you could help me. I would like to do the following:

Using the attached data as an example, I want to take the string in column C (product codes) and create a string in column D (of product ID's) by looking up the individual product codes from column C (seperated by coma's) in column A and getting there relevant ID's from column B and creating a string that will sit in column D (seperated by colon's, no spaces).

I would be greatful for any help with this as I am struggling on how to go about this.

Thanks in advance.

Paul

Hello,

Sorry about this, the system froze and I ended up posting twice, one with an attachment and one without. Here is the attachment for this post.

Thanks.

This thread is a duplicate of http://www.excelforum.com/excel-gene...ew-string.html

How much data is in your real sheet compared to the example?

The array formula that this would require would take considerable time to process with a large amount of data.

Hi Jason,

In the real sheet there would be aproximatly 10000 or more rows.

Thanks for your help.

This is the best I can come up with, I've used a UDF instead of arrays to make it run smoother.

In the example attached you will see that the first 2 rows show a single part number instead of the required string, this number is missing from the list in column A so the formula is returning the problem part number for you to investigate, this can easily be changed if needed.

example-data(1).xls

Hi Jason,

Thats fantastic, perfect, exactly what I needed. I'm very happy with what you have done here.

Looking at the UDF macro you have created I dont quite understand the terms used but I can just about understand what its doing.

Not to worry about the ones that reference a code that doesnt exist in the list that wont happen in the real list and if it does then we need it to show up like that so that we can make any corrections any way.

Thanks so much.