Bounced around with RegEx a bit, and learned some, but ultimately decided that Left functions would be easier given the different types of noise in the team data
Process:
- Cleaned up winning team and losing team data, but searching for "(" and extracting all to the left
- Repeated above by searching for "["
- Split Winning Team and Losing Team through two select tools
- Combined all teams (stacked the two) with Union tool
- 3 streams for calculations
-- Count number of appearances per team (Grouped combined team list by team and counted instances)
-- Count number of wins per team (Grouped winning team list by team and counted instances)
-- Find and Replace tool to combine the columns of All teams, Count of appearances, and Count of wins
--- Calculated ratio of wins to appearances
--- Reformatted result to string: multiplied by 100, rounded to integer, converted to string and appended "%" (also had to replace "%" to 0% where team never won (i.e. wins was Null)
-- Concatenated winning years (Grouped combined team list by team and concatenated years
- Find and Replace tool to combine all data
- Select tool to remove unneeded fields and rename for output