I need to create a QUERY
function to retrieve values from different columns, based on the following 2 criteria:
If the value in column A is "Company name changed", I need columns B and C. And if the value in column A is "Email changed", I need to retrieve the corresponding values to that record from columns B, C, D and E. All of the above in the same table.
Please, excuse me for not sharing a dummy sheet, but I have corporate security restriction, which is not allowing me to make any sheet visible to anyone outside the company.
Source tab:
+----------------------+------+-------------+--------------------------+----------------------------+
| A | B | C | D | E |
+----------------------+------+-------------+--------------------------+----------------------------+
| Status | ID | Company | Email | Generic Email |
+----------------------+------+-------------+--------------------------+----------------------------+
| Email changed | 223A | ABC Capital | george@abccapital.com | info@abccapital.com |
+----------------------+------+-------------+--------------------------+----------------------------+
| In progress | 446g | DEF Finance | stephan@deffinance.co.uk | enquiries@deffinance.co.uk |
+----------------------+------+-------------+--------------------------+----------------------------+
| Company name changed | 233A | GHI Dealing | michael@ghidealing.ru | contact@ghidealing.ru |
+----------------------+------+-------------+--------------------------+----------------------------+
| Dissolved | 334s | JKL Brokers | john@jklbrokers.au | help@jklbrokers.au |
+----------------------+------+-------------+--------------------------+----------------------------+
Output tab:
+------+-------------+-----------------------+---------------------+
| ID | Company | Email | Generic Email |
+------+-------------+-----------------------+---------------------+
| 223A | ABC Capital | george@abccapital.com | info@abccapital.com |
+------+-------------+-----------------------+---------------------+
| 233A | GHI Dealing | | |
+------+-------------+-----------------------+---------------------+
What could be the possible workaround for this?
={QUERY(A1:E, "select B, C, D, E
where A = 'Email changed'", 1);
QUERY(A2:E, "select B, C, ' ', ' '
where A = 'Company name changed'
label ' ''', ' '''", 0)}