Using Vlookup and Concatenate to compare columns across different sheets

[phantomx013]

Hi

I have 2 sheets in an excel file.

Sheet 1 has 2 columns as below:

#47094	#37919
#47073	#45258
#47175, #47178, #47179	#44610
N/A	#36090

<colgroup><col><col></colgroup><tbody>
</tbody>

Sheet 2 has 1 column

#37919

#45258

#44610

#36090

<colgroup><col></colgroup><tbody>
</tbody>

I want to compare sheet1.column2 with sheet1.column1 and put the corresponding results in sheet2.column2

What I did was I select the data in sheet1 and gave it name range "values". Next I used the formula =VLOOKUP(CONCATENATE("*",A1,"*"),Values,2,FALSE) for sheet2.column2

This give me only single corresponding value. e.g. it would give me below result

#37919 #47094
#45258 #47073
#44610 #47175
#36090 #N/A

Whereas I would like to have it in the below format:

#37919 #47094
#45258 #47073
#44610 #47175, #47178, #47189 <<<<<<<<<<<<<<
#36090 #N/A

Can someone help me please? I have about 6000 rows to compare !

Regards

MB

 

[AliGW]

Try this in Sheet 2 B1, copied down:

=VLOOKUP(A1,CHOOSE({1,2},Sheet1!B$1:B$4,Sheet1!A$1:A$4),2,0)

 

[phantomx013]

Hi, Thanks for the quick reply.
I used this formula in sheet2.column2 but it just give me N/A for all the entries

Just want to add that the entries I have in my excel are not in sequential order as shown in my post. So vlookup will have to scan through entire column of 6000 entries and and then pick the corresponding values and place them in 2nd sheet.

 

[AliGW]

Well, it works on your sample data. Could you provide a larger, more REALISTIC sample, otherwise further time will be wasted. Even better: provide a DropBox link to the actual workbook.

 

[phantomx013]

Sorry seems like I messed up my explanation a bit. I apologise.

Sheet 1 is as below:

#47094 #37919
#47073 #45258
#47175, #47178, #47179 #44610
#47175,#47178 #36090

Sheet 2 should be as below


#47094 #37919
#47073 #45258
#47175 #44610, #36090
#47178 #44610, # 36090
#47179 #44610

I Really Apologise !!!!

 

[AliGW]

This is a completely different problem, and I'm afraid I can't help you with it. Somebody good with INDEX and MATCH functions may be able to help. Good luck and sorry I can't give you a solution.

 

[phantomx013]

I will send you a sample excel sheet or dropbox link !!!

 

[phantomx013]

Sorrt, do you know how can I delete this thread?

 

[AliGW]

I will send you a sample excel sheet or dropbox link !!!

It would help anyone able to help you, but unfortunately I don't know how to solve this problem, as it's very different to the problem you originally posted.

 

[Rick Rothstein]

I cannot think of a formula way to do what you want, but I do have a UDF (user defined function) that seems to work...

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
  
  SearchString = Replace(SearchString, "#", "|")
 
  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
      CellVal = Replace(SearchRange(X), "#", "|")
      If MatchCase Then
        CellVal = CellVal
      Else
        CellVal = UCase(CellVal)
      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

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use LookUpConcat just like it was a built-in Excel function. For example, put this in cell B1 on Sheet2 and copy it down to the end of the values in Column A on Sheet2...

=LookupConcat(A1,Sheet1!A$1:A$4,Sheet1!B$1:B$4,", ",FALSE)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.