I want to filter addresses (column A) if they contain any city name from city list (column B). Here is how data looks like:
| Adresses (Col A) | Cities (Col B) |
|---|---|
| 900 Rancho Diego Pkwy, Buellton, CA 92028 | Ventura |
| 3850, Toepperwein Rd, Antonio, TX 78264 | San Antonio |
| 3250 El Camino Real, Atascadero, CA 93444 | Buellton |
| 2340 Palma Dr, Ventura, CA 93090 | New York |
| Valley Rd, Santa Maria, CA 93420 |
In the above table, only 2 cities (Ventura, Buellton) in column B are found in Addresses in Column A, so the output should be:
900 Rancho Diego Pkwy, Buellton, CA 92019
2340 Palma Dr, Ventura, CA 93003
I tried following formulas but to no effect:
=FILTER(A2:A, REGEXMATCH(A2:A, B2:B))
=FILTER(A2:A, MATCH(A2:A, B2:B,0))
=FILTER(A2:A, COUNTIF(B2:B, A2:A))
How to compile list of addresses (Col A) if any city name (Col B) is found in the address? Any help would be much appreciated.
CodePudding user response：
withing GSheets please try:
UPDATED FORMULA:
- this strictly extracts the cities names within the address to tally against the list in Column B to avoid random mismatches.
=FILTER(A2:A,REGEXMATCH(BYROW(A2:A,LAMBDA(ax,IF(ax="",,IFNA(IFNA(REGEXEXTRACT(ax,", ([^,]*), [A-Z]{2}"),REGEXEXTRACT(ax,", ([^,]*), [^,]*$")))))),"(?i)"&TEXTJOIN("|",1,B2:B)))
CodePudding user response：
Use FILTER() with MMULT() in Excel
• Formula used in cell E3
=FILTER(B3:B7,MMULT(N(ISNUMBER(SEARCH(TRANSPOSE(C3:C6),B3:B7))),ROW(C3:C6)^0))
Works in Google-Sheets as well:
• Formula used in cell E3
=FILTER(B3:B7, MMULT(N(ISNUMBER(SEARCH(TRANSPOSE(C3:C6),B3:B7))),SEQUENCE(ROWS(C3:C6),1,1,0)))
An alternative approach using BYROW()
• Formula used in cell E3
=FILTER(B3:B7,BYROW(N(ISNUMBER(SEARCH(TOROW(C3:C6),B3:B7))),LAMBDA(m,SUM(m))))
For Google-Sheets replace the TOROW() with TRANSPOSE()
• Formula used in cell E3
=FILTER(B3:B7,BYROW(N(ISNUMBER(SEARCH(TRANSPOSE(C3:C6),B3:B7))),LAMBDA(m,SUM(m))))
Instead of taking all the rows it is better to use Table Structured References
• Formula used in cell E3
=LET(_cityName,FILTER(C3:C1048576,C3:C1048576<>""),
_addresses,FILTER(B3:B1048576,B3:B1048576<>""),
FILTER(_addresses,MMULT(N(ISNUMBER(SEARCH(TRANSPOSE(_cityName),_addresses))),SEQUENCE(ROWS(_cityName)))))
With BYROW()
• Formula used in cell E3
=LET(_cityName,FILTER(C3:C1048576,C3:C1048576<>""),
_addresses,FILTER(B3:B1048576,B3:B1048576<>""),
_searchArray,N(ISNUMBER(SEARCH(TOROW(_cityName),_addresses))),
FILTER(_addresses,BYROW(_searchArray,LAMBDA(m,SUM(m)))))
Or, create a Defined Name in Name Manager with the LAMBDA() formula and use the range as per your need.
Refers To:
=LAMBDA(Cities,Addresses,
LET(_cityName,FILTER(Cities,Cities<>""),
_addresses,FILTER(Addresses,Addresses<>""),
_searchArray,N(ISNUMBER(SEARCH(TOROW(_cityName),_addresses))),
FILTER(_addresses,BYROW(_searchArray,LAMBDA(m,SUM(m))))))(C3:C1048576,B3:B1048576)
• Formula used in cell E3
=LISTDATA(C3:C1048576,B3:B1048576)
In Google-Sheets
=FILTER(B3:B,BYROW(N(ISNUMBER(SEARCH(TRANSPOSE(FILTER(C3:C,C3:C<>"")),B3:B))),LAMBDA(m,SUM(m))))
CodePudding user response：
You were close with this
=FILTER(A2:A, REGEXMATCH(A2:A, B2:B))
The second argument of REGEXMATCH shouldn't be a range though, this will work:
=FILTER(A2:A,REGEXMATCH(A2:A,TEXTJOIN("|",1,B2:B)))