How to convert a data matrix into a relational table using Google Sheets formula?

Question

I've got a set of data in a matrix form and I'm trying to use a formula to generate a relational table from that matrix. Here's an illustration of what I'm trying to achieve:

Matrix:

        | 3/5 | 4/5 | 5/5 |
---------------------------
Player1 |  1  |  0  |  1  |
Player2 |  0  |  1  |  2  |
Player3 |  1  |  1  |  2  |

Goal:

Player  | Date | Value |
------------------------
Player1 | 3/5  |   1   |
Player3 | 3/5  |   1   |
Player2 | 4/5  |   1   |
Player3 | 4/5  |   1   |
Player1 | 5/5  |   1   |
Player2 | 5/5  |   2   |
Player3 | 5/5  |   2   |

See the real data here: https://docs.google.com/spreadsheets/d/1DNaoBnYMNbznUoAJahW9-UfGq6LbTG3TXgXPz90bWas

Anyway, I followed the instructions in this answer and came up with this formula:

=ArrayFormula(QUERY(VLOOKUP(HLOOKUP(matrix!A3,matrix!A3:A,INT((ROW(matrix!A3:A)-
 ROW(matrix!A3))/COLUMNS(matrix!B1:Z1))+1,0),{matrix!A3:T, IF(ROW(matrix!A3:A), 
 matrix!B1:Z1)}, {SIGN(ROW(matrix!A3:A)), MOD(ROW(matrix!A3:A) - 
 ROW(matrix!A3), COLUMNS(matrix!B1:Z1)) + {2, 2+COLUMNS(matrix!B1:Z1)} }, 0 ), 
 "select Col1, Col3, Col2 where Col2 is not null and Col2 != 0 order by 
 Col3,Col2", 0))`

It does work, sort of, but with the downside that to generate the rows it uses a lot of empty rows in the matrix sheet, and practically requires that the matrix sheet have at least numCols*numRows number of rows. Now I could just add 3500 empty rows to the matrix and be done with it, but somehow it feels like there should be a better way. Any ideas on how to improve it?

score 2 · Accepted Answer · answered Jun 24 '19 at 20:15

2

=ARRAYFORMULA({"Player", "Date", "Value"; 
 SORT(SPLIT(TRANSPOSE(SPLIT(TRIM(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(Sheet1!B2:Z<>"","♠"&Sheet1!A2:A&"♦"&Sheet1!B1:1&"♦"&Sheet1!B2:Z,)),,999^99)),,999^99)),
 "♠")), "♦"), 2, 1, 1, 1)})

answered Jun 24 '19 at 20:15

player0

124,011
12
67
124

1

That seems to work, cool! Would you mind writing a few lines explaining how the formula works? And what those little spades and diamonds do? :) – yzfr1 Jun 24 '19 at 20:39
2

sure... they do nothing. they are there as unique symbol for SPLIT so SPLIT function could split joined stuff exactly where needed. the core idea is: `=ARRAYFORMULA(IF(B2:Z<>"","♠"&A2:A&"♦"&B1:1&"♦"&B2:Z,))` which translates as: if range B2:Z isn't empty append column A then row 1 then range B2:Z together. then just concentrate it all in one cell, then split on unique symbols and done. – player0 Jun 24 '19 at 20:45
Thanks for the help! One final question. Right now the query supports unlimited number of rows, but is hardcoded to end at column Z. Is there a way to make it support unlimited columns as well? – yzfr1 Jun 25 '19 at 18:07
1

`Sheet1!B2:Z` would need to become: `INDIRECT("Sheet1!B2:"&ADDRESS(ROWS(Sheet1!A:A), COLUMNS(Sheet1!1:1), 4))` – player0 Jun 25 '19 at 18:14

score 0 · Answer 2 · answered May 23 '23 at 08:18

I faced the same issue and didn't manage to get it resolved with a formula. An alternative is to write an App script. It takes the active sheet's (the matrix) and creates a new tab with the resulting table.

function onOpen(e) {
  SpreadsheetApp.getUi()
      .createMenu('Generate table from matrix')
      .addItem('For active sheet', 'activeSheet')
      .addToUi();
}

function activeSheet() {
  generateTable(SpreadsheetApp.getActive().getActiveSheet().getName())
}

function generateTable(sheetName) {
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName)
  var numRows = sheet.getRange(1,1,sheet.getLastRow(),1).getNumRows();
  var numColumns = sheet.getRange(1,1,1,sheet.getLastColumn()).getNumColumns();
  var outputArray = [];
  for (var row=2;row <= numRows;row++) {
    for (var col=2;col <= numColumns;col++) {
      outputArray.push([sheet.getRange(row, 1).getDisplayValue(), sheet.getRange(1, col).getValue(), sheet.getRange(row, col).getValue()])
    }
  }
  var newSheet = SpreadsheetApp.getActive().insertSheet(sheetName + " - table")
  newSheet.getRange(1, 1, 1, 3).setValues([['Player','Date','value']])
  newSheet.getRange(2, 1, outputArray.length, 3).setValues(outputArray)
}

How to convert a data matrix into a relational table using Google Sheets formula?

2 Answers2

Linked