Return Full Cell Text based on Prefix Lookup

LeonardH

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

Well-known Member
Joined
Jan 28, 2015
Messages
732
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
7,958
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,439
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
7,958
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
13
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
7,958
Office Version
2007
Platform
Windows
In post#4 I explain how to insert the code so that it works automatically.
 

Forum statistics

Threads
1,078,504
Messages
5,340,763
Members
399,394
Latest member
farlow

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top