Looping through cells (contents of cell seperated by commas) and performing vlookups

[AustinS]

This is confusing and I can't figure out how to make it work.

I have cells that have numbers separated by commas.

e.g., A1: "1,2,3,4"

I want to separate those numbers out and perform a vlookup on each number and concatenate the results into one cell.

So in another tab it might have

1|sentence1
2|sentence2
3|sentence3
4|sentence4

I need to loop through Cell A1-A50 (or whatever) and read out 1,2,3,4 and do vlookups on the other tab and make a concatenation of sentence1sentence2sentence3 into one cell.

I'm failing hard at making this work. Any help is greatly appreciated.

[Rob342]

Austin

Can you post a sample copy workbook & where you want the data to be?

[AustinS]

My computer decided to bsod... So I'm posting from cell phone. Let's seeif I can make sample from my phone...

[AustinS]

It won't let me select a file from my phone but here is a link to public drop box.

httpxx//db.tt/a82qegtQ replace xx with a colon

[Aussiebear]

That link is not found

[AustinS]

Hey Aussie.. that link works fine. However, I wasn't able to post a normal link because of the forum restrictions (under 5 posts).

I'm attaching it here again.. because my computer is working again. It is now attached to this post.

THANKS!

[Aussiebear]

Is there any reason why we can't separate the values into helper columns?

[p45cal]

quite difficult with worksheet formulae, but a udf can do it:
[vba]Public Function beuhh(stuff)
xx = Split(stuff, ",")
For Each thing In xx
yy = Application.VLookup(CLng(Application.Trim(thing)), Sheets("data").Range("A:B"), 2, False)
If IsError(yy) Then beuhh = beuhh & "----" Else beuhh = beuhh & yy
Next thing
End Function
[/vba] Not very efficient, but does the job.

[Austin]

Hmm, this looks like it works. Let me try it on the real set of data and see what happens! Thanks so much.