Automated search and replace of a long list of items

Hintysen

New Member
Joined
Feb 11, 2020
Messages
8
Office Version
  1. 2013
Platform
  1. Windows
Hello, our newspaper pulls high school sports schedules from various school websites and formats them to build daily event schedules to run in print. We download them as Excel documents. All the schools use the same software.
There are a lot of misspellings and mis-identifications in the listings, along with things that we just want to change to match our style guidelines. Fixing these one by one is a very tedious task.
I have built a table of mistakes (in Column A) and corrections for that mistake (in Column B) as found in a typical list. What I'd like to do is set up a macro that will work its way through this entire list and make all the searches and all the replacements. Also, I'd like to be able to add to the "master" list of errors/corrections as we find more.
Is there a way to do this? Thanks.

Here is the list of errors so far
ErrorsCorrections
Wisconsin Rapids LincolnWisconsin Rapids
UW WhitewaterUW-Whitewater
Stevens Point (SPASH)Stevens Point
UW PlattevilleUW-Platteville
University of Wisconsin - WhitewaterUW-Whitewater
University of Wisconsin - PlattevilleUW-Platteville
University of Wisconsin-WhitewaterUW-Whitewater
University of Wisconsin-PlattevilleUW-Platteville
Wisconsin - WhitewaterUW-Whitewater
Wisconsin - PlattevilleUW-Platteville
Wisconsin-WhitewaterUW-Whitewater
Wisconsin-PlattevilleUW-Platteville
Baraboo High SchoolBaraboo
Logan La Crosse Logan
DC Everest Schofield D.C. Everest
Northland LutheranKronenwetter Northland Lutheran
Pacelli CatholicStevens Point Pacelli
Lakeland UnionMinocqua Lakeland
Lourdes Academy Oshkosh Lourdes
Lourdes AcademyOshkosh Lourdes
Lourdes Oshkosh Lourdes
Kaukauna (Football Only) Kaukauna
UWSPUW-Stevens Point
/DLK
High School
MarquetteMilwaukee Marquette
Antioch CommunityAntioch (Ill.)
FreeportFreeport (Ill.)
DavenportDavenport (Iowa)
Niles NorthSkokie (Ill.) Niles North
EdgewoodMadison Edgewood
Madison Madison EdgewoodMadison Edgewood
ParkerJanesville Parker
Janesville Janesville ParkerJanesville Parker
Adams/FrieAdams-Frie
University of Wisconsin (Shell)
University of Wisconsin Shell
Twining ParkTwining Park Monroe
Honey Creek ParkHoney Creek Park Monroe
Jack Young MSJack Young Middle School Baraboo
ArrowheadHartland Arrowhead
Hartland Hartland ArrowheadHartland Arrowhead
Westby AreaWestby
Heights MS/HSHeights
NicoletGlendale Nicolet
Glendale Glendale NicoletGlendale Nicolet
Kettle MoraineWales Kettle Moraine
Wales Kettle Moraine LutheranJackson Kettle Moraine Lutheran
Central La Crosse Central
Aquinas La Crosse Aquinas
HS
BadgerLake Geneva Badger
Verona AreaVerona
Wilmot UnionWilmot
Reedsburg AreaReedsburg
Verona - Stampfl FieldStampfl Field Verona
Beaver Dam - Prairie View Elementary SchoolPrairie View Elementary School Beaver Dam
Reedsburg - Nishan Memorial ParkNishan Park Reedsburg
Luther PrepWatertown Luther Prep
Watertown Watertown Luther PrepWatertown Luther Prep
Southwestern/Benton/Cuba CitySouthwestern co-op
Easton Valley CommunityEaston Valley
Easton ValleyPreston (Iowa) Easton Valley
Notre Dame De La Baie AcademyGreen Bay Notre Dame
Zumbrota-MazeppaZumbrota-Mazeppa (Iowa)
School District of SomersetSomerset
Hayward Community SchoolsHayward
Barron Area SchoolsBarron
Stratford - WIStratford
Pec/ArgylePecatonica/Argyle
RegisEau Claire Regis
Eau Claire Eau Claire RegisEau Claire Regis
St. Mary’s SpringsFond du Lac St. Mary’s Springs
Fond du Lac Fond du Lac St. Mary’s SpringsFond du Lac St. Mary’s Springs
EscanabaEscanaba (Mich.)
MenomineeMenominee (Mich.)
GladstoneGladstone (Mich.)
Byron Public SchoolsByron (Ill.)
Laona-WabenoLaona/Wabeno
Mishicot 2015-16Mishicot
Northland PinesEagle River Northland Pines
Area Schools
School District of
School District
Community Schools
Zumbrota-MazeppaZumbrota-Mazeppa (Minn.)
Goodman-Pembine PatriotsGoodman-Pembine
Oconto Falls PanthersOconto Falls
Saint Mary SpringsFond du Lac St. Mary Springs
SaintSt.
(WI)
BeckerBecker (Minn.)
West Bend East SunsWest Bend East
LaCrosseLa Crosse
Ft. AtkinsonFort Atkinson
Stevens Point AreaStevens Point
HomesteadMequon Homestead
Mequon Mequon HomesteadMequon Homestead
Lake ForestLake Forest (Ill.)
Belvidere NorthBelvidere (Ill.) North
Milwaukee School of LanguagesMilwaukee Languages
Genoa KinstonGenoa (Ill.) Kinston
DC D.C.
Burlington Area School DistrictBurlington
Shawano CommunityShawano
Harold S. VincentMilwaukee Vincent
SalamMilwaukee Salam
Milwaukee Milwaukee SalamMilwaukee Salam
DominicanWhitefish Bay Dominican
Whitefish Bay Whitefish Bay DominicanWhitefish Bay Dominican
Jones ParkJones Park Fort Atkinson
Baraboo - Pierce Park FieldPierce Park Baraboo
Taylor Prairie Elementary SchoolTaylor Prairie Elementary School Cottage Grove
Reddan Soccer ParkReddan Soccer Park Verona
Breese Stevens Field MadisonBreese Stevens Field
Coopco-op
Tomahawk HatchetsTomahawk
De ForestDeForest
Hartford UnionHartford
The Prairie SchoolRacine Prairie
The PrairieRacine Prairie
Verona High SchoolVerona
KanelandMaple Park (Ill.) Kaneland
Woyahn ComplexWoyahn Complex Waukesha
Complex Wisconsin Dells WI 1770 HWY 13 Wisconsin Dells 53965 WI United StatesComplex Wisconsin Dells
Big FootWalworth Big Foot
Walworth Walworth Big FootWalworth Big Foot
TurnerBeloit Turner
Beloit Beloit TurnerBeloit Turner
La Crosse:Lacrosse:
McfarlandMcFarland
Cottage Grove - Fireman’s ParkFireman’s Park Cottage Grove
Brandt Quirk Park-WatertownBrandt Quirk Park Watertown
Goodman Pitch-East Madison CollegeGoodman Pitch Madison Area Technical College
Goodman Pitch-West Madison CollegeGoodman Pitch Madison Area Technical College
Goodman Pitch-North Madison CollegeGoodman Pitch Madison Area Technical College
Goodman Pitch-South Madison CollegeGoodman Pitch Madison Area Technical College
Whitman Middle SchoolWhitman Middle School Wauwatosa
Almond BancroftAlmond-Bancroft
Westfield - WIWestfield
Columbus Catholic SchoolsMarshfield Columbus
St CroixSt. Croix
Hamilton Sussex Hamilton
Catholic CentralBurlington Catholic Central
Burlington Burlington Catholic CentralBurlington Catholic Central
StewartvilleStewartville (Minn.)
TBDTBA
WoodstockWoodstock (Ill.)
Rio Community School DistrictRio
Warren Jr/SrWarren (Ill.)
Multiple Schools
Blackhawk Country Club - MadisonBlackhawk CC Madison
Country ClubCC
Golf ClubGC
Golf CourseGC
Springs AcademySprings
Wisconsin Dells LocationWisconsin Dells
Fischer Field - Jefferson WIFischer Field Jefferson
Fischer Field - JeffersonFischer Field Jefferson
The Oaks GCThe Oaks GC Cottage Grove
Wayland AcademyBeaver Dam Wayland
Park Cottage Grove (Minn.) Park
White Bear Lake AreaWhite Bear Lake (Minn.)
Marquette UniversityMilwaukee Marquette
Milwaukee Milwaukee MarquetteMilwaukee Marquette
Plymouth School DistrictPlymouth
East RidgeWoodbury (Minn.) East Ridge
Manitowoc LincolnManitowoc
Kettle Moraine LutheranJackson Kettle Moraine Lutheran
Jackson Jackson Kettle Moraine LutheranJackson Kettle Moraine Lutheran
Eastside LancersMadison Eastside
Regina Dominican (Wilmette)Wilmette (Ill.) Regina Dominican
Central Wisc. ChristianWaupun Central Wis. Christian
Central Wisconsin ChristianWaupun Central Wis. Christian
Home School EaglesSoutheast Wisconsin Christian Homeschool
Catholic MemorialWaukesha Catholic Memorial
Waukesha Waukesha Catholic MemorialWaukesha Catholic Memorial
Carroll UniversityCarroll University Waukesha
OrangevilleOrangeville (Ill.)
Pearl CityPearl City (Ill.)
Allis Nathan HaleAllis Hale
Schilberg ParkSchilberg Park Milton
Glen Erin GCGlen Erin GC Janesville
Racine SaintRacine St.
CatherinesCatherine’s
Sheboygan Area LutheranSheboygan Lutheran
Geneva National GCGeneva National GC Lake Geneva
Veterans Memorial Field (Portage)Veterans Memorial Field Portage
Wanaki GCWanaki GC Lisbon
Burlington Notre DameBurlington (Iowa) Notre Dame
DeSotoDe Soto
University LakeHartland University Lake
Hartland Hartland University LakeHartland University Lake
- Hockey Rink
Middleton - Breitenbach StadiumBreitenbach Stadium Middleton
Monterey Stadium JanesvilleMonterey Stadium
Tomah La Grange ElementaryLa Grange Elementary School Tomah
Prairie View Elementary School - Beaver DamPrairie View Elementar School Beaver Dam
Mary Rountree Evans FieldMary Rountree Evans Field Baraboo
Badger High SchoolLake Geneva Badger
Franklin Elementary
ParkviewOrfordville Parkview
Orfordville Orfordville ParkviewOrfordville Parkview
St. John’s Northwestern Military AcademyDelafield St. John’s NW
Saint Thomas MoreMilwaukee St. Thomas More
XavierAppleton Xavier
Appleton Appleton XavierAppleton Xavier
Elkhart Lake GlenElkhart Lake-Glen
Winnebago Lutheran AcademyFond du Lac Winnebago Lutheran
Lakeland UnionMinocqua Lakeland
Gale Ettrick TrempealeauGale-Ettrick-Trempealeau
Pius XIMilwaukee Pius XI
Milwaukee Milwaukee Pius XIMilwaukee Pius XI
Racine Case High SchoolRacine Case
Racine Horlick High SchoolRacine Horlick
Sauk Prairie - Sauk City Jaycee Baseball ParkJaycee Baseball Park Sauk City
Fox Valley LutheranAppleton Fox Valley Lutheran
AssumptionWis. Rapids Assumption
Wis. Rapids Wis. Rapids AssumptionWis. Rapids Assumption
Wisconsin Rapids Wisconsin Rapids AssumptionWis. Rapids Assumption
Wisconsin Rapids AssumptionWis. Rapids Assumption
Coulee ChristianWest Salem Coulee Christian
Krueger HaskellKrueger-Haskell
LaFolletteLa Follette
De ForestDeForest
Bradley TechnicalBradley Tech
Racine Case High SchoolRacine Case
Racine Park High SchoolRacine Park
Living Word LutheranJackson Living Word Lutheran
C-FCCochrane-Fountain City
Riverside UniversityRiverside
MayoRochester (Minn.) Mayo
Divine Savior Holy AngelsMilwaukee Divine Savior Holy Angels
St. Mary CatholicNeenah St. Mary
Abundant Life ChristianMadison Abundant Life

