UNIQUE FILTER with ISNA LET: 1 Cell Method Sort? instead Multi Helper Columns>Merge?

[Stephan]

Hello

To Sort a list of NAMES with exam PASS or FAIL, however some FAIL 1st time but PASS next attempt, however using UNIQUE, some NAMEs appear TWICE.

So far FORMULA with numerous helper columns, is there better way then this on XL2021? ie VStack or HStack is 365 only.

To demonstrate error, kylies appears twice, UNIQUE will display name twice, as 2nd column there is 2 entries for same name, hence 2 unique entries:

=SORT(UNIQUE(FILTER(I2#,(J2:J9="PASS")+(J2:J9="FAIL"))))

Start simple, but quickly desends to more columns

PASS LIST: =SORT(UNIQUE(FILTER(I2#,(J2:J9="PASS"))))

FAIL LIST: =SORT(UNIQUE(FILTER(I2#,(J2:J9="FAIL"))))

PASS LIST comparing FAIL LIST to exclude DUPLICATE:
=UNIQUE(FILTER(A17:A21,ISNA(MATCH(A17:A21,C17:C21,0)*0)))

XLOOKUP for EXAM STATUS of EXCLUDE DUPLICATE LIST:
=XLOOKUP(K15,H$2:H$9,I$2:I$9)

COLUMN MERGE of PASS exc DUPLICATE ENTRY + FAIL:

=SORT(UNIQUE(LET(
a,F16:G19,
b,C16#,
ra,ROWS(a),
rb,ROWS(b),
rsq,SEQUENCE(ra+rb),
csq,SEQUENCE(,COLUMNS(a+b)),
out,IFS(rsq<=ra,INDEX(a,rsq,csq),rsq<=ra+rb,INDEX(b,rsq-ra,csq),TRUE,),out)))

 

[AliGW]

I'm not quite sure what the question is here, but if you are wanting only the latest result for each individual, you can use this:

=LET(f,FILTER(A2:D9,C2:C9=MAXIFS(C2:C9,A2:A9,A2:A9,B2:B9,B2:B9)),
SORT(HSTACK(INDEX(f,,1)&" "&INDEX(f,,2),INDEX(f,,4))))

 

[Stephan]

I'm not quite sure what the question is here, but if you are wanting only the latest result for each individual, you can use this:

=LET(f,FILTER(A2:D9,C2:C9=MAXIFS(C2:C9,A2:A9,A2:A9,B2:B9,B2:B9)),
SORT(HSTACK(INDEX(f,,1)&" "&INDEX(f,,2),INDEX(f,,4))))

Hi. Yes correct the Latest Result. It is a just an example list of Names with Exam pass/fail, a person Re-took & passed 1st time.

Wanted a 1 cell unique filter of it, with criteria to consider if same name appears, only to consider the pass if column B has pass & fail.

If other words, yes looking for 1 cell instead of clustering more of same data in other columns.

Hence not seen a formula like this for XL 2021, I guess with my basic knowledge the answer is there, just not considered it. As trying to find way with UNIQUE FILTER, 1 CELL, & V-H Stack exclusive to 365, isn't it? My newest version is XL 2021, prefer non Web based, hence Formula unrecognised on XL21. Cheers for re-ply though, future use of 365, but perhaps the answer is something like this?:

=UNIQUE(FILTER(A:A, (B:B = "YourCondition") * (COUNTIFS(A:A, A:A, B:B, "YourCondition") > 1)))

Unsure is >1 is actual value of COLUMN, or TIMES of OCCURENCE?

X POSTS:

UNIQUE LET SEQUENCE SPILL in XL2021 > 1 Cell? Advanced Logic Criteria, ISNA or IFNA?

Hello. Basic NAMES LIST with exam PASS or FAIL, however some FAIL 1st time but PASS next attempt. Current method is 4 SPILL helpers, just to get the latest unique list in EXCEL 2021, not 365. LET to MERGE/COMBINE 2 COLUMNS into 1 COLUMN. However using UNIQUE, some NAMEs appear TWICE, as...

www.excelforum.com

 

[AliGW]

If you need a solution for Excel 2021, then you'll need to find a way around HSTACK, which won't be available to you.

Maybe this:

=LET(f,FILTER(A2:D9,C2:C9=MAXIFS(C2:C9,A2:A9,A2:A9,B2:B9,B2:B9)),
SORT(CHOOSE({1,2},INDEX(f,,1)&" "&INDEX(f,,2),INDEX(f,,4))))

Also posted on ExcelForum.

 

[Stephan]

If you need a solution for Excel 2021, then you'll need to find a way around HSTACK, which won't be available to you.

Maybe this:

=LET(f,FILTER(A2:D9,C2:C9=MAXIFS(C2:C9,A2:A9,A2:A9,B2:B9,B2:B9)),
SORT(CHOOSE({1,2},INDEX(f,,1)&" "&INDEX(f,,2),INDEX(f,,4))))

Also posted on ExcelForum.

THANKS exactly that, works perfectly in XL2021.
Added to attached file in CELL L3.

Questions:
A. Does MAXIF return which row newest date COLUMN C?
B. What would Formula B is no Column C?

CHEERS

 

[AliGW]

Why and under what circumstances would the attempts to pass the exam not be dated or at least numbered? I don't think this is a realistic scenario.

Does MAXIF return which row newest date COLUMN C?

Yes - for each person. You should know how to look at parts of a complex formula and run them to see what they do. Here, run f.

For example:

=LET(f,FILTER(A2:D9,C2:C9=MAXIFS(C2:C9,A2:A9,A2:A9,B2:B9,B2:B9)),
s,SORT(CHOOSE({1,2},INDEX(f,,1)&" "&INDEX(f,,2),INDEX(f,,4))),
f)

 

[Stephan]

UNIQUE Formulas, you're right they can be complex expressions, and many times the answer is much simpler then you realise, the obviousness of the truth is you actually saw it in use! But after looking @ lots of lots of online & download examples, you realise there are a trail to confusion, they lack piecing together all the likely uses.

Hence on that Prospect, majority of feasible possibilities, attached is a file of useful UNIQUE FORMULA in EXCEL 2021for:

DATE -/+ MIN MAX
£ NUMBER -/+MIN MAX
PART WORD
EXCLUDE WORD
DATA VALIDATION COMBO A>B

Large COMBOs of Y (or No!) in Column P, display results in SPILL I2#

Not included the other useful formula of REGEX, as doesn't work in XL21, useful is looking for certain CODE!

Also any suggestions of other likely sesarch parameters or even vague possiblie scenarios is of use, thanks.



I work from Templates like this, as they work, and don't remember from fresh, blank, as there many expressions & exact layouts.

Also appreciate many descriptions of UNIQUE FILTER are like: =UNIQUE(FILTER(DATA, MAKE="AUDI")*(MODEL="QUATTRO"),)))

What I initially under appreciated is that UNIQUE FILTER its use of...AND * OR + fields can be other then just = EQUALS....

Hence lots of examples including COMPARE OPS (COMPARISON OERATORS):

Comparison operator	Meaning	Example
= (Equal)	Equal to	A1=B1
> (Greater than)	Greater than	A1>B1
< (Less than)	Less than	A1<B1
>= (Greater or Equal)	Greater than or equal to	A1>=B1
<= (Less than or Equal)	Less than or equal to	A1<=B1
<> (not Equal)	Not equal to	A1<>B1
<>""	IF NOT > BLANK	A1<>""
=""	IF BLANK	A1=""
=OR	OR	=OR (B1>100, B2>100, B3>100)