Return Full Cell Text based on Prefix Lookup

LeonardH

New Member
Joined
Dec 21, 2013
Messages
15
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?
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

CyrusTheVirus

Well-known Member
Joined
Jan 28, 2015
Messages
736
Office Version
365
Platform
Windows
can you please provide some examples? copy/paste both data sets showing starting point(s) and expected results.
 

sadboy309

Board Regular
Joined
Oct 15, 2014
Messages
88
*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

Well-known Member
Joined
Dec 3, 2018
Messages
11,782
Office Version
2007
Platform
Windows
Put the following code in the events of your sheet1

Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,926
Office Version
2010
Platform
Windows
Put the following code in the events of your sheet1

Code:
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...
Code:
[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]
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,782
Office Version
2007
Platform
Windows
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:

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

New Member
Joined
Dec 21, 2013
Messages
15
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,782
Office Version
2007
Platform
Windows
In post#4 I explain how to insert the code so that it works automatically.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,508
Messages
5,469,034
Members
406,628
Latest member
jared92

This Week's Hot Topics

Top