extract values where all adjacent cells match criteria

[david763]

hello, I am trying to work through a formula (no vba) that returns a list of values from another table where adjacent cells ALL equate to "inactive".
the column being interrogated may contain duplicate values which have either "active" or "inactive" in an adjacent cell.
for example:
123 has one occurrence of 'active' - I don't want this
456 are all 'active' - I don't want this either
789 - there are 3 occurrences ALL 'inactive' - I want this
100 - there are 2 occurrences both 'inactive' - I want this also
200 - single entry which is 'inactive' - I want this as well

appreciate any help with this

regards

David

 

[arthurbr]

Try

= UNIQUE(FILTER(A4:A15,B4:B15="inactive"))

 

[awoohaw]

This may work for you:

Book1
	A	B	C	D
1	index	status
2
3	123	inactive		789
4	123	active		200
5	456	active		100
6	456	active
7	789	inactive
8	123	inactive
9	200	inactive
10	456	active
11	789	inactive
12	789	inactive
13	100	inactive
14	100	inactive
Sheet1

Cell Formulas
Range	Formula
D3:D5	D3	=LET(ab,$A$3:$B$14,a,$A$3:$A$14,as,COUNTIF($A$3:$A$14,A3:A14),b,$B$3:$B$14,abs,COUNTIFS(a,a,b,"inactive"),UNIQUE(TAKE(FILTER(ab,abs=as,""),,1)))
Dynamic array formulas.

 

[Fluff]

Another option

=UNIQUE(FILTER(A3:A14,ISNA(XMATCH(A3:A14,SORT(FILTER(A3:A14,B3:B14="active")),,2))))

 

[david763]

Hi @arthurbr , @awoohaw and @Fluff
Many thanks for your quick replies - they have got me within reach of the problem. However, I just realised that there is another step to my problem. (sorry!)

There is a supplementary column to be evaluated to give me the resultant list, as follows:

Similar problem than previous but, one more layer...

Inputs:

• Index and Reference values may exist more than once
• Status will always remain the same for each occurrence of the same value (ie if one occurrence of I-2519 is Inactive, then all I-2519 are Inactive; same with Reference values/status)

Required Output:
I need a unique list of Reference numbers (R-*) where:

• Reference Status is 'Active' (ie not even consider R-25241)
• All occurrences of corresponding Index records have status of Inactive. If any matches are Active, then I don't want them.

(PS @awoohaw I have never seen the LET function before - it looks really interesting - I intend to check this out further...)

 

[Sanjeev1976]

You can try the below :

Book2
	A	B	C	D	E	F
1	Reference	Reference Status	Index	Index status		Reference
2	R-06781	Active	I-2519	Inactive		R-15863
3	R-06781	Active	I-2520	Inactive		R-15868
4	R-06781	Active	I-4669	Active		R-25193
5	R-06784	Active	I-2520	Inactive
6	R-06784	Active	I-3038	Inactive
7	R-06784	Active	I-5136	Active
8	R-15863	Active	I-3038	Inactive
9	R-15864	Active	I-2521	Active
10	R-15864	Active	I-3032	Active
11	R-15864	Active	I-3038	Inactive
12	R-15868	Active	I-3042	Inactive
13	R-15868	Active	I-5142	Inactive
14	R-15880	Active	I-2521	Active
15	R-25193	Active	I-5785	Inactive
16	R-25241	Inactive	I-5785	Inactive
17	R-25242	Active	I-5143	Active
18	R-25242	Active	I-5144	Active
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=UNIQUE(FILTER(A2:A18,ISNA(XMATCH(A2:A18,SORT(FILTER(A2:A18,(D2:D18="active")+(B2:B18="inactive"))),,2))))
Dynamic array formulas.

 

[david763]

You can try the below :

Book2
	A	B	C	D	E	F
