Formula / Script to do Find and Replace for Multiple Groups of 10 rows replacing with different words / phrase each time

[tombstonz]

I need help with a formula / script to do a find and replace for one phrase per row for a group of 10 rows and I have around 60 different words / phrases.

I need a group of 10 so at the end I will have around 600 total rows of data. For example

Column A
1. alabama license plate frame
2. alabama license plate
3. alabama license plate cover
4. license plate frame for alabama
5. license plate red alabama
6. red license plate frame alabama
7. alabama metal license plate frame
8. state of alabama license frame
9. thin rim license frame alabama
10. alabama license plate frame

Column B
Hawaii
Alaska
Colorado
Puerto Rico
etc

So the script would run and replace Alabama with Hawaii and then skip one row and paste those in column A in rows 12 - 21 and then the script would run again and replace either Alabama or Hawaii (whatever is easiest) with Alaska and then paste those rows in cells 23 - 32 (skipping row 22), and continue down the list

TIA

 

[hagia_sofia]

Hello, please test if the following is what you are looking for:

=LET(
col_A,A1:A10,
col_B,B1:B4,
col_C,IFNA(EXPAND(col_A,ROWS(col_A)+1),""),
DROP(DROP(REDUCE("",col_B,LAMBDA(a,b,VSTACK(a,SUBSTITUTE(col_C,"alabama",b)))),1),-1))

 

[tombstonz]

Hello, please test if the following is what you are looking for:

=LET(
col_A,A1:A10,
col_B,B1:B4,
col_C,IFNA(EXPAND(col_A,ROWS(col_A)+1),""),
DROP(DROP(REDUCE("",col_B,LAMBDA(a,b,VSTACK(a,SUBSTITUTE(col_C,"alabama",b)))),1),-1))

Sorry, where do I enter the script does it go in the Macro or somewhere else?

 

[hagia_sofia]

Hello, it is a formula, so, pick your cell and select data for column A (at the moment A1:A10 in the formula) and column B (at the moment B1:B4), just make sure that there is enough space below because it will spil...

 

[tombstonz]

WOW!!!!!! This worked perfect thank you very much, this is awesome!!!

 

[hagia_sofia]

Thanks for the feedback!