Forum Discussion
Converting outputs from 96 well plate reader from list to matrix
Hello,
I am using a 96 well plate reader which outputs data as a list of samples that correlate to a row value and a column value. A simplified version of what it puts out looks like this:
Where a combination of the letter and number corresponds to the position on a 96 well plate. Unlike in Excel, the letters A-H label the rows, and the numbers (1-12) label the columns. From this data I want to be able to reconstruct a map of the original plate using a formula, even if the data has been sorted. I have been able to do this by adding a column which concatenates the letter and number then using index and match as below.
The formula is =INDEX($A$2:$D$27,MATCH(CONCAT($F2,G$1),$D$2:$D$27,0),1).
While this works, I would prefer a solution that does not have to make the intermediate concatenated column, in this case column D. Does anyone know how to do this?
Thanks!
5 Replies
... and if you are fortunate enough to be using 365 or Excel 2021
= LET( k, TRANSPOSE(SEQUENCE(3,8)), INDEX(Sample,k))will do the job.
- What is the formula for an opposite task?
Things have moved on since this post. The basis for the solution above would now be
= WRAPCOLS(Sample, 8, "")and the reverse is
= TOCOL(matrix,,1)
=VLOOKUP($F2&G$1,CHOOSE({1,2},$B$2:$B$27&$C$2:$C$27,$A$2:$A$27),2,0)Maybe with this formula. Enter formula with ctrl+shift+enter if you don't work with Office365 or 2021.
In G2:
=INDEX($A$2:$A$97, MATCH(1, ($B$2:$B$97=$F2)*($C$2:$C$97=G$1), 0))
If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.
Then fill down to row 9 and to the right to column R.
