CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
kaitlynmm569
kaitlynmm569

Reputation: 1715

How to get only one of two repeating values

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

Sheet Example

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:

REPT Function in Google Sheets

How to Repeat Multiple Columns N Times in Google Sheets

Upvotes: 0

Views: 85

Answers (2)

basic
basic

Reputation: 11968

If I understand correctly it is enough to use ISEVEN function to alternate by rows:

=ArrayFormula(IF(A2:A,CHOOSE(ISEVEN(ROW(A2:A))+1,"PM","AM"),))

enter image description here

Upvotes: 0

Erik Tyler
Erik Tyler

Reputation: 9345

Delete everything in Col C (including the header) and place this formula in C1:

=ArrayFormula({"AM/PM"; IF(A2:A="",,IF(COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A))=1,"AM","PM"))})

The COUNTIFS finds the number of matches for the date "up to this row" for every row. Since that count will (or should) only ever be a 1 or a 2, the IF makes easy work of assigning "AM" or "PM" accordingly.

Upvotes: 2

Related Questions