Return corresponding values, skipping "0" rows

Question

Hello,

I am working on a script for a liquid handler to prepare a sample plate.

I know for each sample (i.e. a well in a plate) how much volume (i.e. 100 µL) I need to add for each component (i.e. W, X, Y, Z).

Also, each component has a fixed position in the liquid handler, as follows:

Component  Position
    W        1,1
    X        1,2
    Y        1,3
    Z        1,4

I have the following table, listing for each sample how much of each component I need to add:

          Component
Sample   W   X   Y   Z
  1     100  0   0   0
  2      0   0   0  100
  3      0   0   50  0
  4      50  0   0   0
  5      0  100  0   0

I need to have the following output, where the rows with "0" value are skipped, and the "sample" column is ordered based on the component.

Basically, the formula has to go through the "W" column, check if there is a value >0, and if true return the position corresponding to the "W" component (i.e. 1,1).

Position  Sample Volume
  1,1       1     100
  1,1       4     50
  1,2       5     100
  1,3       3     50
  1,4       2     100

Can anyone help me please, finding the right formula to use? Hope it makes sense.
Thank you in advance

Answer 1

Thank you all for your answers.

Actually, I managed to do it with 2 very simple functions, FILTER and LOOKUP, as below:

Hope this helps someone else in the future!

Answer 2

Deceptively short formula in cell J2:

hiding a more complex lambda formula/function (in Name Manager).

See linked-to workbook: https://app.box.com/s/fidh3hoxzoowf2679n9b436mhqhcn9fr

To copy the function into your own workbook, copy my sheet to your workbook, then delete it again, the named lambda function will remain.

Answer 3

=HSTACK(IFERROR(INDEX(B1:B4,MATCH(FILTER(TOCOL(IF(F2:I6>0,F1:I1,""),1,TRUE),TOCOL(IF(F2:I6>0,F1:I1,""),1,TRUE)<>""),A1:A4,0)),""),FILTER(TOCOL(IF(F2:I6>0,E2:E6,""),1,TRUE),TOCOL(IF(F2:I6>0,E2:E6,""),1,TRUE)<>""),FILTER(TOCOL(F2:I6,,TRUE),TOCOL(F2:I6,,TRUE)>0))

Answer 4

=HSTACK(IFERROR(INDEX(B1:B4,MATCH(FILTER(TOCOL(IF(F2:I6>0,F1:I1,""),1,TRUE),TOCOL(IF(F2:I6>0,F1:I1,""),1,TRUE)<>""),A1:A4,0)),""),FILTER(TOCOL(IF(F2:I6>0,E2:E6,""),1,TRUE),TOCOL(IF(F2:I6>0,E2:E6,""),1,TRUE)<>""))

Thank you very much! It works :)

What about if the volumes are different, and I need to have another column with the volumes for each sample? I have updated the question

Answer 5

=HSTACK(IFERROR(INDEX(B1:B4,MATCH(FILTER(TOCOL(IF(F2:I6>0,F1:I1,""),1,TRUE),TOCOL(IF(F2:I6>0,F1:I1,""),1,TRUE)<>""),A1:A4,0)),""),FILTER(TOCOL(IF(F2:I6>0,E2:E6,""),1,TRUE),TOCOL(IF(F2:I6>0,E2:E6,""),1,TRUE)<>""))