Merging Google Sheets formulas into1 to get the same Result
I am working on developing a formula that references the values in Column A and adapts its functionality based on those numbers.
For instance, if I have 25 results in Column A of Sheet1, I would like to split and use the TEXTJOIN function according to the corresponding values in Column A of Sheet2, as illustrated in the formula below.
My goal is to create a dynamic formula that operates in this manner.
Sheet1
| Name |
|---|
| Value1 |
| Value2 |
| Value3 |
| Value4 |
| Value5 |
| Value6 |
| Value7 |
| Value8 |
| Value9 |
| Value10 |
| Value11 |
| Value12 |
| Value13 |
| Value14 |
| Value15 |
| Value16 |
| Value17 |
| Value18 |
| Value19 |
| Value20 |
| Value21 |
| Value22 |
| Value23 |
| Value24 |
| Value25 |
Sheet2
| Number of Textjoins | Results |
|---|---|
| 5 | =TEXTJOIN("', '", TRUE, ARRAYFORMULA("'" & Sheet1!B2:B6 & "'")) |
| 6 | =TEXTJOIN("', '", TRUE, ARRAYFORMULA("'" & Sheet1!B7:B12 & "'")) |
| 4 | =TEXTJOIN("', '", TRUE, ARRAYFORMULA("'" & Sheet1!B13:B16 & "'")) |
| 5 | =TEXTJOIN("', '", TRUE, ARRAYFORMULA("'" & Sheet1!B17:B20 & "'")) |
| 5 | =TEXTJOIN("', '", TRUE, ARRAYFORMULA("'" & Sheet1!B21:B25 & "'")) |
Tried but got an error:
=TEXTJOIN("', '", TRUE, ARRAYFORMULA(
IF(MOD(ROW(Sheet1!B2:B)-ROW(Sheet1!B1), 5) = 0, "'" & Sheet1!B2:B & "'", "")))
Here is the final Result:
| Number of Textjoins | Results |
|---|---|
| 5 | 'Value1'', ''Value2'', ''Value3'', ''Value4'', ''Value5' |
| 6 | 'Value6'', ''Value7'', ''Value8'', ''Value9'', ''Value10'', ''Value11' |
| 4 | 'Value12'', ''Value13'', ''Value14'', ''Value15' |
| 5 | 'Value16'', ''Value17'', ''Value18'', ''Value19', ''Value20' |
| 5 | 'Value21'', ''Value22'', ''Value23'', ''Value24'', ''Value25' |
Solution
Here's a generalized logic which you may adapt to your scenario:
=let(Λ,D2:D6,
map(Λ,lambda(Σ,textjoin(", ",1,chooserows(B2:B,sequence(Σ,1,if(row(Σ)=row(Λ),1,sum(D2:offset(Σ,-1,))+1)))))))
- Downloading a PDF version of the open spreadsheet in Google Drive via scripts
- exporting spreadsheet to pdf then saving the file in google drive
- How to get last number in a row on Google Spreadsheets
- A Script to Simplify Creating a SO Table
- Archive a row after a dropdown status is selected on multiple sheets
- How to extract URL from Link in Google Sheets using a formula?
- Create list of dates in Google Sheets based on day-of-month schedule
- Get Google Sheet by ID?
- Return Column Based on Column Headers
- Add title row with ARRAYFORMULA in Google Sheets
- Why does my Google Service Account not see files shared with it, but can see files created with it?
- Google Sheets macro to copy a line of data to next empty row on another sheet
- Google Sheets - Auto border to a Dynamic Data
- Fix Google Sheets formula so it will append the header title of each column it is pulling data from, instead of just the first column's title
- VLOOKUP IMPORTRANGE to select the column number in another file also
- Split cell with dates and find matching dates on headers
- How to get the next Bi-weekly date based on a given start date and today's date in Google Sheets
- How can I determine if a respondent's email address is unique in a list?
- ARRAYFORMULA in GOOGLE SHEETS
- Can a google-apps-script be written to download a csv file from spreadsheet directly to a local hard drive with an onEdit trigger?
- API call to directory.members.insert failed with error: Resource Not Found: groupKey
- How to find common words in two columns in Google Sheets
- Get list of unique words without numbers and symbols from paragraph range in Google Sheet?
- Apps Script Delete Image
- App Script to download specific cells in Google Sheet to PDF
- Export Google Docs comments into Google Sheets, along with highlighted text?
- INDIRECT function in an INDEX-MATCH-MATCH
- How to write to a "Google Spreadsheet" from Excel 2003 VBA
- How to use date with LIKE inside query?
- Know if the cell is being used in any formula