And here is part of the list as downloaded from one school website:

ActivityTypeDateTimeHome/HostOpponentLocation
Track: Boys VarsityInvitational03-19-205:00PMWisconsin Rapids LincolnPort Edwards, Reedsburg, RhinelanderWisconsin Rapids Lincoln High School
Track: Girls VarsityInvitational03-19-205:00PMWisconsin Rapids LincolnPort Edwards, Reedsburg, RhinelanderWisconsin Rapids Lincoln High School
Track: Boys VarsityInvitational03-20-20TBDWhitewaterStoughtonUW Whitewater Kachel Fieldhouse
Track: Girls VarsityInvitational03-20-20TBDUW WhitewaterStoughtonUW Whitewater Kachel Fieldhouse
Track: Boys VarsityInvitational03-20-204:00PMWhitewaterSauk PrairieUW Whitewater/DLK Kachel Fieldhouse
Track: Girls VarsityInvitational03-20-204:00PMWhitewaterSauk PrairieUW Whitewater/DLK Kachel Fieldhouse
Track: Girls VarsityInvitational03-20-204:00PMStevens Point (SPASH)Baraboo High School, Logan, Wausau East, Marshfield, Appleton West, Oshkosh West, DC Everest, Mosinee, Madison Memorial, Seymour, Wisconsin Dells, Ashwaubenon, Auburndale, Gillett, Green Bay West, Hortonville, Marathon, Northland Lutheran, Omro, Pacelli Catholic, Rosholt, Waupaca, Wild Rose, Lakeland Union, Lakeland Union, Lakeland Union, Lakeland Union, Reedsburg, Bay Port, Nekoosa, Iola-Scandinavia, Lourdes, Appleton North, Kaukauna (Football Only), Kimberly, NeenahUWSP Multi-Activity Center (MAC)
Track: Girls VarsityInvitational03-20-204:00PMStevens Point (SPASH)Baraboo High School, Logan, Wausau East, Marshfield, Appleton West, Oshkosh West, DC Everest, Mosinee, Madison Memorial, Seymour, Wisconsin Dells, Ashwaubenon, Auburndale, Gillett, Green Bay West, Hortonville, Marathon, Northland Lutheran, Omro, Pacelli Catholic, Rosholt, Waupaca, Wild Rose, Lakeland Union, Lakeland Union, Lakeland Union, Reedsburg, Bay Port, Nekoosa, Iola-Scandinavia, Lourdes, Appleton North, Kaukauna (Football Only), Kimberly, NeenahUWSP Multi-Activity Center (MAC)
Track: VarsityInvitational03-20-205:00PMLancasterViroqua, Lodi, Beloit Memorial, Oregon, Madison West, Platteville, Brodhead, Dodgeville, Madison East, McFarland, Middleton, Monroe, Mount Horeb, Prairie du Chien, Richland Center, River Valley, Waunakee, Antioch Community, Davenport West, Niles North, Freeport, Milton, Janesville ParkerUW-Platteville
Track: Boys VarsityInvitational03-20-205:00PMUW PlattevilleTBDUniversity of Wisconsin - Platteville
Track: Boys VarsityInvitational03-20-205:00PMLancasterViroqua, Lodi, Beloit Memorial, Oregon, Madison West, Platteville, Brodhead, Dodgeville, Madison East, McFarland, Middleton, Monroe, Mount Horeb, Prairie du Chien, Richland Center, River Valley, Waunakee, Antioch Community, Davenport West, Niles North, Freeport, Milton, Janesville ParkerUW-Platteville
Track: Boys VarsityInvitational03-20-205:00PMLancasterViroqua, Lodi, Beloit Memorial, Oregon, Madison West, Platteville, Brodhead, Dodgeville, Madison East, McFarland, Middleton, Monroe, Mount Horeb, Prairie du Chien, Richland Center, River Valley, Waunakee, Antioch Community, Davenport West, Niles North, Freeport, Milton, Janesville ParkerUW-Platteville
Track: Boys VarsityInvitational03-20-205:00PMLancasterAntioch Community, Beloit Memorial, Brodhead, Davenport West, Dodgeville, Freeport, Janesville Parker, Lodi, Madison East, Madison West, McFarland, Middleton, Milton, Monroe, Mount Horeb, Niles North, Oregon, Platteville, Prairie du Chien, Richland Center, River Valley, Viroqua, WaunakeeUW-Platteville
Track: Girls VarsityInvitational03-20-205:00PMUW PlattevilleTBDUniversity of Wisconsin - Platteville
Lacrosse: Boys VarsityTournament03-21-20TBDMarquetteDeForestMilwaukee
Track: Boys VarsityInvitational03-21-20TBDWhitewaterWatertownUW Whitewater/DLK Kachel Fieldhouse
Track: Boys VarsityInvitational03-21-20TBDWhitewaterBeaver DamUW-Whitewater
Track: Girls VarsityInvitational03-21-20TBDWhitewaterWatertownUW Whitewater/DLK Kachel Fieldhouse
Track: Girls VarsityInvitational03-21-20TBDWhitewaterBeaver DamUW-Whitewater
Track: Boys VarsityInvitational03-21-2010:00AMStevens Point (SPASH)Baraboo High School, Logan, Marshfield, Appleton West, DC Everest, Mosinee, Madison Memorial, Seymour, Wisconsin Dells, Ashwaubenon, Auburndale, Green Bay East, Green Bay West, Hortonville, Marathon, Northland Lutheran, Pacelli Catholic, Rosholt, Waupaca, Wild Rose, Lakeland Union, Reedsburg, Nekoosa, Iola-Scandinavia, Lourdes, Appleton North, Fond Du Lac, Kimberly, NeenahUWSP Multi-Activity Center (MAC)
Track: Boys VarsityInvitational03-21-2010:00AMStevens Point (SPASH)Baraboo High School, Logan, Marshfield, Appleton West, DC Everest, Mosinee, Madison Memorial, Seymour, Wisconsin Dells, Ashwaubenon, Auburndale, Green Bay East, Green Bay West, Hortonville, Marathon, Northland Lutheran, Pacelli Catholic, Rosholt, Waupaca, Wild Rose, Lakeland Union, Reedsburg, Nekoosa, Iola-Scandinavia, Lourdes, Appleton North, Fond Du Lac, Kimberly, NeenahUWSP Multi-Activity Center (MAC)
Track: Boys VarsityInvitational03-26-204:00PMOshkosh NorthDeForestUW-Oshkosh Kolf Sports Center
Track: Girls VarsityInvitational03-26-204:00PMOshkosh NorthDeForestUW-Oshkosh Kolf Sports Center
Softball: Girls VarsityGame03-26-204:30PMMadison WestMount HorebJefferson Middle School
Track: Girls VarsityMeet03-26-204:30PMMcFarlandWaunakeeMcFarland High School
Softball: Girls VarsityGame03-26-205:00PMWaunakeeMadison MemorialWaunakee High School
Softball: Girls VarsityGame03-27-204:00PMParkerFort AtkinsonJanesville Youth Sports Complex
Softball: Girls VarsityGame03-27-204:00PMJanesville CraigMiltonJanesville Youth Sports Complex
Track: Boys VarsityCity Meet03-27-204:00PMEdgewoodMadison East, Madison La Follette, Madison Memorial, Madison WestUniversity of Wisconsin (Shell)
Softball: Girls VarsityGame03-27-205:00PMAdams/FriendshipReedsburgAdams-Friendship High School
Softball: Girls VarsityGame03-27-206:00PMJanesville CraigFort AtkinsonJanesville Youth Sports Complex
Softball: Girls VarsityGame03-27-206:00PMParkerMiltonJanesville Youth Sports Complex
Track: Boys VarsityInvitational03-28-209:00AMShullsburg/BentonWestby Area, Parker, Oregon, Platteville, Black Hawk, Boscobel, Cassville, Darlington, Dodgeville, Fennimore, Monroe, Monticello, Lancaster, Belleville, Wisconsin Heights MS/HSUW-Platteville
Track: Boys VarsityInvitational03-28-209:00AMShullsburg/BentonWestby Area, Parker, Oregon, Platteville, Black Hawk, Boscobel, Cassville, Darlington, Dodgeville, Fennimore, Monroe, Monticello, Lancaster, Belleville, Wisconsin Heights MS/HSUW-Platteville
Track: Boys VarsityIndoor Relays03-28-2010:00AMWatertownArrowhead, Beaver Dam, Mayville, Slinger, Waukesha SouthWatertown High School
Track: Boys VarsityIndoor Relays03-28-2010:00AMWatertownArrowhead, Beaver Dam, Mayville, Slinger, Waukesha SouthWatertown High School
Track: Boys VarsityInvitational03-28-2010:00AMRiponJack Young MSRipon College
Track: Girls VarsityInvitational03-28-2010:00AMRiponBaraboo High SchoolRipon College
Track: Girls VarsityIndoor Relays03-28-2010:00AMWatertownBeaver Dam, Mayville, SlingerWatertown High School
Track: Girls VarsityIndoor Relays03-28-2010:00AMWatertownBeaver Dam, Mayville, SlingerWatertown High School
Softball: Girls VarsityDoubleheader03-28-2011:00AMMonroeLodiTwining Park
Soccer: Girls VarsityScrimmage03-28-201:00PMMonroeEvansvilleHoney Creek Park
Baseball: VarsityScrimmage03-28-202:00PMSun PrairieDeForestSun Prairie High School
Baseball: VarsityScrimmage03-28-202:00PMWaunakeeLa Crosse AquinasWoodside Sports Complex
Track: Boys VarsityInvitational03-28-203:00PMMaustonCentral, Logan, Black River Falls, Viroqua, West Salem, Holmen, Aquinas, Onalaska, Adams/Friendship, Neillsville, Nekoosa, ReedsburgUniversity of Wisconsin-La Crosse
Golf: Boys Varsity1st day of practice03-30-20TBDMonona Grove
Tennis: Boys Varsity1st day of practice03-30-20TBDMonona Grove
Tennis: Boys Varsity1st day of practice03-30-204:15PMSauk Prairie
Softball: Girls VarsityGame03-30-204:30PMBadgerFort AtkinsonBadger High School
Softball: Girls VarsityGame03-30-205:00PMMonroeBrodheadMonroe Twining Park
Track: Boys VarsityInvitational03-31-20TBDRiponStoughtonRipon College
Track: Girls VarsityInvitational03-31-20TBDRiponStoughtonRipon College
Track: Boys VarsityInvitational03-31-204:00PMPortageColumbus, Wisconsin DellsPortage High School
Track: Girls VarsityInvitational03-31-204:00PMPortageColumbus, Wisconsin DellsPortage High School
Baseball: VarsityScrimmage03-31-204:15PMVerona AreaEdgewoodVerona - Stampfl Field
Baseball: VarsityGame03-31-204:30PMReedsburgWisconsin Dells HSReedsburg - Nishan Memorial Park
Baseball: VarsityGame03-31-204:30PMSun PrairieWaunakeeSun Prairie High School
Soccer: Girls VarsityScrimmage03-31-204:30PMBeaver DamHustisfordBeaver Dam - Prairie View Elementary School
Baseball: VarsityGame03-31-204:45PMEvansvilleMonroeEvansville High School
Baseball: VarsityGame03-31-205:00PMLodiBaraboo High SchoolDane Baseball Field
Baseball: VarsityGame03-31-205:00PMOregonBeloit MemorialOregon High School
Soccer: Girls VarsityGame03-31-205:00PMTomahReedsburgTomah Veteran
Softball: Girls VarsityGame03-31-205:00PMStoughtonFort AtkinsonStoughton High School
Softball: Girls VarsityGame03-31-205:00PMDeForestPortageDeForest High School
Softball: Girls VarsityGame03-31-205:00PMMiltonMonroeMilton High School
Softball: Girls VarsityGame03-31-205:00PMReedsburgBeaver DamReedsburg Area High School
Softball: Girls VarsityGame03-31-205:00PMSauk PrairieBaraboo High SchoolSauk Prairie High School
Softball: Girls VarsityGame03-31-205:00PMWaunakeeOregonWaunakee High School
Softball: Girls VarsityGame03-31-205:00PMWaunakeeOregonWaunakee High School
Softball: Girls VarsityGame03-31-205:00PMMonona GroveOregonTaylor Prairie Elementary School
Track: VarsityInvitational03-31-205:00PMLancasterPlatteville, Argyle, Boscobel, Brodhead, Cuba City, Darlington, Fennimore, Monona Grove, Monroe, Mount Horeb, Prairie du Chien, River Ridge, Belleville, Shullsburg/BentonUW-Platteville
Track: Boys VarsityInvitational03-31-205:00PMLancasterPlatteville, Argyle, Boscobel, Brodhead, Cuba City, Darlington, Fennimore, Monona Grove, Monroe, Mount Horeb, Prairie du Chien, River Ridge, Belleville, Shullsburg/BentonUW-Platteville
Baseball: VarsityGame03-31-206:30PMPortagePoynettePortage High School
Soccer: Girls VarsityMatch03-31-206:45PMJeffersonFort AtkinsonJefferson High School
Soccer: Girls VarsityMatch03-31-207:00PMMonroeCambridgeMonroe High School
Soccer: Girls VarsityMatch03-31-207:00PMSauk PrairiePlattevilleSauk Prairie High School
Baseball: VarsityGame04-02-20TBDBeloit MemorialStoughtonBeloit Telfer Park
Soccer: Girls VarsityBadger Challenge04-02-20TBDBadger NorthEdgewood, Fort Atkinson, Milton, Monona Grove, Monroe, Oregon, Stoughton, WatertownTBA
Track: Girls VarsityInvitational04-02-20TBDLancasterStoughtonUW Platteville
Tennis: Boys VarsityScrimmage04-02-204:00PMWatertownLuther PrepBrandt Quirk Park-Watertown
Baseball: VarsityGame04-02-204:30PMReedsburgAdams/FriendshipReedsburg - Nishan Memorial Park
Baseball: VarsityGame04-02-204:30PMMiltonVerona AreaSchilberg Park
Baseball: VarsityGame04-02-205:00PMWisconsin DellsSauk PrairieWoodside Sports Complex - Wisconsin Dells Location
Baseball: VarsityGame04-02-205:00PMMarshallEdgewoodMarshall High School
Baseball: VarsityGame04-02-205:00PMBaraboo High SchoolOnalaskaMary Rountree Evans Field
Baseball: VarsityGame04-02-205:00PMDeForestColumbusDeForest High School
Soccer: Girls VarsityBadger Challenge04-02-205:00PMReedsburgFort AtkinsonReedsburg Area High School
Soccer: Girls VarsityBadger Challenge04-02-205:00PMBeaver DamBadger SouthPrairie View Elementary School - Beaver Dam
Soccer: Girls VarsityBadger Challenge04-02-205:00PMMount HorebWatertownMount Horeb High School
Softball: Girls VarsityGame04-02-205:00PMReedsburgSauk PrairieReedsburg Area High School
Softball: Girls VarsityGame04-02-205:00PMPortageWaunakeePortage High School
Softball: Girls VarsityGame04-02-205:00PMDeForestMount HorebDeForest High School
Softball: Girls VarsityGame04-02-205:00PMMonroeWatertownMonroe High School
Softball: Girls VarsityGame04-02-205:00PMFort AtkinsonOregonFort Atkinson High School
Softball: Girls VarsityGame04-02-205:00PMMiltonEdgewoodMilton High School
Softball: Girls VarsityGame04-02-205:00PMBaraboo High SchoolBeaver DamBaraboo - Pierce Park Field
Softball: Girls VarsityGame04-02-205:00PMMonona GroveStoughtonTaylor Prairie Elementary School
Track: Boys VarsityInvitational04-02-205:00PMLancasterWestby Area, Stoughton, Oregon, Belmont, Cassville, Evansville, River Ridge, Riverdale, Southwestern/Benton/Cuba City, Deerfield, Iowa-Grant, Potosi, Albany, Wisconsin Heights MS/HS, Easton Valley Community, Easton ValleyUW-Platteville
Track: Girls VarsityInvitational04-02-205:00PMLancasterWestby Area, Stoughton, Oregon, Belmont, Cassville, Evansville, River Ridge, Riverdale, Southwestern/Benton/Cuba City, Deerfield, Iowa-Grant, Potosi, Albany, Wisconsin Heights MS/HS, Easton Valley Community, Easton ValleyUW-Platteville
Soccer: Girls VarsityBadger Challenge04-02-207:00PMWaunakeeBadger SouthWaunakee High School
Soccer: Girls VarsityBadger Challenge04-02-207:00PMDeForestMonona GroveGoodman Pitch-East, Madison College
Soccer: Girls VarsityBadger Challenge04-02-207:00PMSauk PrairieEdgewoodSauk Prairie High School
Soccer: Girls VarsityBadger Challenge04-02-207:00PMPortageMiltonPoynette High School
Tennis: Boys VarsityInvitational04-03-2011:00AMUniversity School of MilwaukeeBrookfield Academy, Kohler, Shorewood, Notre Dame De La Baie Academy, EdgewoodPleasant Valley Tennis
Tennis: Boys VarsityInvitational04-03-201:00PMMukwonagoDeForestMukwonago High School
Baseball: VarsityTournament04-03-202:00PMAmherstAmery, Eau Claire North, New Richmond, Eau Claire Memorial, Zumbrota-Mazeppa, Viroqua, School District of Somerset, Hayward Community Schools, Bloomer, Stoughton, Barron Area Schools, Spooner, Stratford - WI, Loyal, Boyceville, Cumberland, Gibraltar, Greenwood, Ladysmith, Mauston, Mishicot 2015-16, North Fond du Lac, Northland Pines, Pec/Argyle, Prairie du Chien, Regis, Laona-Wabeno, Gladstone, St. Mary's Springs, Menominee, EscanabaWoodside Sports Complex
Softball: Girls VarsityTournament04-03-202:00PMAdams/FriendshipBloomer, Cumberland, Menomonie, WaunakeeWoodside Sports Complex Wisconsin Dells, WI, 1770 HWY 13 , Wisconsin Dells 53965, WI United States
Softball: Girls VarsityTournament04-03-202:00PMAmherstByron Public Schools, New Richmond, Eau Claire Memorial, Zumbrota-Mazeppa, Milton, West Bend East Suns, Onalaska, Antigo, Hayward Community Schools, Bloomer, Loyal, Boyceville, Brillion, Cumberland, Elcho, Florence, Goodman-Pembine Patriots, Mishicot 2015-16, Northland Pines, Northwood, Oconto Falls Panthers, Phillips, St Croix Central, Laona-Wabeno, Waunakee, Becker, Saint Mary Springs, Menominee, Elmwood/Plum City (WI)Woodside Sports Complex
Softball: Girls VarsityTournament04-03-202:00PMAmherstByron Public Schools, New Richmond, Eau Claire Memorial, Zumbrota-Mazeppa, Milton, West Bend East, Onalaska, Antigo, Hayward Community Schools, Bloomer, Loyal, Boyceville, Brillion, Cumberland, Elcho, Florence, Goodman-Pembine Patriots, Mishicot 2015-16, Northland Pines, Northwood, Oconto Falls Panthers, Phillips, St Croix Central, Laona-Wabeno, Waunakee, Becker, Saint Mary Springs, Menominee, Elmwood/Plum City (WI)Woodside Sports Complex Wisconsin Dells, WI, 1770 HWY 13 , Wisconsin Dells 53965, WI United States
Softball: Girls VarsityDoubleheader04-03-203:30PMDeForestLa Crosse CentralDeForest High School
Track: Girls VarsityInvitational04-03-204:00PMMarshallCambridge, Columbus, Deerfield, Lake Mills, Lakeside Lutheran, Madison Country Day, Monona Grove, Pardeeville, Parkview, Rio Community School District, St. John's Northwestern Military Academy, Watertown Luther Prep, Westfield - WIMarshall High School
Tennis: Boys VarsityGame04-03-204:15PMReedsburgViroquaReedsburg Area High School
Track: Boys VarsityQuad04-03-204:15PMMount HorebDodgeville, Monroe, StoughtonMount Horeb High School
Track: Boys VarsityQuad04-03-204:15PMMount HorebDodgeville, Monroe, StoughtonMount Horeb High School
Track: Boys VarsityQuad04-03-204:15PMMount HorebDodgeville, Monroe, StoughtonMount Horeb High School
Track: Girls VarsityQuad04-03-204:15PMMount HorebDodgeville, Monroe, StoughtonMount Horeb High School
Track: Girls VarsityQuad04-03-204:15PMMount HorebDodgeville, Monroe, StoughtonMount Horeb High School
Track: Girls VarsityQuad04-03-204:15PMMount HorebDodgeville, Monroe, StoughtonMount Horeb High School
Baseball: VarsityGame04-03-204:30PMElkhornFort AtkinsonElkhorn Area High School
Baseball: VarsityGame04-03-205:00PMSauk PrairieLodiSauk Prairie - Sauk City Jaycee Baseball Park
Baseball: VarsityGame04-03-205:00PMRiver ValleyReedsburgRiver Valley High School
Baseball: VarsityGame04-03-205:00PMEdgewoodCambridgeEdgewood High School
Baseball: VarsityGame04-03-205:00PMBaraboo High SchoolAdams/FriendshipMary Rountree Evans Field
Baseball: VarsityGame04-03-205:00PMWatertownPulaskiWashington Park - Watertown
Baseball: VarsityGame04-03-205:00PMOshkosh NorthBeaver DamOshkosh North High School
Soccer: Girls VarsityScrimmage04-03-205:00PMSun PrairieStoughtonSun Prairie High School
Softball: Girls VarsityGame04-03-205:00PMLodiSauk PrairieLodi High School
Softball: Girls VarsityGame04-03-205:00PMReedsburgRiver ValleyReedsburg - Nishan Memorial Park
Softball: Girls VarsityGame04-03-205:00PMStoughtonJanesville CraigStoughton High School
Softball: Girls VarsityGame04-03-205:00PMFort AtkinsonJeffersonFort Atkinson High School
Softball: Girls VarsityTournament04-04-20TBDAmherstMiltonWoodside Sports Complex Wisconsin Dells, WI, 1770 HWY 13 , Wisconsin Dells 53965, WI United States
Track: Boys VarsityInvitational04-04-20TBDWhitewaterTBAUW Whitewater Kachel Fieldhouse
Track: Boys VarsityInvitational04-04-20TBDWhitewaterBeaver DamUW-Whitewater
Track: Boys VarsityInvitational04-04-20TBDWhitewaterWatertownUW Whitewater/DLK Kachel Fieldhouse
Track: Boys VarsityMeet04-04-20TBDWhitewaterWaunakeeUW Whitewater Kachel Fieldhouse
Track: Boys VarsityGame04-04-20TBDMadison WestBaraboo High School, Logan, Oak Creek, West Bend East, Racine Case High School, Elkhorn, Aquinas, Kettle Moraine, Fond du Lac, Neenah, Appleton East, DC Everest, Slinger, Milwaukee Hamilton, Stevens Point Area, Beloit Memorial, Madison Memorial, Wauwatosa East, Verona Area, Platteville, Grafton, Homestead, Madison East, Madison La Follette, Menomonee Falls, Middleton, Milwaukee School Of Languages, Monona Grove, Monroe, Port Washington, Waukesha North, Whitefish Bay, Columbus, Marquette, Genoa Kinston, Lake Forest, Belvidere North, Milwaukee Madison, Wilmot Union, South Milwaukee, Milwaukee Lutheran, Edgewood, Milwaukee Washington, Brookfield East, Milwaukee KingUniversity of Wisconsin (Shell)
Track: Boys VarsityGame04-04-20TBDMadison WestBaraboo High School, Logan, Oak Creek, West Bend East, Racine Case High School, Elkhorn, Aquinas, Kettle Moraine, Fond du Lac, Neenah, Appleton East, DC Everest, Slinger, Milwaukee Hamilton, Stevens Point Area, Beloit Memorial, Madison Memorial, Wauwatosa East, Verona Area High School, Platteville, Grafton, Homestead, Madison East, Madison La Follette, Menomonee Falls, Middleton, Milwaukee School Of Languages, Monona Grove, Monroe, Port Washington, Waukesha North, Whitefish Bay, Columbus, Marquette, Genoa Kinston, Lake Forest, Belvidere North, Milwaukee Madison, Wilmot Union, South Milwaukee, Milwaukee Lutheran, Edgewood, Milwaukee Washington, Brookfield East, Milwaukee KingUniversity of Wisconsin (Shell)
Track: Boys VarsityGame04-04-20TBDMadison WestBaraboo High School, Logan, Oak Creek, West Bend East, Racine Case High School, Elkhorn, Aquinas, Kettle Moraine, Fond du Lac, Neenah, Appleton East, DC Everest, Slinger, Milwaukee Hamilton, Stevens Point Area, Beloit Memorial, Madison Memorial, Wauwatosa East, Verona Area, Platteville, Grafton, Homestead, Madison East, Madison La Follette, Menomonee Falls, Middleton, Milwaukee School Of Languages, Monona Grove, Monroe, Port Washington, Waukesha North, Whitefish Bay, Columbus, Marquette, Genoa Kinston, Lake Forest, Belvidere North, Milwaukee Madison, Wilmot Union, South Milwaukee, Milwaukee Lutheran, Edgewood, Milwaukee Washington, Brookfield East, Milwaukee KingUniversity of Wisconsin (Shell)
Track: Boys VarsityGame04-04-20TBDMadison WestBaraboo High School, Logan, Oak Creek, West Bend East, Racine Case High School, Elkhorn, Aquinas, Kettle Moraine, Fond du Lac, Neenah, Appleton East, DC Everest, Slinger, Milwaukee Hamilton, Stevens Point Area, Beloit Memorial, Madison Memorial, Wauwatosa East, Verona Area, Platteville, Grafton, Homestead, Madison East, Madison La Follette, Menomonee Falls, Middleton, Milwaukee School Of Languages, Monona Grove, Monroe, Port Washington, Waukesha North, Whitefish Bay, Columbus, Marquette, Genoa Kinston, Lake Forest, Belvidere North, Milwaukee Madison, Wilmot Union, South Milwaukee, Milwaukee Lutheran, Edgewood, Milwaukee Washington, Brookfield East, Milwaukee KingUniversity of Wisconsin (Shell)
Track: Girls VarsityInvitational04-04-20TBDWhitewaterBeaver DamUW-Whitewater
Track: Girls VarsityInvitational04-04-20TBDWhitewaterWatertownUW Whitewater/DLK Kachel Fieldhouse
Track: Girls VarsityMeet04-04-20TBDWhitewaterWaunakeeUW Whitewater Kachel Fieldhouse
Track: Girls VarsityInvitational04-04-20TBDWhitewaterStoughtonUW Whitewater Kachel Fieldhouse
Tennis: Boys VarsityInvitational04-04-209:00AMMukwonagoDeForestMukwonago High School
Tennis: Boys VarsityInvitational04-04-209:00AMOshkosh NorthBay Port, Muskego, Oshkosh West, Stevens Point (SPASH), WatertownOshkosh North High School
Tennis: Boys VarsityInvitational04-04-209:00AMParkerBurlington Area School District, Union Grove, Fort Atkinson, Janesville Craig, Madison East, MonroeJanesville Parker High School
Tennis: Boys VarsityInvitational04-04-209:00AMParkerBurlington Area School District, Union Grove, Fort Atkinson, Janesville Craig, Madison East, MonroeJanesville Parker High School
Baseball: VarsityGame04-04-209:30AMFort AtkinsonLake MillsJones Park
Thank you.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Here you go...
Be careful when using Find And Replace. because it can replace many words that you don't want to be replaced. Like in your list of mistakes and correction I can see HS replacing with a blank value and DC replacing D.C. In this case, excel will replace HS and DC appearing in any word as well e.g. school name ********hs or ******hs** will become **********.
And if you check mark Match entire cell contents then only matching entire cells values will be replace not all cell containing that value.
I hope you can understand me with my bad English :)
If you have questions. Feel free to ask!

