Transpose previous value to next column based on conditions

I hope this is relatively simple.

I need to transpose the previous value from a row to the next available column. However, this should be based on the value in Col A. So if the value in Col A changes, they cycle should start again. It should also only go to a maximum of the last 20 rows then start again.

I have attached a sample of what I am trying to do. I need to fill each of the "Num.x" columns. My data has over 10,000 rows.

A, B,C is OK
But D, after 20 reached, does the cycle logic change?
Last 4 rows, 1st column, why not 3,3,3,3 and 2nd column, why not 0,0,0,0?
What is the logic behind?

Please try at C2

=IF(COUNTIFS($A$2:$A2,$A2)-COLUMNS($C2:C2)>0,INDEX($B$1:$B2,MAX(0,COUNTIFS($A$2:$A2,$A2)-21)+COLUMNS($C2:C2)+MATCH(2,1/($A$1:$A2<>$A2))),"")

In C2 then copy across

Please Login or Register  to view this content.

Change C1 to value "1" then format it as "Num."#,
D1=C1+1
Drag accross
C2:

Please Login or Register  to view this content.