0

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

Community
  • 1
  • 1
Andrew Perry
  • 743
  • 2
  • 11
  • 32
  • Andrew, did you look at the first formula in my answer? it generates a sorted list with empty cells removed. Essetially using that formula in column C would remove the need for what you are doing in column B (according to your image from the link Q) – Forward Ed Jun 09 '16 at 07:55
  • Yeah, I had a look at it - is there any way to tweak it so it doesn't need the empty cell above it? I've got to work with fixed row numbers, I'm afraid. – Andrew Perry Jun 09 '16 at 08:00
  • Ahh, not that I am aware of...are you opposed to array formulas? – Forward Ed Jun 09 '16 at 08:01
  • Nope, love 'em. :-) – Andrew Perry Jun 09 '16 at 08:01
  • I don't fully understand them, but they tend to work well, so I like them a lot. :-D – Andrew Perry Jun 09 '16 at 08:02
  • just looking at an example that used that formula and they did not have a blank cell above it. Did the formula fail on you when you tried it without the blank cell. I am wondering if the old grey matter in my noggin is leading me astray with false recollections again. – Forward Ed Jun 09 '16 at 08:04
  • 1
    Would love to sit around and help some more, but while the dog is not begging to be taken for a walk this time, my bed is begging me to go and assume the horizontal position on it! – Forward Ed Jun 09 '16 at 09:03
  • No worries, mate, I know the siren call of the pillow well. Thanks! – Andrew Perry Jun 09 '16 at 09:04

1 Answers1

1
=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.

Forward Ed
  • 9,484
  • 3
  • 22
  • 52
  • Not sure I'm implementing it properly - going with `=INDEX($U$2:$U$20, MATCH(0, COUNTIF($V$2:V2, $U$2:$U$20), 0))` as an array formula - but it's just repeating entries until it gets to a new one. I'll check the link and see if that sheds any light on it. – Andrew Perry Jun 09 '16 at 09:07
  • 1
    See the edit I made in the paragraph after the formula. and I am going to bed this time I mean it! – Forward Ed Jun 09 '16 at 09:11