Need help with an arrayformula that repeats a list in a particular way.

In Google Sheets.

Hello all!

I'm trying to run a pay equity analysis and am having trouble automating the sheet.

I'm trying to use =ARRAYFORMULA to automate the report based on other data but I have a problem in repeating a list.

I have a list of titles to compare against (10 executive titles). You'll see in the first photo that I use Transpose(Split(Rept( to bring in the list of job titles and they have 4 empty cells between each title. The problem is that the formulas used to make calculations for total comp, total workforce, avg comp are using an arrayformula and so they're looking at a blank cell to compare against. I'd like to bring in the titles, but have them repeat 4 times, have 1 blank cell, then populate the next title in the list for 4 times, 1 blank cell etc. etc.
You can see in the second photo I'm trying to do that, but it stops after the first title.

Any idea how to use an array formula that uses a list of titles and makes them repeat 4 times, have a blank cell, populate the next title etc?

Thanks!



Screen Shot 2021-10-15 at 5.27.06 PM.png Screen Shot 2021-10-15 at 5.34.10 PM.png