Only Return Values that Start with a Specific Letter

[silverbluemoon]

I found the following awesome script on digital-help.com that allows me to lookup and return multiple values concatenated into one cell (which I need to do):

[COLOR=#000000]Function Lookup_concat(Search_string As String, _[/COLOR]
  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
NextLookup_concat = Trim(result)
[COLOR=#000000]End Function[/COLOR]

THEN, I only have to enter THIS in each cell:

=Lookup_concat(Look_up_value, Search_in_column, Concatenate_values_in_column)

This PERFECT, except that I only need to return values that start with a specific letter. I need to figure this out quickly, so please help if you can! Thank you!!

 

[Rick Rothstein]

I found the following awesome script on digital-help.com that allows me to lookup and return multiple values concatenated into one cell (which I need to do):

[COLOR=#000000]Function Lookup_concat(Search_string As String, _[/COLOR]
  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
NextLookup_concat = Trim(result)
[COLOR=#000000]End Function[/COLOR]

THEN, I only have to enter THIS in each cell:

=Lookup_concat(Look_up_value, Search_in_column, Concatenate_values_in_column)

This PERFECT, except that I only need to return values that start with a specific letter. I need to figure this out quickly, so please help if you can! Thank you!!

I have modified the code for you... to use it, pass the letter you want the found cells to start with followed by an asterisk (both characters in quote marks) into the first argument.

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) Like Search_string Then
      result = result & " " & Return_val_col.Cells(i, 1).Value
    End If
  Next
  Lookup_concat = Trim(result)
End Function

 

[silverbluemoon]

I have modified the code for you... to use it, pass the letter you want the found cells to start with followed by an asterisk (both characters in quote marks) into the first argument.

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) Like Search_string Then
      result = result & " " & Return_val_col.Cells(i, 1).Value
    End If
  Next
  Lookup_concat = Trim(result)
End Function

Thank you so much, but I don't see the change in the code above. Am I just missing it?

 

[Rick Rothstein]

Thank you so much, but I don't see the change in the code above. Am I just missing it?

I changed the equal sign to the word "Like" (Like is a comparison operator that let's you use wildcards in a text pattern for the comparison).

 

[silverbluemoon]

Oh, I see! So, please forgive yet another beginner question, but where do I put the letter I want it to find? And this will give me the string I want it to find at the beginning of the contents of the cell?

 

[Rick Rothstein]

Oh, I see! So, please forgive yet another beginner question, but where do I put the letter I want it to find? And this will give me the string I want it to find at the beginning of the contents of the cell?

Sorry, since you posted the original function, I figured you were familiar with them. First of all, since it is a function, actually more technically a UDF (User Defined Function), you use it just like any other Excel function... if you want the output to appear in, say, cell C2, you put the formula (basically for your intended use, an equal sign followed by the function) in cell C2. The function takes three arguments... the first argument is a text value consisting of the letter you want to find at the beginning of the text followed by an asterisk (which is a wildcard standing in for zero or more other characters), the second argument is the range you want to search for that begins with that letter and the third argument is the range you want to lookup and return values from. So, let's say you wanted to search A2:A100 for text starting with the letter "E" and return the corresponding values in C2:C10, the formula you would in the cell you want to display the concatenated values would be this...

=Lookup_concat("E*",A2:A100,C2:C100)

Alternately, if you wanted store the letter in a cell, say C1, then your formula would look like this...

=Lookup_concat(C1&"*",A2:A100,C2:C100)

 

[silverbluemoon]

Oh I see. I am familiar, in general, how arguments are passed to the function -- in this case the Lookup_concat takes a cell and looks up it up in another column, then returns the value from yet another column. But in this case, the search list is a bunch of part numbers. I have thousands and cannot change them all to add an asterisk; the part numbers must remain the same. I am looking up a list of part numbers I have in another list and getting all t he values that apply. In this case, all of our vehicles that these parts go into. So is there a way to tell the function to search for a specific value I specify, without it being in the search (part numbers I'm searching for) column?

 

[Rick Rothstein]

Oh I see. I am familiar, in general, how arguments are passed to the function -- in this case the Lookup_concat takes a cell and looks up it up in another column, then returns the value from yet another column. But in this case, the search list is a bunch of part numbers. I have thousands and cannot change them all to add an asterisk; the part numbers must remain the same. I am looking up a list of part numbers I have in another list and getting all t he values that apply. In this case, all of our vehicles that these parts go into. So is there a way to tell the function to search for a specific value I specify, without it being in the search (part numbers I'm searching for) column?

Okay, let's see if I have this correct... you have a column of part numbers and you want to look something up based on the leftmost character of that part number and concatenate them together. If that is correct, then you are going to have to tell us what column the part numbers are listed in, what column their first character should be looked up in and what column the concatenations should come from.