Jan 11 2024 11:56 AM
This issue has 2 piece's:
I have been trying to figure out this formula for a while now. I am attempting to get the initials from Column A (that are every 7 rows) in order in Column AR. So AR5 should = AD. AR6 should = ADL. So on and so forth.
Then I need Colum AS5 to = AI5, AT5 = AI6, AU5 = AI7, AV5 = AI8, AW5=AI9, AX5=AI10, AY5=AI11
AS6=AI12, AT6=AI13.... ultimately looking something like this:
Jan 11 2024 12:21 PM
In cell AR5:
=TOCOL(A5:A25,1)
In cell AS5:
=WRAPROWS(AI5:AI25,7)
With Office 365 or Excel for the web you can enter the above formulas in cells AR5 and AS5.
Jan 11 2024 12:35 PM
Jan 11 2024 12:46 PM
SolutionThis formula is in cell AR5 and filled down. The formula has to be entered with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.
=INDEX($A$5:$A$25,SMALL(IF(NOT(ISBLANK($A$5:$A$25)),ROW($A$5:$A$25)-4),ROW(A1)))
This formula is in cell AS5 and filled across range AS5:AY7.
=INDEX($AI$5:$AI$25,COLUMN(A1)+(ROW(A1)-ROW($A$1))*7)
I assume that you don't work with Office 365 or Excel for the web. With older versions such as Excel 2013 you can apply the above formulas.
Jan 11 2024 01:46 PM
Jan 11 2024 03:03 PM
OK, so this is what you don't want! But, as a 365 developer, I would use nothing else.
There is one formula in cell AQ2 and that's it.
= LET(
facl, TAKE(WRAPROWS(facility,7), 1),
init, TAKE(WRAPROWS(initials,7),,1),
ftot, WRAPROWS(totals,7),
gtot, BYROW(ftot, LAMBDA(x,SUM(x))),
hdr, HSTACK("INIT", facl, "Total"),
body, HSTACK(init, ftot, gtot),
VSTACK(hdr, body)
)
Had you required it, it wouldn't have taken much to calculate the facility totals from the daily totals within the formula.
Note: As of a couple of days ago, one line of code got a bit simpler on my insider beta Excel version
gtot, BYROW(ftot, SUM),
Jan 15 2024 06:46 AM
Jan 11 2024 12:46 PM
SolutionThis formula is in cell AR5 and filled down. The formula has to be entered with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.
=INDEX($A$5:$A$25,SMALL(IF(NOT(ISBLANK($A$5:$A$25)),ROW($A$5:$A$25)-4),ROW(A1)))
This formula is in cell AS5 and filled across range AS5:AY7.
=INDEX($AI$5:$AI$25,COLUMN(A1)+(ROW(A1)-ROW($A$1))*7)
I assume that you don't work with Office 365 or Excel for the web. With older versions such as Excel 2013 you can apply the above formulas.