Formula Solutions
February 25, 2020 - by Bill Jelen
Note
This is one of a series of articles detailing solutions sent in for the Podcast 2316 challenge.
While I expected mostly Power Query or VBA solutions to the problem, there were some cool formula solutions.
Hussein Korish sent in a solution with 7 unique formulas, including a dynamic array formula.
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| K13:K36 | K13 | =INDEX(FILTER(IF(LEN(TRANSPOSE(FILTER($H$3:$AA$3,H3:AA3>LEN(H3:AA3))))>2,TRANSPOSE(FILTER($H$3:$AA$3,H3:AA3>LEN(H3:AA3))),""),IF(LEN(TRANSPOSE(FILTER($H$3:$AA$3,H3:AA3>LEN(H3:AA3))))>2,TRANSPOSE(FILTER($H$3:$AA$3,H3:AA3>LEN(H3:AA3))),"")<>""),MATCH(SEQUENCE(COUNTA($J$13:$J$36),,1,1),SEQUENCE(COUNTA($J$13:$J$36)/COUNTA($B$4:$B$9),,1,COUNTA($B$4:$B$9)),1)) |
| L13:L36 | L13 | =OFFSET($H$3,MOD(COUNTA($J$12:J12)-1,COUNTA($B$4:$B$9))+1,TRANSPOSE(MATCH(K13,$H$3:$AA$3,0))+COLUMNS($L$12:$P$12)-COLUMNS(L$12:$P$12)) |
| M13:M36 | M13 | =OFFSET($H$3,MOD(COUNTA($J$12:J12)-1,COUNTA($B$4:$B$9))+1,TRANSPOSE(MATCH(K13,$H$3:$AA$3,0))+COLUMNS($L$12:$P$12)-COLUMNS(M$12:$P$12)) |
| N13:N36 | N13 | =OFFSET($H$3,MOD(COUNTA($J$12:J12)-1,COUNTA($B$4:$B$9))+1,TRANSPOSE(MATCH(K13,$H$3:$AA$3,0))+COLUMNS($L$12:$P$12)-COLUMNS(N$12:$P$12)) |
| O13:O36 | O13 | =OFFSET($H$3,MOD(COUNTA($J$12:J12)-1,COUNTA($B$4:$B$9))+1,TRANSPOSE(MATCH(K13,$H$3:$AA$3,0))+COLUMNS($L$12:$P$12)-COLUMNS(O$12:$P$12)) |
| P13:P36 | P13 | =SUM(L13:O13) |
| J13:J36 | J13 | =INDEX($B$4:$B$9,MATCH(MOD(COUNTA($J$12:J12)-1,COUNTA($B$4:$B$9))+1,SEQUENCE(COUNTA($B$4:$B$9),1,1),0)) |
| Dynamic array formulas. | ||
Prashanth Sambaraju sent in another formula solution that uses five formulas.
The formulas used above:
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J15:J38 | J15 | =IF(MOD(ROWS($J$15:J15),6)=0,6,MOD(ROWS($J$15:J15),6)) |
| K15:K38 | K15 | =OFFSET($A$3,J15,J$15,1,1) |
| L15:L38 | L15 | =CONCATENATE("Employee", " ",ROUNDUP(ROWS($J$15:J15)/6,0)) |
| M15:P38 | M15 | =OFFSET($A$3,$J15,MATCH($L15,$B$3:$AA$3,0)+MOD(COLUMNS($A:A),5)) |
| Q15:Q38 | Q15 | =SUM(M15:P15) |
René Martin sent in this formula solution with three unique formulas:
The formulas used in the above:
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| I12:N12 | I12 | =A3 |
| I13:O13,O14:O36 | I13 | =IF(COLUMN()=9,OFFSET($A$2,MOD(ROW(A1),6)+1,0),IF(COLUMN()=10,"Employee "&ROUNDUP(ROW(A1)/6,0),IF(COLUMN()=15,SUM(E13:H13),OFFSET($G$3,MOD(ROW(A6),6)+1,ROUNDUP(ROW(A1)/6,0)*5-7+COLUMN(A1))))) |
| I14:N36 | I14 | =IF(COLUMN()=9,OFFSET($A$2,MOD(ROW(A2),6)+1,0),IF(COLUMN()=10,"Employee "&ROUNDUP(ROW(A2)/6,0),OFFSET($G$3,MOD(ROW(A7),6)+1,ROUNDUP(ROW(A2)/6,0)*5-7+COLUMN(A2)))) |
An alternative solution from René Martin:
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| I12:N12 | I12 | =A3 |
| I13:O13,O14:O36 | I13 | =IF(COLUMN()=9,OFFSET($A$2,MOD(ROW(A1),6)+1,0),IF(COLUMN()=10,"Employee "&ROUNDUP(ROW(A1)/6,0),IF(COLUMN()=15,SUM(E13:H13),OFFSET($G$3,MOD(ROW(A6),6)+1,ROUNDUP(ROW(A1)/6,0)*5-7+COLUMN(A1))))) |
| I14:N36 | I14 | =IF(COLUMN()=9,OFFSET($A$2,MOD(ROW(A2),6)+1,0),IF(COLUMN()=10,"Employee "&ROUNDUP(ROW(A2)/6,0),OFFSET($G$3,MOD(ROW(A7),6)+1,ROUNDUP(ROW(A2)/6,0)*5-7+COLUMN(A2)))) |
Excel MVP Roger Govier sent in a formula solution. First off, Roger deleted the unnecessary columns from the original data. Roger points out that you could leave them there, but then you have to adjust the column index numbers appropriately.
Roger used three named ranges. This figure shows _rows selected.
He also added _Cols as B3:U3. He redefined my Ugly_Data as B4:U9.
Roger’s solution is two formulas, copied down and one formula copied down and across.
Return to the main page for the Podcast 2316 challenge.
To read the last article and Bill’s composite solution: Composite Solution to Podcast 2316 Challenge
