SOLVED

If column has a word within it pull the name in another column

Brass Contributor

Hi there,

I have the below in a ss in columns A,B,C respectively. In another tab I want to have the column heading be a city. ie. Windsor. and I want to pull the first and last names of anyone who says windsor (see 2nd ss below) in the list below. If there is no data then to leave it blank or write "NA". Can someone help me please? Not sure if it can be a pivot table because there is multiple cities in 1 cell

 

FirstLastCity
AdamElliottSarnia, London, Windsor
LisaFerreiraLondon

 

WindsorSarniaLondon
Adam ElliottAdam ElliottAdam Elliott
  Lisa Ferreira
10 Replies

@LisaMarie1981 

=IF(ISNUMBER(SEARCH(E$1,$C2)),$A2&" "&$B2,"")

 

This could be a simple solution if you create the column headings with the help of text to columns and remove duplicates and transpose. The formula is in cell E2 and filled across range E2:J5 in the example. For illustration the tables are in the same spreadsheet.

names and cities.png

 

@LisaMarie1981 

In A2 on the second sheet:

=FILTER('People Sheet'!$A$2:$A$200&" "&'People Sheet'!$B$2:$B$200, ISNUMBER(SEARCH(A1, 'People Sheet'!$C$2:$C$200)), "")

Replace People Sheet with the real name of the first sheet, and adjust the ranges as needed.

Then fill to the right.

@LisaMarie1981 

Another alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

 

The data layout in the screenshot and in the attached file is for illustration. You can as well place the green result table in another worksheet.

If column has a word within it pull the name in another column.png

Thanks Oliver...this would be great but I get this error when I try to refresh
[Expression.Error] we cannot convert the value null to type Text

@LisaMarie1981 

The expression error occurs if no city is entered for a first and last name.

expression.png

 

If it should be possible to enter first and last name without at least one city then the query must be changed accordingly. For example with a changed query the result could look like in the screenshot below where the first and last name is entered under "no city entered" if there isn't an entry for city in the blue table.

account for missing city.png

@OliverScheurich 

LisaMarie1981_0-1708456399338.png

The city is filled out, but still getting error

@LisaMarie1981 

Can you attach your file without sensitive data? Another strange thing in your file is that the M code returns "Adam Example" while "Ann" and "Example" was entered. In the last three rows of the blue table there is no data entered. Without seeing your file and code i can't help you i'm afraid. Alternatively you can enter the data from your file in the file i already sent you and run the query.

first last city.png

@OliverScheurich attached below. I just downloaded yours that you sent and changed Adam to Ann and added another row and then clicked refresh. 

best response confirmed by HansVogelaar (MVP)
Solution

@LisaMarie1981 

The file you sent is my first suggestion where all data must be entered. Attached is the file with the changed M code which accepts rows without a city. I changed the name of the file from "If column has a word within it pull the name in another column" to "CHANGED If column has a word within it pull the name in another column" for easier identification.

AMAZING!! Thank you so much, this is perfect!
1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

@LisaMarie1981 

The file you sent is my first suggestion where all data must be entered. Attached is the file with the changed M code which accepts rows without a city. I changed the name of the file from "If column has a word within it pull the name in another column" to "CHANGED If column has a word within it pull the name in another column" for easier identification.

View solution in original post