Return Full Cell Text based on Prefix Lookup

LeonardH

New Member
Joined
Dec 21, 2013
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
can you please provide some examples? copy/paste both data sets showing starting point(s) and expected results.
 
Upvote 0
*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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.

d2bb9d19400f65c30c096821a595877c.jpg



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
 
Upvote 0
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.



s!AgQ7-waf38omgtsLvKZ_S6XZ5Dg1fw
s!AgQ7-waf38omgtsLvKZ_S6XZ5Dg1fw
Capture.PNG
Capture%201.PNG


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
 
Upvote 0
In post#4 I explain how to insert the code so that it works automatically.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top