How to get one column names where another column is not-empty in google spreadsheet?

0
votes

I am trying to learn google spreadsheet and encountered following problem:

I am working on a google spreadsheet and I was stuck how to extract the second state name from the string.

MWE

  A                    
1 Company   City       Rejected
2 A         Dublin     Jan 1, 2021
3 B         Chicago    Jan 2, 2021
4 C         Atlanta
5 D         Seattle

Required answer:

Rejected companies = A,B

Since the Rejected column is not empty, the company names should be included and separated by comma.

Link to the google sheet (public shared file): https://docs.google.com/spreadsheets/d/10NzbtJhQj4hQBnZXcmwise3bLBIAWrE0qwSus_bz7a0/edit?usp=sharing

I would truly appreciate the effort.

1
google-sheets

1 Answers

2
votes

Here's a formula you can use: =textjoin(", ",False,QUERY(A2:L,"select B where H is not null",0))

This combines a list of all company that were rejected and sets comma as a delimiter for them`

Here's an example screenshot Example