I needed a single formula (not a Button) to transpose the Rows and Columns of a table.
Max
3 Likes
How It Works
each line explained…
- the List() produces a list of lists or 2d matrix of the table contents
- we call that matrix V so we can refer to it below
- for J=1 to NrCols - Count(V) is the number of columns
- for I=1 to NrRows - Count(V.First()) is the number of rows
- build a new matrix that is transposed - V[I,J] is in row then column order
- next I
- next J
- traverse the transposed matrix rows the columns
- pad-out the cell and make it fixed-size-font, end with a newline
The resulting display is the contents of the Original table with the rows and columns transposed
Max
6 Likes
This is awesome!
Thank you so much for all of your contributions to the community. Your posts are always so insightful and are true gems for Coda enthusiast.
2 Likes
ok… i looked at my code and realized it was twice as long and complicated as needed.
so i have reduced it to a more manageable minimal set of code.
here is the result (as before, its a ‘virtual’ transposed table - display-only)
and here is the shorter formula…
How It Works line-by-line
- the List() and WithName() collects the entire table into the 2d matrix M (a list of one-list-per-column)
- for C=1 to NrColumns (Count(M) is the number of cols)
- for R=1 to NrRows (Count(M.First()) is the number of rows)
- show each cell M[C,R] padded and in fixed-width-font
- next R and separate the cells with a space
- next C and separate rows with a line-break
- end the withname for M
This runs faster, has less moving parts, and is easier to follow.
Someone asked why the _Code()? it shows the cells in a fixed-width font. otherwise the width of each cell would be slightly different and they would not align properly.
Max
4 Likes
@Xyzor_Max, one would think I no longer would be surprised by all the gems you publish, and yet, one’d be wrong.
This is incredible, thank you SO much for so generously sharing your findings with the community.
In order to make your wondeful contribution accessible to as many interested readers as possible, I have taken the step-by-step overview you included and broke it down into displaying the formula results for each step.
It helped me much faster understand what your code does, and I’m thinking it might serve the same purpose for others.
I’m sharing the doc in an fully editable view, so that everyone can peek inside the formula editor - please do make sure not to delete anything, as this would sour the experience for others.
Max, what a community champion you are, indeed. Thanks so much for all you do for our Coda community!
All the best from Austria,
Nina
3 Likes
thanks @Nina_Kastenauer1,
you did a great job breaking it down, explaining it well, and displaying all the intermediate results.
much appreciated
max
1 Like
Recently, I was searching for a way to dynamically represent data in a virtual table (that is, moving from a “one-to-many” relationship to a “one-to-one” relationship without the user having to press a button to achieve this.
@Xyzor_Max graciously took the time to share his wisdom with me on how to best achieve this. The second thing he said once I finally cracked it (after “Congrats!”) was “Now you must share your solution with the community”. And, as is usually the case, he was right of course. So, here goes:
Imagine we want to schedule a team retreat. While the scheduling is under way, we want to dynamically keep track of the number of times each team member interacts with every team member.
That is, we want to dynamically visualize Interactions Scheduled, ie how often does each team member meet with every other team member?
We will create the virtual table in the following two ways:
And here are the formulas used (I decided to include a static header, as well as a static side-bar, to ensure that the 2D-Matrix could easily be read by the user)
And to display as colored circles, simply add a “Switch-If” to the formula to display a green circle to represent the value 1, a yellow circle to represent the value 2, etc.
Enjoy,
Nina
3 Likes