1	Reference	Reference Status	Index	Index status		Reference
2	R-06781	Active	I-2519	Inactive		R-15863
3	R-06781	Active	I-2520	Inactive		R-15868
4	R-06781	Active	I-4669	Active		R-25193
5	R-06784	Active	I-2520	Inactive
6	R-06784	Active	I-3038	Inactive
7	R-06784	Active	I-5136	Active
8	R-15863	Active	I-3038	Inactive
9	R-15864	Active	I-2521	Active
10	R-15864	Active	I-3032	Active
11	R-15864	Active	I-3038	Inactive
12	R-15868	Active	I-3042	Inactive
13	R-15868	Active	I-5142	Inactive
14	R-15880	Active	I-2521	Active
15	R-25193	Active	I-5785	Inactive
16	R-25241	Inactive	I-5785	Inactive
17	R-25242	Active	I-5143	Active
18	R-25242	Active	I-5144	Active
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=UNIQUE(FILTER(A2:A18,ISNA(XMATCH(A2:A18,SORT(FILTER(A2:A18,(D2:D18="active")+(B2:B18="inactive"))),,2))))
Dynamic array formulas.

Thank you @Sanjeev1976 it worked...!!

PS, I've tried to unpack the formula parts to work out what it going on, but getting lost - could you please briefly describe the process the formula is following..?

 

[Carl Stephens]

Thank you @Sanjeev1976 it worked...!!

PS, I've tried to unpack the formula parts to work out what it going on, but getting lost - could you please briefly describe the process the formula is following..?

This formula in Excel accomplishes a specific task: finding unique values in a range (A2:A18) that don't belong to specific categories defined in other columns. Here's a breakdown of how it works step by step:

1. Innermost FILTER:
   • This part focuses on the range A2:A18 and filters it based on two conditions combined using the + operator (logical OR):
     • (D2:D18="active"): This checks if the corresponding cells in D2:D18 (presumably a category column) contain the text "active".
     • (B2:B18="inactive"): This checks if the corresponding cells in B2:B18 (another possible category column) contain the text "inactive".
   • The result of this filtering will be a temporary list containing only rows from A2:A18 where either the corresponding cell in D2:D18 is "active" or the corresponding cell in B2:B18 is "inactive". In essence, it creates a list of items that belong to the specified categories.
2. SORT:
   • This function takes the output from the inner FILTER (the list of items in categories) and sorts it in ascending order by default (since no order argument is specified, ,). This sorting might not be crucial for the final result, but it could be relevant depending on your data and how you want to handle potential duplicates within categories.
3. XMATCH:
   • This function acts as the core of the formula. It searches for each value in the original range A2:A18 (considered the main list) within the sorted list created by the inner FILTER and SORT combination.
   • The 2 argument passed to XMATCH specifies an exact match search.
   • If a match is found (meaning the value exists in the sorted list of categorized items), XMATCH returns the position of that match within the sorted list.
   • If no match is found (meaning the value doesn't exist in the sorted list and therefore doesn't belong to any category), XMATCH returns the error value #N/A (Not Available).
4. Outer FILTER:
   • This part filters the original range A2:A18 again, but this time based on the output from the XMATCH function. It acts as a sieve:
     • It keeps only the rows in A2:A18 where the corresponding XMATCH result is #N/A. This means it retains only the values from the main list (A2:A18) that weren't found in the sorted and filtered list based on the "active" or "inactive" criteria in D2:D18 and B2:B18. In other words, it keeps the items that don't belong to any category.
5. UNIQUE:
   • This function takes the output from the outer FILTER (the list of items that don't belong to categories) and removes any duplicate values. This ensures that you get a final list with only distinct values that are not present in the categorized items.

In essence, the formula identifies and returns unique values from A2:A18 that are not found in the list created by filtering A2:A18 based on the conditions in D2:D18 and B2:B18 (potentially categories).

Example:

Let's say A2:A18 contains a list of fruits (apple, banana, orange, grape, cherry, kiwi, mango, apple), D2:D18 indicates "citrus" (orange, lemon), and B2:B18 indicates "red" (apple, cherry, strawberry).

The formula would return: {"banana", "grape", "kiwi", "mango"} because these fruits are not categorized as "citrus" or "red" in the respective columns.

 

[Fluff]

This sorting might not be crucial for the final result, but it could be relevant depending on your data and how you want to handle potential duplicates within categories.

The sort is crucial as the Xmatch function is doing a binary match, specified by the 2 as the 4th argument