-2

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.

  • 1
    You may not be able to post actual data, but please create a table that shows the problem and the expected output in the smallest most concise manner. it will help us understand the problem better. – Scott Craner Sep 12 '18 at 18:46
  • 2
    "I have patient data, and will not post or share anything due to HIPAA" this is what mock data is for, surely Bugs Bunny, Sherlock Holmes and Plato won't mind if you invent data about them – cybernetic.nomad Sep 12 '18 at 18:47
  • Please add information by editing your question. Avoid comments as they are harder to parse when they are more than 1 or two simple sentences. – cybernetic.nomad Sep 12 '18 at 19:10
  • edited original post with "mock data" – ghent96 Sep 12 '18 at 19:28
0

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.

  • thank you very much for trying to help. Various methods exist in filters, copy-paste methods, and selecting data to eliminate blank cells and rows. These are very useful, but I am most interested in a formula for this application. – ghent96 Sep 13 '18 at 16:48
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.