Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 736
  • Last Modified:

Excel help needed to query between 2 sheets

Hi All,

Need help with a formula or macro


Check column "C" in sheet 2 and map to Sheet 1 Colum "C"
If match found copy the Column "D" name in sheet2 name into the column "E" in sheet1
I will have more than one name from Column "D" to be placed in column "E"
Column "C"  in sheet2 will have identical names to match and place names in sheet1 "Column "E"


attached sample excel
0
bsharath
Asked:
bsharath
  • 8
  • 5
  • 5
1 Solution
 
mark_harris231Commented:
No attachment.  Could you also "dummy up" a few manual examples of what you expect for output?
0
 
bsharathAuthor Commented:
Attached sorry
Sample-excel.xlsx
0
 
rpawlyCommented:
If I am understanding this correctly, you can accomplish this using VLOOKUP.  This will check Column "C" in sheet1 with Column "C" in sheet2.  If they match, it will pull the value from Column "D" in sheet2 into Column "E" in sheet1.  The following formula needs to go into Column "E" Row "1" in sheet1:

=VLOOKUP(C1,Sheet2!C:D,2,FALSE)

You can then copy that down the entire column and the C1 value will change to match each cell accordingly.  

If you were thinking something else, let me know.

Brad
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
mark_harris231Commented:
@Brad - that won't address the need to concatenate multiple matches in Column E.  VB will be required.  Not my forte, but hopefully clarifies the request.
0
 
bsharathAuthor Commented:
1. The names are wrongly placed

2. I only get one name in the cell. There are cases where there will be 2 or more and i want them all into a single cell
0
 
rpawlyCommented:
Ok, I think I understand the situation now.  Can you attach your example file?  I would like to take a look and just make sure I have a full understanding.

Brad
0
 
rpawlyCommented:
This should be able to do what you are looking for:

VBA Code:
Function Lookup_concat(Search_string As String, _
  Search_in_col As Range, Return_val_col As Range)
Dim i As Long
Dim result As String
For i = 1 To Search_in_col.Count
  If Search_in_col.Cells(i, 1) = Search_string Then
    result = result & " " & Return_val_col.Cells(i, 1).Value
  End If
Next
Lookup_concat = Trim(result)
End Function

To add this function:
Press Alt-F11 to open visual basic editor
In the Insert Menu, click Module
Copy and paste the above vba code
Exit visual basic editor

Source: http://www.get-digital-help.com/2010/12/20/excel-udf-lookup-and-return-multiple-values-concatenated-into-one-cell/

You will need to use the following formula in the in Column "E" Row "1" of Sheet1:

=Lookup_concat(C1,Sheet2!C:C,Sheet2!D:D)

You can then paste that formula down the sheet and it will update accordingly.

Let me know if you have any other issues.

Brad
0
 
bsharathAuthor Commented:
Perfect
Can i have a coma delimiter between each name
0
 
rpawlyCommented:
Yes.  The code will need to be modified.  It is currently pulling the space from:

result = result & " " & Return_val_col.Cells(i, 1).Value

If you want it to just be a comma, change it to:

result = result & "," & Return_val_col.Cells(i, 1).Value

If you want it to be a comma and a space, change it to:

result = result & ", " & Return_val_col.Cells(i, 1).Value

Brad
0
 
bsharathAuthor Commented:
Used this
result = result & ", " & Return_val_col.Cells(i, 1).Value
But a coma is created before the word as well and one names where there is just 1
0
 
mark_harris231Commented:
This is, admittedly, a "guess" (and no points please), but I think this might work?

Function Lookup_concat(Search_string As String, _
  Search_in_col As Range, Return_val_col As Range)
Dim i As Long
Dim result As String
If Search_in_col.Cells(1.1) = Search_string Then
   result = Return_val_col.Cells(1,1).Value
   For i = 2 To Search_in_col.Count
     If Search_in_col.Cells(i, 1) = Search_string Then
       result = result & ", " & Return_val_col.Cells(i, 1).Value
     End If
   Next
End If
Lookup_concat = Trim(result)
End Function
0
 
rpawlyCommented:
Try this:

Function Lookup_concat(Search_string As String, _
  Search_in_col As Range, Return_val_col As Range)

Dim i As Long
Dim result As String

For i = 1 To Search_in_col.Count
  If Search_in_col.Cells(i, 1) = Search_string Then
    result = result & ", " & Return_val_col.Cells(i, 1).Value
  End If
Next
result = Right(result, Len(result) - 1)
Lookup_concat = Trim(result)

End Function


Brad
0
 
mark_harris231Commented:
Ah no....disregard.  That patently won't work as it presumes that the first match must be found in Cell(1,1).

How about this:

Function Lookup_concat(Search_string As String, _
  Search_in_col As Range, Return_val_col As Range)
Dim i As Long
Dim result As String
result = ""
   For i = 1 To Search_in_col.Count
     If Search_in_col.Cells(i, 1) = Search_string Then
       If result = "" Then
         result = Return_val_col.Cells(i,1).Value
      Else
       result = result & ", " & Return_val_col.Cells(i, 1).Value
       End If
     End If
  Next
Lookup_concat = Trim(result)
End Function
0
 
mark_harris231Commented:
ah no...disregard
 was directed at my "guess".
0
 
mark_harris231Commented:
@bsharath: In your example, Type (Column B) also appears to be relevant?  As currently written, the code only checks one condition (a match on Column C), and will return a result of "Ramke, Samuel" in Cell E9, which isn't supported by the data in Sheet2.  I think you will need the conditions to check for a match of both Column B and Column C, yes?
0
 
mark_harris231Commented:
This modification satisfies both conditions in my testing:

Function Lookup_concat(Search_string1 As String, _
  Search_in_col1 As Range, Search_string2 As String, _
  Search_in_col2 As Range, Return_val_col As Range)
Dim i As Long
Dim result As String
Results = ""
For i = 1 To Search_in_col1.Count
  If Search_in_col1.Cells(i, 1) = Search_string1 And Search_in_col2.Cells(i, 1) = Search_string2 Then
    If result = "" Then
      result = Return_val_col.Cells(i, 1)
    Else
      result = result & ", " & Return_val_col.Cells(i, 1).Value
    End If
  End If
Next
Lookup_concat = Trim(result)
End Function

Open in new window


I'd also recommend changing the formula in column E.  Currently, it checks the entire column, which is slow.  Instead, I'd recommend limiting the range of values checked:

=Lookup_concat(B4,Sheet2!$B$1:$B$25,C4,Sheet2!$C$1:$C$25,Sheet2!$D$1:$D$25)

In this example, the check is limited to just rows 1-25 on Sheet2 and is considerably faster.  

Optimally, you would also use Named Ranges and make this a dynamic range (i.e., sized to the amount of data in the respective columns).  This would allow you to grow Sheet2 data without adusting the formulas in Column E.

See here for discussion on Dynamic Named Ranges.

In practice, your formula in E would then be something like:

=Lookup_concat(B2,Type,C2,Hostname,Users)
0
 
bsharathAuthor Commented:
Thanks
I just need "C" checked
0
 
mark_harris231Commented:
Understood.  I would still recommend that you limit the ranges being checked in the Column E formulas.  It's quite slow as originally presented.

Also realized that I missed adding the link to the discussion on Dynamic Named Ranges.  See the following:

Link to Dynamic Range Introduction and practical application example
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 8
  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now