Forum Discussion
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 an array to show using # or something similar.
i have and i want
1 1
1
2 2
2
so not to have the same number appear below. only to have the number appear below with the spaces in between. i hope this makes sense. see sheet attached. thank you!
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,"")
8 Replies
- thank you so much for the responses! is there a way to do this using a2# vs a2:a15
- If A2:A15 is actually a dynamic array, replace A2:A15 with A2# in the various proposal you got, that should work...
I like going with REDUCE for this task:
=REDUCE("After",UNIQUE(data),SStack)Though, I prefer to use EXPAND within 'SStack (Selective Stack):
=LAMBDA(a,v,LET(k, COUNT(FILTER(data, data = v)), VSTACK(a, EXPAND(v, k, 1, ""))))This should work fine:
=LET( data,$A$2:$A$15, posUnique,XMATCH(UNIQUE(data),data), MAP(SEQUENCE(ROWS(data)),LAMBDA(n,IF(ISNUMBER(XMATCH(n,posUnique)),INDEX(data,n),""))))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,"")- i used the drop frequency but i love learning all of the new formula functions provided. thank you everyone!
- Glad we could help & Thanks for providing feedback
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)
