CODEWITHSUNDEEP
excelexcel-formulaexcel-2013
Andrew Perry
Andrew Perry

Reputation: 765

Can I dynamically sort a list from this formula without helper columns?

I currently have a column of data (the named range VoyCode_NoBlanks) that's consolidated from another column (the range VoyCode_Blanks) using this formula:

{=IF(ROW()-ROW(VoyCode_NoBlanks)+1>ROWS(VoyCode_Blanks)-COUNTBLANK(VoyCode_Blanks),"",INDIRECT(ADDRESS(SMALL((IF(VoyCode_Blanks<>"",ROW(VoyCode_Blanks),ROW()+ROWS(VoyCode_Blanks))),ROW()-ROW(VoyCode_NoBlanks)+1),COLUMN(VoyCode_Blanks),4)))}

(Thanks to @ForwardEd for the formula, at Dynamic ranges again - once more, with text strings)

Is there any way I can modify it to list those values in ascending order instead of just the order that they appear?

I'd like to avoid extra columns or rows, VBA

Upvotes: 0

Views: 1357

Answers (1)

Forward Ed
Forward Ed

Reputation: 9874

=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))

That is the array version of the previous formula. Remember to enter it with CONTROL+SHIFT+ENTER. you will know you have done it right when you see { } around the formula in the formula bar. You cannot enter these manually. This may be a single cell array formula. Meaning you make it in B2. Then you copy it to or drag from B3 down tot he end of your list.

Not sure if it requires the cell above or not to be blank. I pulled it from this website.

Doing a bit of reading and there is potential that both the previous regular formula and this array formula will work as long as the value in B1 is not appart of the list.

Upvotes: 1

Related Questions