find partial matches in header of all worksheets and replace with set values

albytross

New Member
Joined
Sep 22, 2021
Messages
1
Office Version
  1. 365
Hi,
My background is CSS and HTML (yes, i'm a fossil).

Can anyone help with some code? I'm impressed how much i've managed to get done using various forums but now I am well and trully stuck.

I have a workbook open with numerous worksheets (the total number of worksheets changes, depending on the data dump I receive)
The worksheets have similar but different header values despite referencing comparable data, and I want to make the column names uniform (so later I can easily append/merge the data)

I'm searching for some code which:
- References a user-defined list of header values contained on a master-sheet
- searches the other open worksheet for "similar" header values to those in the list, based on either partial matches or matches of multiple criteria, and then renames the cell to the list value

e.g. user defined list contains "car" "fuel" "kilometers" - but my worksheets contain values such as "Car_1", "Car_MG_2" "Car_Drive" "Fuel" "fuel_1" "kilometers_2" "kilometers_odo".
Anything with "car**" should rename to "car", and so on.

my data doesnt relate to cars but it seemed an easy example.

Alby
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,298
Office Version
  1. 365
Platform
  1. Windows
I want to make the column names uniform (so later I can easily append/merge the data)
Since the goal is to append/merge the data anyway, have you considered using Power Query for the exercise ?
 

Forum statistics

Threads
1,148,367
Messages
5,746,283
Members
424,006
Latest member
Metal_warrior

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
Top