Filling a cell with only the last available data measurement of a sanple

[hpdeboer]

Hi there,

I have data from laboratory samples in an Excel sheet.
They are all samples that have been measured in duplo or more repeats.
In the case of a duplo measurement the results will be copied in one row in another sheet.
In the case of a triplo measurement 2 results will be copied beneath each other and the third measurement will be copied in the second row next to the second measurement.

The question is however : how am I gonna fix this with formulas.

An example of how I want it to be in the sheet is shown below.
I can't submit a link to an excel sheet because of the forum rules to new members.



A B C D E

1 Sample ID Analysis Data Sample ID Repeated results Reported results
2
3 Sample 1 10 Sample 1 10 11
4 Sample 1 11
5 Sample 2 20 Sample 2 20
6 Sample 2 22 22 23
7 Sample 2 23 Sample 3 30
8 Sample 3 30 33
9 Sample 3 33 34 35
10 Sample 3 34 Sample 4 50
11 Sample 3 35 53
12 Sample 4 50 55
13 Sample 4 53 56 57
14 Sample 4 55
15 Sample 4 56
16 Sample 4 57




I hope someone can help me..
Maybe there are helpful Excel functions for this.

Greetings,

Harrie

[Glenn Kennedy]

Is this OK as an alternative?

In E2, copied across and down:
=IFERROR(IF($D2="","",INDEX($B:$B,AGGREGATE(15,6,ROW($A$2:$A$20)/($A$2:$A$20=$D2),COLUMNS($E2:E2)))),"")

In F2, copied down:

=IFERROR(LOOKUP(2,1/(E2:J2<>""),E2:J2),"")

and an alternative :

=IF(M2="","",LOOKUP(2,1/($A$2:$A$15=M2),$B$2:$B$15))

[hpdeboer]

Hi Glenn,

Thanks for the quick answer.

Bit its not covering my problem.
Maybe i wasn 't completely clear.

The reported result should only be entered in the first row if there is a duplo measurement.
If there are more repeats (triplo and more) then de reported result should be written in de column after the last repeated result.
So in the case of a triplo measurement the reported result should be written in the column after the third measurement and in the case of a quadruplo measurement the reported result should be written in the column after the fourth measurement.

I hope this will be clear and otherwise you can ask me further questions.

Greetings,

Harrie

[Glenn Kennedy]

I was hoping that my alternatives would have been acceptable to you... I will take another look...

BUT please confirm

a) that you want a blank row after sample 1... and not after the others

b) the logic behind wanting it!!

[hpdeboer]

Hi,

a) that you want a blank row after sample 1... and not after the others

No not necesarilly

b) the logic behind wanting it!!
The logic is that we report that this way when having several re-analyses.

I attached the original excel file with comments behind each row

Greetings,

Harrie

[AliGW]

In D3:

=FILTER(A3:B16,B3:B16 < MAXIFS(B3:B16,A3:A16,A3:A16))

In F3 copied down:

=IF(OR(D3="",D3=D4),"",MAXIFS($B$3:$B$16,$A$3:$A$16,D3))

[Glenn Kennedy]

I wonder if I have overcomplicated this? To some extent I am sure that I have. However, I think Ali may have been misled by the fact that the test results for each sample are in ascending order. If that is the case, then go with her offering... for sure. It is way, way simpler than this. But, if as I suspect that was just down to the provision of a badly-designed sample sheet... here is an alternative (a bit of a monster). But it is a one-cell dynamic array!!

=LET(A,A3:A20,B,B3:B20,Bo,B4:B21,rA,ROWS(A),CuA,MMULT((SEQUENCE(rA)>=SEQUENCE(,rA))*(A=TRANSPOSE(A)),ROW(A)^0),ToA,MMULT((--(A=TRANSPOSE(A))),ROW(A)^0),CHOOSE({1,2,3},SUBSTITUTE(FILTER(IF(CuA=1,A,IF(CuA<ToA,"¦","")),IF(CuA=1,A,IF(CuA<ToA,"¦",""))<>""),"¦",""),FILTER(IF(CuA<ToA,B,""),IF(CuA<ToA,B,"")<>""),SUBSTITUTE(FILTER(IF(CuA=ToA-1,Bo,IF(ToA<>CuA,"¦","")),IF(CuA=ToA-1,Bo,IF(ToA<>CuA,"`",""))<>""),"¦","")))

[AliGW]

As always, Glenn, I provide solutions for the data presented to us. I don't second guess what the OP hasn't told us - it's up to them to get the sample data right.

Although I was already thinking that my solution may not work because of what we haven't been told ...

[Glenn Kennedy]

But... on the plus side... it's far, far simpler!!

With years and years of laboratory analysis behind me, it never occurred to me that the numbers might be in ascending order. In my lab days... they were usually (very) random...

[AliGW]

I (like the majority here, I suspect) do NOT have the laboratory analysis experience that you have, although many who ask questions here seem to assume that we will have all experience necessary to see beyond their over-simplified sample data ...

I am ready for the rebuff when it comes: "This works on my sample data, but unfortinately, when I try it on my real data, it doesn't work." I'm used to it.

[hpdeboer]

Hi there,

I just oversimplified the results with the intention to make the question not more difficult than necessary.

Maybe thats more confusing for you..
But I can alway respond on the solutions I guess.

I will look at the solutions. It will take some time for me I think, because I'm not so deep in this matter as you both are.

But thanks in advance.
For your very quick answers

Greetings,

Harrie

[Glenn Kennedy]

Yes... DO look at the solutions. I gave you a couple of hours of my time today!!

[AliGW]

I'm not so deep in this matter as you both are.

Thanks for letting us know. In that case, I won't waste any more of my time on it.