google-sheetsgoogle-sheets-formula
Create a long format summary
How could I convert a wide table to long format using a formula in Google Sheets? Within each group, the values should be alphabetically sorted (e.g. anna is first in the expected output). Blank cells should be ignored.
The expected output is the data summarised in long format:
Solution
You can try this formula. Change the range in the Let function to accommodate your dataset (here listed as A:D based on the question).
=let(range,A:D,toprow,row(index(range,1,1)),data,arrayformula(split(transpose(split(textjoin(",",true,tocol(BYCOL(range,LAMBDA(bc,vstack("|"&filter(bc,row(bc)=toprow),sort(filter(bc,row(bc)>toprow),1,1)))),1,1)),"|")),",")),wraprows(torow(BYROW(data,LAMBDA(k,makearray(1,(COUNTA(k)-1)*2,lambda(r,c,if(isodd(c),index(k,r,1),(index(k,r,(c/2)+1))))))),1),2))
- Multiple submission at exact same time in Google App Script
- Hello world menu and sidebar for Google Workspace Add-On for Google Sheets
- Did Google Sheets stop allowing json access?
- How to Run Script on Multiple Spreadsheet Tabs
- Filtering data from other google Spreadsheet sheet based on a combo value from other sheet in the same spreadsheet
- Can I count occurrences of each unique item and then sort by that count
- Check if Cell Contains Month and Compare to Date
- Arrayformula with Query in Google Sheets
- Apps Script: why does name of properties cause "Maximum call stack size exceeded"?
- Conditional borders troubleshooting
- Troubleshooting Spreadsheet Form Submit Events Google
- Macro for replacing data and then collating it?
- QUERY Formula referring to the value of a cell rather than a constant piece of text
- Index Function - Use One Formula to Generate the Result List with Two Variables
- google sheets IMPORTXML for currency prices
- How to add plus 5 hour to the copy Cell and past in other with plus time
- How to connect Google Sheets to Database
- Merging Google Sheets formulas into1 to get the same Result
- TypeError: UrlFetchApp.fetch(...).then is not a function
- Using Column Headers with COUNTIF To Add those that Fall Between Dates in Cells
- Using SUMIFS with MAP/LAMBDA
- Deal with line break while using IMPORTXML in Google Sheet
- What is an equivalent for INDEX in ARRAYFORMULA?
- Google Sheet Script - Removing a gap of blank cells within a range to keep a list compact and tidy?
- For loop running only for first element
- How can I use a Custom Function in Formulas across Multiple Spreadsheets?
- How to use a filter inside SortN
- Problems with ShinyApp and Google authentification, when making Public
- Newbie Help: Syntax error: SyntaxError: Unexpected identifier (JavaScript)
- Error in Google App Script when submitting form data to Google Sheets