Creating a Automate Formula Which Repeats the Values according to Criteria (Dynamic Formula)

Question

I have been finding a way where i can make this thing automate/Dynamic.

Below Sheet have two columns where Col"B" has such values like

KPM11
KPM22
KPM33
KPM44
KPM55
KPM66
KPM77
KPM88
KPM99
KPM100
KPM110

I want to repeat these values until next is start as i have settled it manually in Col"A".

Looking for a way/formula which makes it automate so i can drag it till the range or could be array.

Any help will be highly appreciated.

I have tried to create a formula but its out of my experience.

player0 player0 · Accepted Answer · 2020-12-16T18:43:54

try:

=ARRAYFORMULA(IF(B2:B="",, VLOOKUP(ROW(A2:A), 
 IF(REGEXMATCH(B2:B, TEXTJOIN("|", 1, H:H)), {ROW(A2:A), B2:B}), 2, 1)))