Reputation: 329
Query values from single column and fill columns, both based on multiple criteria
I am trying to query solely the IDs of clients that meet specific criteria from a source tab to an output tab and fill 2 columns with static values and 2 columns with dynamic values, based on criteria.
In the source tab I have:
+-----------------------+------+
| Status | ID |
+-----------------------+------+
| Retired/Deceased | 2a33 |
+-----------------------+------+
| Liquidation | 1sTR |
+-----------------------+------+
| Dissolved | 3B76 |
+-----------------------+------+
| Released from company | 463z |
+-----------------------+------+
| Active | 557g |
+-----------------------+------+
| In progress | zz34 |
+-----------------------+------+
| Demo | cc56 |
+-----------------------+------+
Please note, that there are 7 criteria values and I need the output for only 4 of them. Meaning that I need 4 values, based on which there will be binary fill of dynamic columns. The other 3 values are obsolete.
From these 4 values, if I have eg. Criteria 1, then I will have one fill of the 2 dynamic columns, if not (for the other 3 values) I will have other fill values.
So I guess simply going with a binary solution for the selection of the specific values is not applicable.
In the output tab logic:
+--------------------------------------------------------------------------------+
| Output tab |
+--------------------------------------------------------------------------------+
| ID | Status | Reason | Comment | Detail |
+----+--------+--------+----------------------------+----------------------------+
| A1 | Static | Static | If criteria = criteria 1, | If criteria = criteria 1, |
| | | | then null, | then value 1, |
| | | | else criteria value | else value 2 |
+----+--------+--------+----------------------------+----------------------------+
| B1 | Static | Static | If criteria = criteria 1, | If criteria = criteria 1, |
| | | | then null, | then value 1, |
| | | | else criteria value | else value 2 |
+----+--------+--------+----------------------------+----------------------------+
| C1 | Static | Static | If criteria = criteria 1, | If criteria = criteria 1, |
| | | | then null, else | then value 1, |
| | | | criteria value | else value 2 |
+----+--------+--------+----------------------------+----------------------------+
| D1 | Static | Static | If criteria = criteria 1, | If criteria = criteria 1, |
| | | | then null, | then value 1, |
| | | | else criteria value | else value 2 |
+----+--------+--------+----------------------------+----------------------------+
| E1 | Static | Static | If criteria = criteria 1, | If criteria = criteria 1, |
| | | | then null, | then value 1, |
| | | | else criteria value | else value 2 |
+----+--------+--------+----------------------------+----------------------------+
| F1 | Static | Static | If criteria = criteria 1, | If criteria = criteria 1, |
| | | | then null, | then value 1, |
| | | | else criteria value | else value 2 |
+----+--------+--------+----------------------------+----------------------------+
| G1 | Static | Static | If criteria = criteria 1, | If criteria = criteria 1, |
| | | | then null, | then value 1, |
| | | | else criteria value | else value 2 |
+----+--------+--------+----------------------------+----------------------------+
Dummy output tab:
+-----------------------+------+-------------------+-------------+------------------+---------------------------------+
| Status | ID | Status | Reason | Comment | Detail |
+-----------------------+------+-------------------+-------------+------------------+---------------------------------+
| Retired/Deceased | 2a33 | Unable to proceed | Unspecified | Retired/Deceased | Retired/No longer in business |
+-----------------------+------+-------------------+-------------+------------------+---------------------------------+
| Liquidation | 1sTR | Unable to proceed | Unspecified | Liquidation | Retired/No longer in business |
+-----------------------+------+-------------------+-------------+------------------+---------------------------------+
| Dissolved | 3B76 | Unable to proceed | Unspecified | Dissolved | Retired/No longer in business |
+-----------------------+------+-------------------+-------------+------------------+---------------------------------+
| Released from company | 463z | Unable to proceed | Unspecified | (null) | No longer works for the company |
+-----------------------+------+-------------------+-------------+------------------+---------------------------------+
The column 'Status' is not required. I added it just for reference and readability.
Apologies, but I have corporate security restrictions for sharing links to Google Sheets.
The part that I struggle most with is that in column 4 (the first one from the dynamic ones) needs to return the value of the criteria from column 2 from the source tab.
So far I have worked my way around the first part of the query, where I QUERY the IDs based on multiple criteria, labelled and filled the static values columns.
=QUERY(Data!$A$3:$BN,
"SELECT B, 'Unable to proceed', 'Unspecified'
WHERE A = 'Retired/Deceased'
OR A = ''Liquidation'
OR A = 'Dissolved'
OR A = 'Released from company'
AND A IS NOT NULL
LABEL 'Unable to proceed' 'Unspecified' , 'Status' 'Reason'", 1)
However, I am struggling with the dynamic columns, based on multiple criteria.
I looked up ARRAYFORMULA with IFERROR and VLOOKUP in a nested QUERY, but was not able to work my way around it.
Also, I am quite interested in how this would work if there were more than 2 options of values to fill columns 3 and 4 in the output tab.
As far as I know, the way to work around 2 values based on criteria is to nest an IFERROR function to make it binary. But what if there were more than 2 values to fill the arrays with?
Upvotes: 1
Views: 330
Answers (2)
Reputation: 1
based on ID:
=QUERY({QUERY(Data!$A$1:$B,
"SELECT B, 'Unable to proceed', 'Unspecified'
WHERE A = 'Retired/Deceased'
OR A = 'Liquidation'
OR A = 'Dissolved'
OR A = 'Released from company'
AND A IS NOT NULL
LABEL 'Unable to proceed''Status', 'Unspecified''Reason'", 1),
QUERY(ARRAYFORMULA(IFERROR(VLOOKUP(
QUERY(Data!$A$2:$B,
"SELECT B
WHERE A = 'Retired/Deceased'
OR A = 'Liquidation'
OR A = 'Dissolved'
OR A = 'Released from company'
AND A IS NOT NULL", 0),
{"2a33", "Retired/Deceased", "Retired/No longer in business";
"1sTR", "Liquidation", "Retired/No longer in business";
"3B76", "Dissolved", "Retired/No longer in business";
"463z", "", "No longer works for the company"}, {2, 3}, 0), )),
"LABEL Col1'Comment', Col2'Detail'", 0)}, , 0)
Upvotes: 1
Reputation: 1
based on status:
=QUERY({QUERY(Data!$A$1:$B,
"SELECT B, 'Unable to proceed', 'Unspecified'
WHERE A = 'Retired/Deceased'
OR A = 'Liquidation'
OR A = 'Dissolved'
OR A = 'Released from company'
AND A IS NOT NULL
LABEL 'Unable to proceed''Status', 'Unspecified''Reason'", 1),
QUERY(ARRAYFORMULA(IFERROR(VLOOKUP(
QUERY(Data!$A$2:$B,
"SELECT A
WHERE A = 'Retired/Deceased'
OR A = 'Liquidation'
OR A = 'Dissolved'
OR A = 'Released from company'
AND A IS NOT NULL", 0),
{"Retired/Deceased", "Retired/Deceased", "Retired/No longer in business";
"Liquidation", "Liquidation", "Retired/No longer in business";
"Dissolved", "Dissolved", "Retired/No longer in business";
"Released from company", "", "No longer works for the company"}, {2, 3}, 0), )),
"LABEL Col1 'Comment', Col2 'Detail'", 0)}, , 0)
Upvotes: 1
Related Questions
- Google Sheet Formula with complex query and multiple criteria
- Google Sheets query function based on multiple rows and/or columns
- Google sheets query formula with multiple conditions
- Google Sheets need to return different values in a column based on multiple criteria in another
- Unable to correctly combine multiple criteria in query function in Google Sheets
- Google Sheet Query Based on Matching Columns to Other Sheet
- Query for 2 criteria to return one value
- Query based on cell reference on multiple conditions on Google Sheets
- Query to retrieve values from different columns, based on multiple criteria
- Using multiple columns of data to match and return value in Google Sheets