Click here to download the file...

VBA Code:
Sub Find_Replace()
Dim Mistake As Variant
Dim Correction As Variant
Dim Row As Integer
Dim LR As Integer

LR = Workbooks("Automated Find and Replace").Sheets("Corrections List").Cells(Rows.Count, "A").End(xlUp).Row

For FnR = 2 To LR
    Mistake = Workbooks("Automated Find And Replace").Sheets("Corrections List").Range("A" & FnR)
    Correction = Workbooks("Automated Find And Replace").Sheets("Corrections List").Range("B" & FnR)
    Workbooks("Automated Find And Replace").Sheets("Raw Data").Select
'change xlPart to xlWhole if you need to replace only cells matching entire cell contents
    Cells.Replace What:=Mistake, Replacement:=Correction, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
Next
End Sub
 
Upvote 0
Hello, thanks so much for your help. I am excited to give it a try. When I do, I get this error message:
Run time error '9' - Subscript out of range.

When I hit Debug, it highlights this line:

Workbooks("Automated Find And Replace").Sheets("Raw Data").Select

I tried having two separate files, named according to the conventions listed in the macro you supplied, and also with the corrections files on different sheets on the same workbook as the one that needs the corrections. I know this is just a new user goofing up (me). What am I doing wrong? Thanks
 
