This might be possible with some ingenious queries, but to me it seemed easier to write a script (entered via Tools > Script editor). It updates the sheet as new data is entered. Some explanation:
- The output is set to begin at cell F1, as in your example. This can be changed by changing the row number and column number
1, 6
near the end of the script.
- The number of desired columns,
newColumns
, is inferred by going through column A until there is an empty cell in it. In your case, A4 is the first empty cell, which signifies you have 3 rows of data, so there will be 3 columns.
- The script assumes there is an empty column to the right of the input, which serves as an indication that the input ends there.
The logic is pretty straightforward: grabbing and pushing data around.
function onEdit() {
var i,j,k;
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = sheet.getDataRange();
var v = range.getValues();
i = 0;
while (v[i][0]) {i++;}
var newColumns = i;
var row = [];
for (k=0; k<newColumns; k++) {
row.push(v[k][0]);
}
var values = [row];
for (i=0; i<v.length-newColumns+1; i++) {
if (v[i][0] == 'Date') {
for (j=1; j<v[i].length; j++) {
if (!v[i][j]) {break;}
row = [];
for (k=0; k<newColumns; k++) {
row.push(v[i+k][j]);
}
values.push(row);
}
}
}
var newRange = sheet.getRange(1, 6, values.length, newColumns);
newRange.setValues(values);
}
If you want to put the output on another sheet, replace the command
var newRange = sheet.getRange(1, 6, values.length, newColumns);
with
var targetSheet = ss.getSheetByName('Your target sheet name');
var newRange = targetSheet.getRange(1, 6, values.length, newColumns);
(You may also want to change 1, 6 to something else like 1, 1 now that the output has its own sheet.)
Older post, but I wrote a single-cell array formula that accomplishes this task and placed it into your editable sheet, in a new sheet I created for the purpose (Sheet2).
Headers are manually entered in Sheet2!A1:E1.
The following array formula is entered into Sheet2!A2:
=ArrayFormula(IF(ROW(Sheet1!A2:A)>COUNTA(Sheet1!A2:A)*18+1,"",{VLOOKUP(Sheet1!A$1,TRANSPOSE(QUERY({Sheet1!A:AM})),INT((ROW(Sheet1!A2:A)-2)/18)+2,FALSE),VLOOKUP(VLOOKUP(Sheet1!A$1,TRANSPOSE(QUERY({Sheet1!A:AM})),INT((ROW(Sheet1!A2:A)-2)/18)+2,FALSE),Sheet1!A2:AM,{2,3},FALSE),VLOOKUP(VLOOKUP(Sheet1!A$1,TRANSPOSE(QUERY({Sheet1!A:AM})),INT((ROW(Sheet1!A2:A)-2)/18)+2,FALSE),Sheet1!A2:AM,MOD(ROW(Sheet1!A2:A)-2,18)+4,FALSE),VLOOKUP(VLOOKUP(Sheet1!A$1,TRANSPOSE(QUERY({Sheet1!A:AM})),INT((ROW(Sheet1!A2:A)-2)/18)+2,FALSE),Sheet1!A2:AM,MOD(ROW(Sheet1!A2:A)-2,18)+22,FALSE)}))
Best Answer
I'll describe the solution step by step:
Get all names in separate cells
does this, by taking nonempty cells in the range A2:A (this is what
filter
is for), joining them using the same comma-space separator you have within cells, and then splitting by that separator (not by space or comma separately).Transpose
This is easy: just add
transpose
.Get unique names only
This is only if you don't want the counts.
unique
does the job then.Get unique names and their counts
For this I would use
query
instead ofunique
:Which says: group by name, and include the count of repetitions. The last argument "0" says the data has no header row, which it doesn't since we started with A2.