Formula to Extract Unqiue Numbers

[legalhustler]

I have the following array formula that requires CSE, but would like to change it so I don't use CSE. I think AGGREGATE function would be a substitute?? Can someone help.


{=IF(ROWS($C$4:C4)<=$D$4,INDEX($C$4:$C$51,SMALL(IF(FREQUENCY(IF($C$4:$C$51<>"",MATCH($C$4:$C$51,$C$4:$C$51,0)),ROW($C$4:$C$51)-ROW($C$4)+1),ROW($C$4:$C$51)-ROW($C$4)+1),ROWS($C$4:C4))),"")}

 

[Aladin Akyurek]

It looks like the need of applying control+shift+enter (CSE) bothers you. The following set up does not require CSE.

Row\Col	C​	D​	E​
3​	Num	0​	unique
4​	2​	1​	2​
5​	2​		5​
6​	5​	2​	7​
7​	5​		3​
8​	7​	3​	6​
9​	7​		8​
10​	3​	4​
11​	6​	5​
12​	2​
13​	7​
14​	8​	6​

D3 must house a 0.

In D4 enter and copy down:

=IF(ISNUMBER(MATCH($C4,$C$3:C3,0)),"",LOOKUP(9.99999999999999E+307,$D$3:D3)+1)

In E4 enter and copy down:

=IF(ROWS($E$4:E4)<=LOOKUP(9.99999999999999E+307,$D$4:$D$14),LOOKUP(ROWS($E$4:E4),$D$4:$D$14,$C$4:$C$14),"")

 

[legalhustler]

It looks like the need of applying control+shift+enter (CSE) bothers you. The following set up does not require CSE.

Row\Col	C​	D​	E​
3​	Num	0​	unique
4​	2​	1​	2​
5​	2​		5​
6​	5​	2​	7​
7​	5​		3​
8​	7​	3​	6​
9​	7​		8​
10​	3​	4​
11​	6​	5​
12​	2​
13​	7​
14​	8​	6​

D3 must house a 0.

In D4 enter and copy down:

=IF(ISNUMBER(MATCH($C4,$C$3:C3,0)),"",LOOKUP(9.99999999999999E+307,$D$3:D3)+1)

In E4 enter and copy down:

=IF(ROWS($E$4:E4)<=LOOKUP(9.99999999999999E+307,$D$4:$D$14),LOOKUP(ROWS($E$4:E4),$D$4:$D$14,$C$4:$C$14),"")

Anyways to avoid helper columns too? Also formula needs to exclude empty cells, like shown in my original formula.

 

[Aladin Akyurek]

Anyways to avoid helper columns too?

That's the gist of the set up. It trades off cell memory against more speed (and no CSE).

Also formula needs to exclude empty cells, like shown in my original formula.

Modify the formula in D4 to...

=IF($C4="","",IF(ISNUMBER(MATCH($C4,$C$3:C3,0)),"",LOOKUP(9.99999999999999E+307,$D$3:D3)+1))

and copy down.