Upvote 0
Hello, thanks so much for your help. I am excited to give it a try. When I do, I get this error message:
Run time error '9' - Subscript out of range.

When I hit Debug, it highlights this line:

Workbooks("Automated Find And Replace").Sheets("Raw Data").Select

I tried having two separate files, named according to the conventions listed in the macro you supplied, and also with the corrections files on different sheets on the same workbook as the one that needs the corrections. I know this is just a new user goofing up (me). What am I doing wrong? Thanks
You need to copy paste your data in Raw Data sheet and then update Corrections sheet and then run the macro on the same file. Actually in that line it selects the sheet where Find and Replace need to happen. I have specified this workbook and sheet strictly so macro don't update any other workbook or sheet if its opened.
Download the file as it is and then update sheets and then give it a try.
 
Upvote 0
Well goodness gracious, that works like a charm. You have no idea how much time this is going to save me when I do these schedule files three times per year! Probably 60 to 90 hours of drudgery! I will keep building the errors/corrections list to make it easier and easier down the road. Thank you very much! "fiberboysa" for a terrific solution.
 
Upvote 0
Now I have another question, which again would save me a ton of tedium.
If you look at the second example above, in the OPPONENT column, you see that some of the cells hold a lot of school names. These are for big track tournaments, etc., with a lot of schools. However, the school names are often NOT alphabetized.
I would like to have a macro that lets me:
1) Select the Opponent column
2) Run the macro
3) It goes into each cell and sorts the entries in that single cell alphabetically, sorted by commas
4) It plugs the alphabetized list into the same cell, wiping out the former, non-alphabetized entry
5) It then does the same for the next cell below in that column and repeats till it's done.

