I have a dataset that looks like this:
| Column A | Column B | Column C | Column D |
|---|---|---|---|
| Text1 | Text1 | Text1 | 189.21 |
| Text2 | Text2 | Text2 | 180.20 |
| Text3 | Text3 | Text3 | 236.67 |
| Text4 | Text4 | Text4 | 204.23 |
| Text5 | Text5 | Text5 | 205.21 |
| Text6 | Text6 | Text6 | 209.20 |
The array is of variable length but fixed width.
I’m looking for a formula that can create groups (of 3 rows per group but if this could be adjustable that would be ideal) such that each group has no values in column D within 2 of each other.
So for the example above the 2 groups returned could be
| Column A | Column B | Column C | Column D |
|---|---|---|---|
| Text1 | Text1 | Text1 | 189.21 |
| Text2 | Text2 | Text2 | 180.20 |
| Text4 | Text4 | Text4 | 204.23 |
and
| Column A | Column B | Column C | Column D |
|---|---|---|---|
| Text3 | Text3 | Text3 | 236.67 |
| Text5 | Text5 | Text5 | 205.21 |
| Text6 | Text6 | Text6 | 209.20 |
If there are multiple ways to make a group it does not matter which way is chosen as long as no data is duplicated.
4
You can use this formula to return an extended table with group ID
=LET(groupBy,G1,
data,Table1[#All],
id,SEQUENCE(ROWS(data)-1),
dataExtended,HSTACK(ROUNDUP(id/groupBy,0),DROP(data,1)),
headerExtended,HSTACK("GroupID",TAKE(data,1)),
VSTACK(headerExtended,dataExtended))
You can adjust groupBy by changing the value in G1.
I formatted the data as table.