Tag
Writing here in english just as a reminder of a little nice solution I found for building a dynamic list in excel skipping blank values.
Scenario: you have a column of values with blanks in between and you want to make a contiguous list (in another sheet or column) using the previous column as a source. Plus, you want to do this without using VBA or ctrl+enter, just regular excel formulas.
Say your starting column with values is G and the column where you want to put your contiguous list is K
First: you need to find the first non blank value in your column G. Use type this formula in the first cell of column K (K1)
=INDEX(G:G,MATCH(TRUE,INDEX((G:G<>0),0),0))
NOTE: this formula may vary depending on your case, the aim here is to find the first non blank value, whatever it is the formula to get it
Second: in K2 type this formula
=IFERROR(INDEX(INDIRECT(“G”&(MATCH(K1;G:G;0)+1)&”:G200″);MATCH(TRUE;INDEX((INDIRECT(“G”&(MATCH(K1;G:G;0)+1)&”:G200″);0);1);””)
G200 is just an example value, use any other value depending on the length of your source column G. The above formula is exactly the same one you have put in K1, except it dynamically changes the range of the index-match pair depending on the row number of the previous value in the column using the INDIRECT function to build the index and starting the search from the row after the one corresponding ton the non blank value found in the previous cell.
Third: drag down the formula you typed in K2 to the desired row. At some point the match function will not work any more and will raise an error. Hence the IFERROR to eliminate the #NA values that will inevitably appear.
Your formula may vary. In my case I also had to do it for multiple columns, and I needed to dynamically change not only the row but also the source colums to look into, using the super-useful formula to convert an index number into a cell letter:
=SUBSTITUTE(ADDRESS(1;D1;4);1;””)
Where in D1 I have put the source column index number to use. I also had to mix other index/match pairs in the formula, but this is another story.