I’m trying to create a smart formula in Google Sheets. It needs to look at Column A in Sheet1 and do different things based on the numbers there.
Here’s what I want: If Sheet1 has 25 items in Column A, I want to use TEXTJOIN to group them. The groups should match the numbers in Column A of Sheet2.
I’ve set up two sheets to show what I mean:
Sheet1 has a list of 25 values (Value1 to Value25).
Sheet2 shows how I want to group them:
- First group: 5 values
- Second group: 6 values
- Third group: 4 values
- Fourth group: 5 values
- Last group: 5 values
Each group uses TEXTJOIN to combine the values with commas and quotes.
I tried this formula but it didn’t work:
=TEXTJOIN("', '", TRUE, ARRAYFORMULA(
IF(MOD(ROW(Sheet1!B2:B)-ROW(Sheet1!B1), 5) = 0, "'" & Sheet1!B2:B & "'", "")))
Can anyone help me make a formula that does this grouping automatically? It should adapt if the number of values or group sizes change. Thanks!
I’ve encountered similar challenges in my spreadsheet work. One approach that could work for you is using a combination of INDEX, SEQUENCE, and TEXTJOIN functions. Here’s a formula you might find useful:
=ARRAYFORMULA(TEXTJOIN(CHAR(10), TRUE, TEXTJOIN(", ", TRUE, “'” & INDEX(Sheet1!A:A, SEQUENCE(Sheet2!A1, 1, SUM(Sheet2!A$1:A1) - Sheet2!A1 + 1)) & “'”)))
This formula dynamically adapts to changes in group sizes and total items. It pulls the group sizes from Sheet2 and uses them to create the appropriate groupings from Sheet1. You’ll need to place this in Sheet2 and copy it down for each group. The CHAR(10) creates a new line between groups for better readability.
Keep in mind that this solution assumes your data is structured exactly as you described. You may need to adjust cell references if your layout differs.
I’ve dealt with similar challenges in my work, and I found a solution that might help you out. Instead of using MOD, try combining INDIRECT with ADDRESS to dynamically reference your ranges. Here’s a formula that should do the trick:
=ARRAYFORMULA(TEXTJOIN(", ", TRUE, “'” & INDIRECT(ADDRESS(ROW(Sheet2!A1), 1, 4, 1, “Sheet1”) & “:” & ADDRESS(ROW(Sheet2!A1) + Sheet2!A1 - 1, 1, 4, 1, “Sheet1”)) & “'”))
This formula adapts to changes in group sizes and total items. It references Sheet2 for group sizes and dynamically builds ranges from Sheet1. You’ll need to put this in Sheet2 and drag it down for each group. It’s not perfect, but it should get you closer to what you’re after. Let me know if you need any clarification on how it works.
hey charlottew, try using OFFSET with COUNTA in your TEXTJOIN.
=TEXTJOIN(“, “, TRUE, ARRAYFORMULA(”'” & OFFSET(Sheet1!A1,0,0,COUNTA(Sheet1!A:A)) & “'”))
tweak it as needed.