Spreadsheet Wizardry: Unwinding Data
OK, I had a workbook with two spreadsheets in it. Sheet1 had 1,340 data points, four per row. Sheet2 also had 1,340 points, but only one point per row. I wanted a scatter plot of the points in the first sheet as a function of the points in the second sheet. They had the same number of points. They were in the same order. They just weren't laid out the same. I needed the four cells in the first row of Sheet1 in a column next to the first four cells in a column of Sheet2. As you might imagine, 1,340 cut 'n' paste operations was not an appealing proposition. I briefly contemplated using perl to redo the CSV for the four-per-row. Efficient though perl is very well suited to these sorts of things, it felt like something a self-respecting spreadsheet program ought to be able to do without assistance.
The answer turned out to be oocalc's OFFSET and ROW functions. ROW, given no arguments, returns the row number of the current cell. A little modulo here, a little integer division there . . . I'd be able to calculate the horizontal and vertical offsets into the four-per-row sheet. I had to subtract two from the row partly because oocalc's origin is 1, 1 and partly because I didn't want the header row included in the calculation. The formula: =OFFSET('Sheet1'.$C$2; INT( (ROW()-2)/4 ) ; MOD( ROW()-2; 4) )
The answer turned out to be oocalc's OFFSET and ROW functions. ROW, given no arguments, returns the row number of the current cell. A little modulo here, a little integer division there . . . I'd be able to calculate the horizontal and vertical offsets into the four-per-row sheet. I had to subtract two from the row partly because oocalc's origin is 1, 1 and partly because I didn't want the header row included in the calculation. The formula: =OFFSET('Sheet1'.$C$2; INT( (ROW()-2)/4 ) ; MOD( ROW()-2; 4) )