For example, here is a list from inside one of the cells:
Baraboo High School, Logan, Wausau East, Marshfield, Appleton West, Oshkosh West, DC Everest, Mosinee, Madison Memorial, Seymour, Wisconsin Dells, Ashwaubenon, Auburndale, Gillett, Green Bay West, Hortonville, Marathon, Northland Lutheran, Omro, Pacelli Catholic, Rosholt, Waupaca, Wild Rose, Lakeland Union, Lakeland Union, Lakeland Union, Reedsburg, Bay Port, Nekoosa, Iola-Scandinavia, Lourdes, Appleton North, Kaukauna (Football Only), Kimberly, Neenah

I would like that to be changed to alphabetical order:

Appleton North, Appleton West, Ashwaubenon, Auburndale, Baraboo High School, Bay Port, DC Everest, Gillett, Green Bay West, Hortonville, Iola-Scandinavia, Kaukauna (Football Only), Kimberly, Lakeland Union, Lakeland Union, Lakeland Union, Logan, Lourdes, Madison Memorial, Marathon, Marshfield, Mosinee, Neenah, Nekoosa, Northland Lutheran, Omro, Oshkosh West, Pacelli Catholic, Reedsburg, Rosholt, Seymour, Waupaca, Wausau East, Wild Rose, Wisconsin Dells

