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

tombstonz

New Member
Joined
Aug 17, 2024
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Hello, please test if the following is what you are looking for:

Excel Formula:
=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))
 
Upvote 0
Hello, please test if the following is what you are looking for:

Excel Formula:
=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?
 
Upvote 0
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...
 
Upvote 1

Forum statistics

Threads
1,224,462
Messages
6,178,816
Members
452,881
Latest member
motivationgyan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top