1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Excel - condense column data with many blanks

Discussion in 'Main Forum' started by ghent96, Sep 12, 2018.

  1. ghent96

    ghent96 Guest

    I was trying to comment on this question and answer, but don't have enough reputation yet, sorry: basically it doesn't work; but 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, and will not post or share anything due to HIPAA. Basically here's the example:

    • 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.

    thank you in advance! :)

    EDIT: wow, I thought this would be a useful website and not a more intellectually elitist version of Reddit with downvotes by cranky reputation whores. Here's some of this almighty-important mock data for anyone useful and good-hearted out there: (wtf is there no upload/attach option?)

    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

    Login To add answer/comment
     

Share This Page