I have 2 columns one is a period and another is a cycle. I need to create a 3rd column where I create a cycle identifier. Where the Letter changes on the cycle but resets every period.

I seem to have it with the following formula IF(A1<>A2,1,IF(B1<>B2,C1 1,C1)). Which will give results of 1, 2 or 3. Then to get the numbers into letter form by using a switch SWITCH(C1,1,"A",2,"B",3,"C") in an adjacent cell. However I was curious if there is a more efficient or better way to accomplish this perhaps in all in one formula.

Any suggestions would be greatly appreciated.

CodePudding user response：

Period & Cycle

• Copy this formula to cell C2 and copy it down.

=IF(B2<>B1,IF(A2<>A1,CHAR(65),CHAR(CODE(C1) 1)),C1)

CodePudding user response：

In Excel 365, you could use a spill formula like this:

=CHAR(B2:B15-XLOOKUP(A2:A15,A2:A15,B2:B15) 65)

You could argue that this is less efficient because it uses a lookup so there could be a speed hit with large amounts of data. On the other hand, it could be considered more efficient because it is a single formula and doesn't need to be pulled down.

If you were worried about the speed, you could set the binary search option in xlookup:

=CHAR(B2:B15-XLOOKUP(A2:A15,A2:A15,B2:B15,,2) 65)

(Column A has to be sorted ascending for this to work - I'm fairly sure that where there are duplicates this will still give the first match. However Microsoft are quoted as saying that there is only a slight benefit of using binary search according to this and other articles)

You could make the formula more dynamic:

=CHAR(B2:INDEX(B:B,COUNTA(B:B))-XLOOKUP(A2:INDEX(A:A,COUNTA(A:A)),A2:INDEX(A:A,COUNTA(A:A)),B2:INDEX(B:B,COUNTA(B:B))) 65)

Or using Let

=LET(Period,A2:INDEX(A:A,COUNTA(A:A)),
Cycle,B2:INDEX(B:B,COUNTA(B:B)),
CHAR(Cycle-XLOOKUP(Period,Period,Cycle) 65))