Currently I copy the contest, paste into Word and run a macro that turns each comma-space into an end-paragraph, sorts A to Z, then turns the paragraphs back into comma-space. It's faster, but it's still a matter of many hours to do thousands of these. A macro would be sweet!

Thank you. You've already made a huge impact, this would be frosting on the cake.
 
Upvote 0
I would like that to be changed to alphabetical order:
Hi, Hintysen
Try this:
Select the cell(s) or column then run the code.
Make sure that every item are separated by a comma & a space as in your example.
VBA Code:
Sub a1123877a()
'https://www.mrexcel.com/board/threads/automated-search-and-replace-of-a-long-list-of-items.1123877/
Dim r As Range
Dim ary
Dim dar As Object
Application.ScreenUpdating = False
For Each r In Intersect(Selection, ActiveSheet.UsedRange)
    
    ary = Split(r.Value, ", ")
    Set dar = CreateObject("System.Collections.ArrayList")
    For Each x In ary
        dar.Add CStr(x)
    Next
    dar.Sort
    r.Value = Join(dar.toarray(), ", ")

Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Akuini, hello!

I gave that a try with multiple files, but I always ran into trouble whether I had the whole column selected or if it was just one cell.
When I clicked on Debug, this line came up in yellow highlight.

Set dar = CreateObject("System.Collections.ArrayList")

Do I need to create an additional worksheet or column for this VB program to do its work? Right now, I just created the macro and clicked on the column (or cell) and ran the macro.

I did not try it on the file that I set up using fiberboysa's instructions, because I didn't want to blow up my master file which is now 90 percent cleaned up thanks to that macro!

As you can tell, I was busy covering basketball games or putting the newspaper out (or watching TV) when I should have been studying VB.

Thanks
 
Last edited:
Upvote 0
You can select a cell or multiple cell or a whole column.
But the the error on: Set dar = CreateObject("System.Collections.ArrayList")
shows there's a problem with creating "System.Collections.ArrayList".
Can you run this code & see if the same error occurs?
VBA Code:
Sub try555()
Set dar = CreateObject("System.Collections.ArrayList")
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

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