Need help with Formula for incremental cell reference

[BlizzCo]

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:

[#2]

@BlizzCo 

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.

[#3]

I appreciate the response, but neither of these solutions worked for me. I was hoping whatever formula I used I would be able to drag down so that it would fill the cells as the data far surpasses the snip I took. Thanks for trying though.

[#4 · Accepted solution]

@BlizzCo 

This 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.

[#5]

This worked perfectly! Thank you so very much!

[#6]

@BlizzCo 

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),

[#7]

Peter,

Thank you so much for taking the time to reply! I hope you have a wonderful week!