Forum Discussion

rangelsammon's avatar
rangelsammon
Brass Contributor
Dec 18, 2022
Solved

Unique range with space in between

https://1drv.ms/x/s!AnFi6uGE1rekjWMiExJCd5UPyZEq?e=QkobYl   at the moment using a IF formula to display a list of unique values from another list but wondering if a cleaner way of doing with using ...
excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Dec 18, 2022

    rangelsammon 

     

    Alternatively, assuming the same serie doesn't repeat in A2:A15, in B2:

     

     

    =IF(DROP(FREQUENCY(A2:A15,A2:A15),-1),A2:A15,"")

    or

    =IF(XMATCH(A2:A15,A2:A15)=SEQUENCE(ROWS(A2:A15)),A2:A15,"")

     

     

     

Harun24HR's avatar
Harun24HR
Bronze Contributor
Dec 18, 2022

rangelsammon Try-

 

=IF(A2:A15<>A1:A14,A2:A15,"")

 

Better you can use REDUCE() function like-

 

=DROP(REDUCE("",A2:A15,LAMBDA(a,x,VSTACK(a,IF(ISERROR(XMATCH(x,a,0)),x,"")))),1)

 

To make input column more dynamic use A2:INDEX(A:A,COUNTA(A:A)) Try-

 

=DROP(REDUCE("",A2:INDEX(A:A,COUNTA(A:A)),LAMBDA(a,x,VSTACK(a,IF(ISERROR(XMATCH(x,a,0)),x,"")))),1)

 

 

Resources