If, like us, you do a lot of work with data or reports in Excel, you have almost certainly come across the irritating challenges presented by having values in rows (all in one column) and needing it to be transposed to columns (all in one row) - or vice-versa.
|
If you are using TABLE based GRTMPS input data, this comes up quite a lot. Unit modes are defined in rows (TABLE 2xx.0), but used as columns (TABLEs 2xx.1, 2xx.2). Groups are columns when their members are listed (1xx.3, 141.3 etc.) but rows when constrained (1xx.1, 141.1). |
 |
If you are using Database input, the SSI workbooks mostly contain data is as records in rows but there are still places where transpositions occur such as between the list of qualities to the headers for component quality data and from the process operations on the Define Modes panel to the headers for the Unit Operations panel and SSI or
We (Nicola and I) have been working on a very large distribution problem with 1000s of locations recently and had to find some creative ways to map from the worksheets of data provided into SSI’s and TABLES to feed into a GRTMPS model. (We are using a mix of formats for speed and convenience). It isn’t enough to simply put the data in the right format – it has to be dynamic so that it updates automatically when the source data changes – including the addition or deletion of locations.
There are of course lots of ways to transpose data in Excel.
Copy/Paste Special Transpose will tip a list over onto its side. If there are formulae they will need to be adjusted for their new position, so you have to make everything a fixed reference or do lots of dragging aftwards. You also lose the connection to the original list. So this is good for one-off conversions but not great for maintenance.
You could just point each cell at the at one you want. B1=A2, C1=A3, D1=A4 etc. That would update if you changed the contents of the cells, but will be slow to set-up for a sizeable data set and difficult to maintain as you can’t just copy the formula into an adjacent cell. (E1 would end up with B4, not A5).
There is a TRANSPOSE array function. This does maintain the link to the original list. Change a value in the original data and the column name will update too. But things go horribly wrong if you insert or delete in the original list or try to move it. I also find it very fiddly to get the control-enter in with just the right selection (Not a trivial task when that is more than 1000 cells).
So I had a moan (rant even) at Nicola about what a pain this was and challenged her to find some better way. Her trawl though the internet yielded the following solution:
=INDIRECT(ADDRESS(COLUMN(A2)-COLUMN($A$2)+ROW($A$2),ROW(A2)-ROW($A$2)+COLUMN($A$2)))
where A2 is the starting point of the data that you want to transpose.
Consider the list of GRTMPS team in column A.
The transposing formula is entered in D2 and then filled sidewise into the columns to the right until the end of the list is reached. It will update if any entry is changed and is easy to maintain as the formula can just be copied. A COUNT of entries in the row and column copies of the list is enough to flag up when the range should be extended further to the right.
How does it work? INDIRECT is an address function that says what the input solves to it should be treated as a Cell Reference (or Range Name). So, for example =INDIRECT(LEFT("ABC",1)&2) is the same as saying =A2. The COLUMN and ROW functions translate cell references into position numbers (A=1, B=2 etc.). The calculation works out the offset for both column and row between the start of the list and the destination cell for each element. ADDRESS converts that back into a cell reference. So D2 points to A2. The formula in E2 will use A2 again where it is a fixed reference, but shift to B2, one column further over, where it is not. It will solve to A3. Very clever! (Use the Evaluate Formula tool in Excel to step through each element if you want to see exactly what is going on).
I have no doubt that this is going to be a very useful technique. If you extended the formula down through rows you can flip a whole table. the scope in Report Generator appears to be considerable. I’m definitely going to have a look at my template for mapping final pool-property values into a component quality SSI. I suspect this approach could also be extended to do useful things like splitting a set of results records into a column per period, provided you start with it appropriately sorted and know how many periods there are.
PSI-2 doesn’t understand ADDRESS or INDIRECT so this mehod doesn't work in simulator workbooks - however, after the first version of this note was posted a couple of colleagues and some clients pointed out that you can use OFFSET instead, like so:
From Kathy's, Nicola's and Shari's Desks.
updated 20th July 2021