Pull text from a list if it contains specific text

[snjpverma]

If A1 is entered Tom, then all the cell values containing Tom should get updated in B1, C1, D1 and so on.

The data with names is in A3:A100 where the A1 has to be searched for and values to be pulled from.

Vlookup pulls only the first value.

Please provide formula as well as Macro suggestions.
If the macro is difficult I will go for a formula solution because I am pretty weak in VBA.

 

[DanteAmor]

Try this array formula

Book1
	A	B	C	D
1	Tom	Tom B	Tom M	John Tom
2
3	Tom B
4	Alan C
5	John D
6	Tom M
7	Will S
8	John Tom
Sheet2

Cell Formulas
Range	Formula
B1:D1	B1	{=IFERROR(INDEX($A$3:$A$8, SMALL(IF(ISNUMBER(SEARCH($A$1,$A$3:$A$8)),ROW($A$3:$A$8)),COLUMNS($B$2:B2))-2),"")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

______________________________________

I also put a macro for your sheet events

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Target.Address(0, 0) = "A1" Then
    Dim c As Range, j As Long
    j = 2
    Range(Cells(1, 2), Cells(1, Columns.Count)).ClearContents
    For Each c In Range("A3", Range("A" & Rows.Count).End(xlUp))
      If c.Value Like "*" & Target.Value & "*" Then
        Cells(1, j).Value = c
        j = j + 1
      End If
    Next
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[snjpverma]

Those are such amazing solutions. Kudos!

Looking at your code makes me inclined to use the VBA method as it is more feasible as well. I realized that formula would be very cumbersome in this situation.
However, I would request you to make some small changes based on the actual scenario. The data above was a related sample.

After reading the below, if you think another approach (a third option) is better, then I would certainly request you to suggest me the same.

Actual scenario.

Sheet 1 where the actual data entry happens and where the names in Column D has to match with the name in sheet 2

Book1
	D	E	F	G
1	Name	Comments	Suggestions
2	Tom
3	Alan
4	Judy
5	Edwin
6
Sheet1

Sheet 2 where the List is saved. (200 names)

Book1
	A
1	200 Names
2	Tom Billow
3	Alan C
4	John D
5	Mony Tom
6	Will S
7	H Tom
Sheet2

I find people putting names in sheet 1 Column D which is not standardized (i.e. not in Sheet 2). This create trouble when I prepare reports at the end.
I tried Data Validation but then it is very difficult for them to go through a huge drop down list of 200 names that wold come in Data validation.
Your VBA code pointed toward a wonderful solution.
I think, the moment a person enters a name in Column D which is not matching with the list in sheet 2 (A2:A200), then the names related to that should appear as a suggestion in G1, H1, I1, J1 and so on. this will make it easier for them to copy and paste it in Column D. --- new shortcut method
(As of now they have to go to Sheet 2 and then press (Ctrl + F) to search the names and then copy it and come back to sheet 1 and paste it) -- long old method
If the name entered is correct i.e. matching with any one of names in Sheet 2, then the suggestions need not appear.

Since I am not familiar with much of VBA. I cannot think of a solution to delete the suggestions.
For example,
A person enters Tom in D2, suggestions appear in G2:J2. Assuming he copies the one in H2 (the one that was needed) and pastes it in D2. The work gets completed.
Can it happen that once the correct name is entered in Sheet 1 Column D, the suggestion in that row will get deleted?
If both suggestion and deletion happens successfully, that would make my day.

 

[snjpverma]

Anyone please?

 

[DanteAmor]

Instead of a validation list, you could put an ActiveX Control Combobox.

____________________________________________________________________________
You can capture letters and show you the suggested names:

____________________________________________________________________________
To add Combo Box Control ActiveX:

1. Go To Developer Tab and then click Insert from the Controls
2. Click on the Combo Box from the Activex Controls group.
3. Drag a Combo Box on the Worksheet.

____________________________________________________________________________
The code in sheet event:

Private Sub ComboBox1_Change()
  Dim i As Long
  With Me.ComboBox1
    .List = Sheets("Sheet2").Range("A2:A" & Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row).Value
    For i = .ListCount - 1 To 0 Step -1
      If InStr(1, .List(i), .Text, 1) = 0 Then .RemoveItem i
    Next i
    .DropDown
  End With
End Sub

Private Sub ComboBox1_Click()
  Call Fill_Cell
End Sub
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  If KeyCode = 13 Then Call Fill_Cell
End Sub
Sub Fill_Cell()
  If ComboBox1.ListIndex <> -1 Then
    Range("D2").Value = ComboBox1.Value
  End If
End Sub

____________________________________________________________________________
SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[snjpverma]

Thanks DanteAmor,
I tried the code and it is working Absolutely fine.
However, I believe the Combo box will be useful for one particular cell.
But my Data Entry goes down from D2 e.g. D2, D3 D4 D5 D6 and so on.

So if a person Enter "Tom" in D4, the suggestions appear in the same row i.e. G4, H4, I4 and so on.. I don't think there will be more than 7 suggestions though.

I am sorry I didn't mention it in the first instance.

 

[Akuini]

But my Data Entry goes down from D2 e.g. D2, D3 D4 D5 D6 and so on.

So if a person Enter "Tom" in D4, the suggestions appear in the same row i.e. G4, H4, I4 and so on.. I don't think there will be more than 7 suggestions though.

You may want to try a dynamic searchable combobox, you can find an example here:

Best way to create a searchable drop-down list with auto-complete functionality to cells in a column?

Hi Folks, I have a time-sheet workbook with two worksheets (ws). In sheet 1 (the time-sheet ws), Column A is "Employee". Time-sheet worksheet. Employee Start time Finish time Hours worked Joe Blogs Jane Doe I wish to be able to start typing a different employee on each row...

www.mrexcel.com

 

[Peter_SSs]

I think this code implements your suggestion from post 3. Test in copy of your workbook. To implement ..
1. Right click the Sheet1 name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1. (You will need to delete any existing Worksheet_Change code currently in that module)
3. Test by entering/deleting/modifying values in column A of Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim s As String
  Dim vals As Variant, ListOfNames As Variant
  
  If Target.Count = 1 And Target.Column = 1 And Target.Row > 1 Then
    Application.EnableEvents = False
    ListOfNames = Application.Transpose(Sheets("Sheet2").Range("A2", Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)).Value)
    s = Target.Value
    Target.Interior.Color = xlNone
    Target.Offset(, 2).Resize(, UBound(ListOfNames)).ClearContents
    If Len(s) > 0 Then
      If IsError(Application.Match(s, ListOfNames, 0)) Then
        vals = Filter(ListOfNames, s, True)
        If UBound(vals) = -1 Then
          Target.Offset(, 2).Value = "Error"
          Target.Interior.Color = vbRed
        Else
          Target.Offset(, 2).Resize(, UBound(vals) + 1).Value = vals
          Target.Interior.Color = 5296274
        End If
      Else
        Target.Offset(, 2).Resize(, UBound(ListOfNames)).ClearContents
      End If
    Else
      Target.Offset(, 2).Resize(, UBound(ListOfNames)).ClearContents
    End If
    Application.EnableEvents = True
  End If
End Sub

If you enter a partially correct name, the matching suggestions will appear beside it in that same row & the target cell will go green.
If a fully correct name is entered (or the cell is cleared) any names to the right will be cleared and any colour removed.
If no partial match is found then the target cell will go red and "Error" will appear beside the value in that row.

Here is a screen shot where a partially correct name has been entered. the options containing that text are shown in columns C:E in this case.

Book1
	A	B	C	D	E	F
1	Name	Comments	Suggestions
2
3	Tom		Tom B	Tom M	John Tom
4
Sheet 1

 

[snjpverma]

Peter sss,. Thank you so much. That's exactly what I need as per shown in your example.
I'll try it and will update you at the earliest.

 

[DanteAmor]

Thanks DanteAmor,
I tried the code and it is working Absolutely fine.
However, I believe the Combo box will be useful for one particular cell.
But my Data Entry goes down from D2 e.g. D2, D3 D4 D5 D6 and so on.

I am sorry I didn't mention it in the first instance.

Dont worry. I'm just trying to give you other options for what you need.

Just change this line
Range("D2").Value = ComboBox1.Value

For this:
Range("D" & Rows.Count).End(xlUp)(2).Value = ComboBox1.Value