The Issue
In simple terms, I am trying to set a formula for an alternating pattern. The issue I keep running into is the fact that there are two alternating values, and Google Sheets doesn't like to repeat only one of those values without the other.
I have created an example sheet to demonstrate my issue. In Column A, a date is input. Column B and Column C then autofill with the day of the week and AM or PM respectively. Every other value in Column C alternates between AM and PM. I am trying to set it up so that the row is blank until a value in input in Column A. The issue comes when there is an odd number of Dates in Column A. Note that the alternating AM/PM pattern will never change.
What I've Tried
As seen in the image above, there are three main methods that I have tried. The data in C2:C8 is the desired result.
Method 1:
E2:=transpose(split({rept(join(";",{"AM";" "})&";",(roundup(counta(A2:A9)/2)))},";"))
F3:=transpose(split({rept(join(";",{"PM";" "})&";",(counta(A2:A9)/2))},";"))These formulas work separately, and best represent what I am trying to accomplish, but I have not found a way to combine them to work together in one column.
Method 2:
H2:=transpose(split({rept(join(";",{"AM";"PM"})&";",(roundup(counta(A2:A9)/2)))},";"))This is essentially the same as Method 1, but put into one formula. The issue here is that Google Sheets doesn't like to repeat half a number of times. So if the number of times to repeat (
counta(A2:A9)/2) contains a half (i.e. 3.5), it will still round down to the nearest whole number.
Method 3:
J2:=ArrayFormula(TEXT(SEQUENCE(3),"")&{"AM";"PM"})This one appeared most promising to me because when incrementing by one, it added one row, but I quickly ran into the issue where if I went over a sequence number of 2, it threw the error
Array arguments to CONCAT are of different size.
References
I have used various search terms and websites to try to solve this, and have yet to find something that works. I may be missing something very simple, though, and hopefully this is a quick solution.
Example Sheet:
https://docs.google.com/spreadsheets/d/1I3EtptFLfDHpAQ8AR6Lwa01dSpJ3Cy8MTX1_OjHExSc/edit?usp=sharing
All my formulas are derived from the websites below:
