PDA

View Full Version : Looping through cells (contents of cell seperated by commas) and performing vlookups



AustinS
10-26-2011, 10:19 AM
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
10-26-2011, 11:26 AM
Austin

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

AustinS
10-26-2011, 12:11 PM
My computer decided to bsod... So I'm posting from cell phone. Let's seeif I can make sample from my phone...

AustinS
10-26-2011, 12:34 PM
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
10-26-2011, 04:17 PM
That link is not found

AustinS
10-26-2011, 04:55 PM
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
10-27-2011, 12:19 AM
Is there any reason why we can't separate the values into helper columns?

p45cal
10-27-2011, 02:13 AM
quite difficult with worksheet formulae, but a udf can do it:
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
Not very efficient, but does the job.

Austin
10-27-2011, 05:19 AM
Hmm, this looks like it works. Let me try it on the real set of data and see what happens! Thanks so much.