1

I am restructuring a few datasets and need to retrieve a list of non-contiguous values in a row in Excel for Mac. There are cells with blank spaces that should be discarded, so the result is a horizontal list of the same values, without the blank cells in between them.

The values appear in blocks of 6 contiguous cells interspersed by varying amounts (multiples of 6!) of blank cells. Data is numeric, so >0 does the trick. I have tried adapting solutions found online, such as this one here, with no luck.

I had to do something similar by retrieving 1st to 10th occurrence of values separately, and was able to accomplish that after dumping hloookup, learning index+match and array formulas, and adjusting solutions found online for a similar problem with columns, like this.

This is how my solution to the retrieving the 2nd occurence in a row looks like:

=IFERROR(INDEX($FR5:$GT5,SMALL(IF($FR5:$GT5>0,COLUMN($FR5:$GT5)-COLUMN($FR5)+1,FALSE),2)),"9999")

where $FR5:$GT5 is the range from which I need to retrieve values, and 9999 is my code for missing values. Just thought I'd throw it there, someone with limited skills as myself might find it useful.

Any tips to help me move along? Preferably, I'd like to adapt my previous formula to resolve this issue. I tried but was unable to get rid of the blank cells. I am stuck! Thanks in advance.

Community
  • 1
  • 1
user5225119
  • 35
  • 5
  • 1
    Thanks, that would actually work - except that I have to do this for several datasets, a few times in each of them, for varying ranges and number of values to be retrieved. So I need to rely on formulas I can double check etc. I even have sum/countifs in my ranges of origin and destiny as safety check that all values were retrieved correctly! In any case, thanks for adding to my bag of Excel tricks! – user5225119 Aug 16 '15 at 13:57

1 Answers1

1

It isn't entirely clear on whether you want to show the collated (sans blanks) values in a row or a column but I suspect from the combination of relative row/absolute column cell references that you want to start in a cell and fill right.

Your formula is very close to collecting the sequence of numbers while ignoring blanks and numbers less than or equal to zero. You just need to adjust the k paramter of the SMALL function (the 2 in your sample formula above).

If you put =COLUMN(A:A) in a cell and fill right you will receive a sequence of integers like 1, 2, 3, 4, etc. Similarly, if you put =ROW(1:1) in a cell and fill down you will again receive 1, 2, 3, 4, etc. One of these sub-formulas can be used to increase the k of your SMALL depending upon whether you want to fill your formula right to receive the returned values in a single row or fill down to receive the returned values in a single column. In this manner, filling right or down will get you the second, third, etc returns.

To receive your returned values in a row use this array formula,

=IFERROR(INDEX(5:5, SMALL(IF($FR5:$GT5>0, COLUMN($FR:$GT)), COLUMN(A:A))),"9999")

Fill right as necessary to catch all values. To receive your returned values in a column use this array formula,

=IFERROR(INDEX($5:$5, SMALL(IF(FR$5:GT$5>0, COLUMN(FR:GT)), ROW(1:1))),"9999")

Fill down as necessary to catch all values.

Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula.

I've never been a fan of the maths acrobatics (e.g. COLUMN($FR5:$GT5)-COLUMN($FR5)+1) that your original formula uses to determine the column (or row) in that style of formula. I've pared it down substantially in the above equivalent formulas but they amount to the same thing.

  • Thank you! That works brilliantly. I do appreciate you taking the time to explain why it works as well :-) I suspected the solution was pretty much in my face...haha. It helps to have a more elegant approach to my nth solution as well. – user5225119 Aug 16 '15 at 13:47