Mulitiple lookups returning value into one cell seperated by commas

[jbhamra89]

Hi all,

Been struggling with this for a while (first time post-er as well).

I have a column of numbers with multiple numbers in each cell (75, 82, 82, 85, 18) and a table which matches those number with what those number refer to. ( 75=Networking, 82=Security, etc.)

I need to replace the column of numbers with what the numbers equal to while keeping in mind the commas in that cell as well.

It sounds easy but its a tricky one.

I have att a sample sheet.

Any help is appreciated!

test data.xlsx

[OnlySteveH]

I need to replace the column of numbers with what the numbers equal to while keeping in mind the commas in that cell as well.

Will the output look like, for example:

Networking, Databases, Cloud, DR, Desktop

In place of the entries in Column A on the Group ID sheet?

Steve.

P.S. Welcome to the forum

[jbhamra89]

Correct!

Thanks for the welcome Steve

[darknation144]

I can do it easily if I can use text to columns but I guess you want to keep all the numbers in the same cell?

[jbhamra89]

Hey darknation,

I also thought of trying text to columns-and it could deff work-however this would be a monthly report and a smoother operation would be needed.

and yesss all the converted numbers need to be in the same cell separated by commas.

[jbhamra89]

Am I going to have to resort to VBA?

[darknation144]

Easiest way would be a while loop but I've never done one in VBA I'm sure someone else can help.

[jbhamra89]

I'll deff look into that then. thanks again

[OnlySteveH]

This is doable in VBA, yes - I'm working on that now - I can't see any other way of doing it.

One "issue" is that the cells in GroupID, Column A are full of loads of spaces after the numbers. Cell A4, for example, is 255 characters long with all the spaces. Is that necessary? It probably isn't a problem, as I'll sift out the whitespace, but it is just a little messy.

To parse through the string and pull out what precedes a comma, trim that of whitespace then lookup to the Table on Sheet1 is simple enough (he says!). A new string can then be constructed per-cell. This just then needs repeating through all the cells in the initial column.

Cells like A12 are going to be very long; the output will not be pretty.

[NBVC]

You will need to add a user defined function:

Add this code to your VB editor (ALT+F11, INSERT, Module)

Please Login or Register  to view this content.

Then use formula

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

Note: if items are not found, nothing will be inserted in the string, so it might be off....

[OnlySteveH]

... And, presumably, you don't want the original numbers overtyping? I'll put the character strings into the next column.

[OnlySteveH]

Nice one @NBVC!

[jbhamra89]

haha the power of VBA...

The spaces are not necessary at all, I believe it was just sloppiness of creating the test data on my part.

I appreciate your help Steve!

I will deff need to brush up more on VBA

[darknation144]

NBVC I have no idea how you knocked that up that fast, Impressive.

[NBVC]

The VBA code is not actually mine... as you will see in the first comment up top (it is one that is popular and is commonly used)

.... but the formula is mine

[jbhamra89]

NBVC!

I agree with darknation, I have no idea how you did that so quickly.

I am trying out the formula as we speak and will let you know the conclusion.

Thanks for all your help guys

[OnlySteveH]

Having a library of code snippets and user defined functions is the way forward!

Not sure if the aconcat -vs- mconcat may cause a problem? The UDF & cell function seem to have different names?

[NBVC]

Yes, the formula should use ACONCAT... i changed it... MCONCAT is free addin function that I also use.. but it isn't compatible with Excel 2010, i don't think....

so formula should be:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER

[jbhamra89]

looks like it is starting to work! Thanks a lot for your help everyone.

-J