Vlookup multiple Values then Concatenate all values in One cell

[Wsr712]

Hi Everyone,

Excel Noob here, Here's my situation, most of the time I organize countries
by their region to cut time, i hoping that Excel Vlookup would help me out, but as other noob like me experience is that it only throws the first value it gets and disregards the rest. For instance, Asia Pacific, there are several countries under that region like
Malaysia, Vietnam, Cambodia etc. so my given would be as below

Asia Pacific Malaysia,
Asia Pacific China
Asia Pacific Philippines

Europe Amsterdam
Europe Italy
Europe France

The output I would like would be:

Asia Pacific: Malaysia,, China, Philippines
Europe: Amsterdam, Italy, France


This would save me time Appreciate the help and Thanks in advance!

 

[osf9]

Dear Rick,

your UDF seems to be great, but I have 2 questions:

1) once I import it in a file, am I able to share that file with other people? And will they be able to see the same results I can see? In other words, the UDF will be linked to my file or to my Excel?
2) In case I have a situation like this:

Alpha 1
Beta 4
Alpha 1
Alpha 2
Beta 4
Beta 2

How can I have the following results?

Alpha 1,2
Beta 4,2

At the moment your formula gives me:

Apha 1,1,2
Beta 4,4,2

and I would like to avoid doubled results.

Thanks in advance, best regards,

Carlo

 

[Rick Rothstein]

Dear Rick,

your UDF seems to be great, but I have 2 questions:

1) once I import it in a file, am I able to share that file with other people? And will they be able to see the same results I can see? In other words, the UDF will be linked to my file or to my Excel?
2) In case I have a situation like this:

Alpha 1
Beta 4
Alpha 1
Alpha 2
Beta 4
Beta 2

How can I have the following results?

Alpha 1,2
Beta 4,2

At the moment your formula gives me:

Apha 1,1,2
Beta 4,4,2

and I would like to avoid doubled results.

Thanks in advance, best regards,

Carlo

1) Once you install the UDF into a module within a workbook, it travels with that workbook. And, hence, it will only be available from that workbook (you would have to install it into any other workbooks you want it to be available in).

2) The UDF has an optional UniqueOnly argument which is defaulted to False... set it to True and duplicate values will not appear.

 

[osf9]

Rick... You are great!
Thanks, regards, Carlo

 

[osf9]

Dear Rick,

sorry in advance for what will seem a "silly question": your LookUpConcat is great, but I need to use in a spreadsheet where I have something like 3000 unique value to lookup, for 10 columns and with a database of 6000 lines and 60 columns.

As you can imagine this complicates my Excel and it usually stuck it every time it calculates formula.

My question is: is there any way to "speed up" your LookUpConcat?

Thanks in advance or your response, best regards, Carlo

 

[jwhite0720]

I'm running into the same issue as osf9. I've got several thousand lines of data, and it's bogging down. Any suggestions?

 

[antalgebra]

This function does not work on the latest Mac Excel. Any help on how I can troubleshoot this? it returns #NAME for all instances. I even downloaded someone else's workbook that had the correct stuff, and it still returned #NAME after I clicked in the cell.

UDF was inserted correctly as module1, and seems to work on the Windows side of things

 

[Rick Rothstein]

This function does not work on the latest Mac Excel. Any help on how I can troubleshoot this? it returns #NAME for all instances. I even downloaded someone else's workbook that had the correct stuff, and it still returned #NAME after I clicked in the cell.

UDF was inserted correctly as module1, and seems to work on the Windows side of things

Sorry, but I cannot help you with your problem as I do not have access to a Mac, so I have no way of testing/debugging the code on that platform.

 

[antalgebra]

Sorry, but I cannot help you with your problem as I do not have access to a Mac, so I have no way of testing/debugging the code on that platform.

Thank you for replying so quick!
Im excel-consulting for a company that is 100% mac, (I do NOT recommend office version of mac at all).

Anyway; I made a silly mistake by installing the function in a different (open) workbook in the macro dictionaries - interestingly installing the function in workbook2, it still pulls up as an option when you start typing it into a cell in workbook1... It just won't work. woah.

Anyway, thanks! I can confirm your macro works 100% on a mac!

 

[Sgdva]

I'm not quite sure if I should open a new thread, but, since this code kind of works for me I'll post here.
I'm trying to do the same effect as a pivot table would do setting autofilters (it will only show the data that match the filters that you are giving), for this, the code does the trick! However, I want a kind of "OR" condition.
IE:
My data: (Col A) A1: Value1 Value2
Data to be looked in: (Col B) B3:...Value1 has 30 units...Value2 has 20 units... B7: ...Value1 has 70 units ... Value2 has 1000 units...
Data to display: (Col C) C3: (Where it found value 1 and value 2 in B) Person 1 C7: (Where it found value 1 and value 2 in B) Person 2
Final result: A1 is found in B3 and B7, thus, returning C3 and C7.
As we may see, value 1 and value 2 are in the string, but, the formula doesn't look them up (I tried "The fifth argument, a Boolean, determines whether the lookup should be trying to match the entire cell value or only part of the cell value (the default value is True meaning the lookup will have to match the entire cell value", but, that doesn't do the trick).
Any help would be so helpful!

 

[serquan]

Okay, I discovered the problem... some of your concatenations in Column AJ are returning #N/A errors and that is affecting my code. Here is revised code to work around that problem (actually, the code is the same except for the line I highlighted in red)...

Function LookUpConcat(ByVal SearchString As String, SearchRange As Range, ReturnRange As Range, _
                      Optional Delimiter As String = " ", Optional MatchWhole As Boolean = True, _
                      Optional UniqueOnly As Boolean = False, Optional MatchCase As Boolean = False)
                   
  Dim X As Long, CellVal As String, ReturnVal As String, Result As String
  
  If (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or _
     (ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then
    LookUpConcat = CVErr(xlErrRef)
  Else
    If Not MatchCase Then SearchString = UCase(SearchString)
    For X = 1 To SearchRange.Count
      If IsError(SearchRange(X)) Then GoTo Continue
      If MatchCase Then
        CellVal = SearchRange(X).Value
      Else
        CellVal = UCase(SearchRange(X).Value)
      End If
      ReturnVal = ReturnRange(X).Value
      If MatchWhole And CellVal = SearchString Then
        If UniqueOnly And InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) > 0 Then GoTo Continue
        Result = Result & Delimiter & ReturnVal
      ElseIf Not MatchWhole And CellVal Like "*" & SearchString & "*" Then
        If UniqueOnly And InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) > 0 Then GoTo Continue
        Result = Result & Delimiter & ReturnVal
      End If
Continue:
    Next
    
    LookUpConcat = Mid(Result, Len(Delimiter) + 1)
  End If
End Function

I do note, however, that some of the cells that match up in Column AJ have nothing in the return cell in Column Q... because of this, you are going to see one or more trailing backslashes (the empty string for a matching item is a value that will be returned).

First of all, thank you for the amazing coding.

I have implemented your code into worksheet and tried with basic table like:
1 a
1 b
3 c
5 d
3 e
4 f
etc.
It worked perfectly. However, when I used on my initial intended table, it keeps giving me "VALUE" error, my references are matching. Their, format is same "general". I am trying to concatenate comments.

So, is there any suggestion?

thank you for your time.