Extract Unique values, multiple length criteria

[bsquad]

I used to work a ton with these types of unique extraction array formulas, but am having some trouble remembering if I am even going about this the right way. The formula below is attempting to extract/index a unique value if its length is equal to 18 or 26 or 28.

This workbook is large, it has lots of formulas and vba code, I would like to not use Application.Calculation = xlCalculationManual in my code, and I am avoiding all Volatile formula functions.

the Array formula

=IF(ROWS($N$17:N17)>SUMPRODUCT(--(LEN($B$17:$B$26)=18),--(LEN($B$17:$B$26)=26),--(LEN($B$17:$B$26)=28)),"",INDEX($B$17:$B$26,SMALL(IF((LEN($B$17:$B$26)=18)*(LEN($B$17:$B$26)=26)*(LEN($B$17:$B$26)=28),ROW($B$17:$B$26)-ROW($B$17)+1),ROWS($N$17:N17))))

 

[Aladin Akyurek]

In N16 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($B$17:$B$26=""),IF(ISNUMBER(MATCH(LEN($B$17:$B$26),{18,26,28},0)),MATCH($B$17:$B$26,$B$17:$B$26,0))),ROW($B$17:$B$26)-ROW($B$17)+1),1))

In N17 control+shift+enter, not just enter, and copy down:

=IF(ROWS($N$17:N17)>$N$16,"",INDEX($B$17:$B$26,SMALL(IF(FREQUENCY(IF(1-($B$17:$B$26=""),IF(ISNUMBER(MATCH(LEN($B$17:$B$26),{18,26,28},0)),MATCH($B$17:$B$26,$B$17:$B$26,0))),ROW($B$17:$B$26)-ROW($B$17)+1),ROW($B$17:$B$26)-ROW($B$17)+1),ROWS($N$17:N17))))

 

[bsquad]

Aladin, thanks so much for the reply; its appreciated. The formula you provided works great!

I could essentially replace the $N$16 with something like a =IF(ROWS($N$17:N17)>SUMPRODUCT(--($B$17:$B$26<>"")) ?

I see what you are doing with the first formula and would agree it would be the most precise; but it wouldn't necessarily be essential in order to run the 2nd formula if I used something like a SUMPRODUCT correct?

 

[Aladin Akyurek]

Aladin, thanks so much for the reply; its appreciated. The formula you provided works great!

You are welcome.

I could essentially replace the $N$16 with something like a =IF(ROWS($N$17:N17)>SUMPRODUCT(--($B$17:$B$26<>"")) ?

I see what you are doing with the first formula and would agree it would be the most precise; but it wouldn't necessarily be essential in order to run the 2nd formula if I used something like a SUMPRODUCT correct?

No. FREQUENCY version is faster.

If you insist on having a single formula...

Select B17:B26 and name the selection Data in the Name Manager.
Define also Ivec in the Name Manager as referring to:

=ROW(Data)-ROW(INDEX(Data,1,1))+1

Now we can the following single formula. Control+shift+enter and copy down:

=IF(ROWS($N$17:N17)>SUM(IF(FREQUENCY(IF(1-(Data=""),IF(ISNUMBER(MATCH(LEN(Data),{18,26,28},0)),MATCH(Data,Data,0))),Ivec),1)),"",INDEX(Data,SMALL(IF(FREQUENCY(IF(1-(Data=""),IF(ISNUMBER(MATCH(LEN(Data),{18,26,28},0)),MATCH(Data,Data,0))),Ivec),Ivec),ROWS($N$17:N17))))