VBA or Macro needed.. Complex Vlookup, return to multiple rows.. Going crazy here..

[lbarchi]

I have a worksheet of items and comma separated tags, and other worksheet with tags and tag codes.

The tag list is something like this:

Column A: "CODE" - Column B: "Tags"
115 - blue
128 - black and white
110 - yellow
212 - small
518 - female
887 - cotton
664 - imported


My items worksheet is like this:
Column A: Item ID 1541
Column B: Juvenile dress with flowers
Column C: female, small, cotton, black and white, imported

I need something to automatically split the Column C, VLookup the tags to get the codes, and write the ItemID and the tag codes in different rows,
like this:

Cell A1: Item ID 1541

Cell B1: 518

Cell A2: Item ID 1541

Cell B2: 212

Cell A3: Item ID 1541

Cell B3: 887

Cell A4: Item ID 1541

Cell B4: 128

and so on..

I managed to do it with "tag to columns", then VLookup, and writing the results in a new worksheet, but I'm going crazy doing it manually...

Any help would be appreciated!

Thanks so much in advance!!

[tfurnivall]

Hi lbarchi!

Take a look at this possible solution.

You'll notice that I reversed the order of the columns in your code table, because as I understand you you want to look up a tag string, and get the coded value for that tag. VLOOKUP wants its value to find to be in the first column of the array.


HTH

Tony

[lbarchi]

Hi, thanks for the fast response! Would you please take a look at the attached file? You will see all the steps I have to take to get the desired data.. That's why I need a VBA, macro, something! The manual imput is driving me nuts and my boss wants the data as shown in the xls file..

Thanks a lot!

[lbarchi]

One more thing, the number of tags of the items is variable, so sometimes i have 5,6, 10 columns with tags..

[tfurnivall]

Ooops, my bad. I uploaded a spreadsheet without the code!

Try this. It has a procedure to translate the english text into numerical codes.

Please Login or Register  to view this content.

What I've done is to create a new string of all the codes joined by commas. However, what you would want to do is to create a new entry in the sheet DesiredResults, with the Item code and each TagCode as you encounter it. I've flagged the code with a big SHOUTOUT where you'd need to do this.

Let me know if you need help with that,

Tony

[lbarchi]

Thanks sooo much, I've tested it and now I have all the tag codes separated with commas, amazing!

One question, you lost me here: How do I create a new entry in the sheet3 with the Item code and each TagCode?

[tfurnivall]

Try this.

I added a routine to set up the new worksheet (DesiredResults), and a routine (in place of the test routine) to cycle through all of the inventory items (There's only one at present, the one that you gave me!

Tony

[lbarchi]

I can't thank you enough for this!!! Thanks sooooo much! You saved my life!!