Return Full Cell Text based on Prefix Lookup

[LeonardH]

Hello Everyone, thanks in advance!

On Sheet 1 in cell A1, I have a can type a prefix (e.g. "CZ1-1123TRH-2200-") and there is a table on a Sheet 2 wherein Column A includes hundreds of values with several prefixes and multiple suffixes for each that create several "assets" (e.g. CZ1-1123-TRH2200-ACS, CZ1-1123-TRH2200-BRS, CZ1-1123-TRH2200-BRS-FR-R, etc.).

When I put in the prefix in A1, I would like to have a way for it to automatically grab the full asset names in every cell in Column A of Sheet 2 that contain that prefix and create a list (perhaps beginning with B2 of Sheet 1).

Any thoughts?

 

[CyrusTheVirus]

can you please provide some examples? copy/paste both data sets showing starting point(s) and expected results.

 

[sadboy309]

*syntax*
Sheet2:
[B2]=<several prefixes> & Sheet1!A1 & <multiple suffixes>
With:
<several prefixes> and <multiple suffixes> can a cell A2 on sheet2, or it is Text value example: = Sheet1!A1 & "ACS" OR = Sheet1!A1 & A2

 

[DanteAmor]

Put the following code in the events of your sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        If Target.Value = "" Then Exit Sub
        If Target.Count > 1 Then Exit Sub
        Dim s As Worksheet, b As Range, r As Range, cell As String
        
        Rows("2:" & Rows.Count).ClearContents
        Set s = Sheets("Sheet2")
        Set r = s.Range("A:A")
        Set b = r.Find(Target.Value, LookIn:=xlValues, lookat:=xlPart)
        If Not b Is Nothing Then
            cell = b.Address
            Do
                If Left(b.Value, Len(Target.Value)) = Target.Value Then
                    Range("B" & Range("B" & Rows.Count).End(xlUp)(2).Row).Value = b.Value
                End If
                Set b = r.FindNext(b)
            Loop While Not b Is Nothing And cell <> b.Address
        End If
    End If
End Sub

Event Sheet:
Right click the tab of the sheet "Sheet1", select view code & paste the code into the window that opens up.

 

[Rick Rothstein]

Put the following code in the events of your sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        If Target.Value = "" Then Exit Sub
        If Target.Count > 1 Then Exit Sub
        Dim s As Worksheet, b As Range, r As Range, cell As String
        
        Rows("2:" & Rows.Count).ClearContents
        Set s = Sheets("Sheet2")
        Set r = s.Range("A:A")
        Set b = r.Find(Target.Value, LookIn:=xlValues, lookat:=xlPart)
        If Not b Is Nothing Then
            cell = b.Address
            Do
                If Left(b.Value, Len(Target.Value)) = Target.Value Then
                    Range("B" & Range("B" & Rows.Count).End(xlUp)(2).Row).Value = b.Value
                End If
                Set b = r.FindNext(b)
            Loop While Not b Is Nothing And cell <> b.Address
        End If
    End If
End Sub

Event Sheet:
Right click the tab of the sheet "Sheet1", select view code & paste the code into the window that opens up.

Based on the OP's posted sample data and prefixes, I believe this loopless Change event code will also work...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Arr As Variant
  If Target.Address(0, 0) = "A1" Then
    Application.EnableEvents = False
    Range("B:B").ClearContents
    If Len(Target.Value) Then
      Arr = Filter(Application.Transpose(Sheets("Sheet2").Range("A1", Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp))), Range("A1").Value, True, vbTextCompare)
      Range("B2").Resize(1 + UBound(Arr)) = Application.Transpose(Arr)
    End If
    Application.EnableEvents = True
  End If
End Sub[/td]
[/tr]
[/table]

 

[DanteAmor]

Hi Rick, You gave me an idea to use the filter

Please LeonardH :

On Sheet1 in cell "A1" put the tittle "ASSET", on Sheet2 in cell "A1" put the same tittle "ASSET"
Now, on sheet1 in cell "A2" write your prefix.

Use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Address <> "$A$2" Then Exit Sub
    Range("B2:B" & Rows.Count).ClearContents
    Sheets("Sheet2").Range("A:A").AdvancedFilter xlFilterCopy, Range("A1:A2"), Range("B1")
End Sub

 

[LeonardH]

Thanks everyone for your replies and sorry that I've taken so long to respond. I guess what would be ideal is a =FILTER function like the one that's available in Google Sheets. I saw that Microsoft is developing the function, but has yet to release it in an update.

Anyway, here are some screenshots that I hope give more context to what I'm talking about. In the first picture, there is a drop-down menu with all the prefixes and then I would like all of the full Asset Numbers (in the second screenshot) to fill in below in column G where currently only the prefix is.

DanteAmor, I think you might have something there that will work. Would you possibly be able to update the references? Also, I'll admit that I'm a bit of a noob when it comes to code. I'm used to formulas. Will the code automatically run? How do I go about inserting it?

Thanks again for all your help!

LeonardH

 

[DanteAmor]

In post#4 I explain how to insert the code so that it works automatically.