Condense column data with many blanks

Question

I've seen this and similar methods posted here and elsewhere:

How can I condense a list that has missing data to a new list without the cells with missing data?

I have patient data, basically like this:

D       E       F           H         I         J
label1  label2  label3      label4    label5    label6
8       15      7           8         15        7
                            4         11        3
                            5         13        1
4       11      3           2          8        0
                            3          5        2



5       13      1

2       8       0                    


3       5       2

• row 1 are data labels
• up to 5 possible number values are randomly positioned in columns D, E, F
• there are lots of blanks
• collect those up-to-5 numbers, and put them in a 3x5y grid H2:H6 so I can average them and stdev them elsewhere
• no NUM! errors can be returned

2 solutions have been found here and elsewhere but they don't work.

=IFERROR(INDEX(D:D,SMALL(IF(ISNUMBER(D:D),ROW(D:D)),ROW(D1))),"") 

This does not work in Excel 2016 xlsx format, but seems to work sometimes in a 2003 Excel .xls. That's the really wierd part. I'd like to use 2016 format, because 97-2003 gives some errors trying to save. The formula returns blanks or the first row data label, or all the same number from D2 instead of up-to-5 numbers that is has condensed.

=IF(ROWS(H$2:H2)>COUNTA(D:D),"",INDEX(D:D,SMALL(IF(D$2:D$256<>"",ROW(D$2:D$256)),ROWS(H$2:H2))))

This does not work because it returns NUM! error values for totally blank columns or columns with less than five numerical values. This workbook will have "future" days and sheets that are blank, but have the formula pasted or templated in, and the whole workbook from H2:J6 is averaged and stdev'd on sheet1. That in turn is graphed. It cannot have non-numerical error values.

Answer 1

=IFERROR(INDEX(D:D,SMALL(IF(ISNUMBER(D:D),ROW(D:D)),ROW(D1))),"")

This will actually work if pasted in correctly with the final (ROW(D1)) remaining a relative reference. The other references may be relative or absolute as desired. A lot of sources will suggest highlighting all destination cells for an array formula, then type the formula in the first cell then press cntl + shift + enter. This is wrong. This literally puts the exact same formula in each cell. That final ROW relative reference must update across each destination cell for the formula output.

Click in the first cell of your desired output area. Enter the formula. C+S+E. Then grab the bottom right corner and drag the formula across all of your desired output cells. In my case, I entered the formula into H2. C+S+E. Drug it down from H2:H6. Then drug that columnar group across horizontally from H2:H6 to J2:J6. Works perfectly now.

Answer 2

I have less than 50 reputation so I cannot comment.

From my understanding you only need to remove the blank rows, right? Seems a simple filter can do the job. AFAIK that's what I get from the sample.

However there could be blank cells instead of blank rows. In that case, a more time-consuming-but-easier-to-implement method is to copy paste data from column D to somewhere else, remove the blanks and remove duplicates. Do the same for the other two columns and you are done. This is easy as you only have a total of 3 columns to work on.