Forum Discussion
If column has a word within it pull the name in another column
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
| First | Last | City |
| Adam | Elliott | Sarnia, London, Windsor |
| Lisa | Ferreira | London |
| Windsor | Sarnia | London |
| Adam Elliott | Adam Elliott | Adam Elliott |
| Lisa Ferreira |
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.
10 Replies
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.
- 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 TextThe expression error occurs if no city is entered for a first and last name.
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.
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.
=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.
