I have tried to create a spreadsheet which lets me know if the last trailing step of two URLs are matching. (In case you are wondering: It is for redirect mapping because when I have old URLs and match them to a new structure the last trail stays the same more often than not.)
The said spreadsheet is working more or less and can be found here: https://docs.google.com/spreadsheets/d/1m3E5NQYSUGe4Kxn4BrpcgWj4WhotKG88JCx93ER3U1c/edit?usp=sharing
I did the following:
- Pull old and new URLs in two separate sheets (oldcrumb/newcrumb)
- Split the URLs in these sheets into separate cells splitting at every "/"
- Compare the last filled Cell in a Row between the two sheets
Unfortunately my solution is somewhat clunky. For about 600 initial rows I need another 1200 rows because I use 2 different sheets and also use split formula to get hands on the last trail.
Also I have just implemented comparison for a certain range (Rows I to H in the split row sheets) of trailing position with several IF-conditions. If URLs are supershort or superlong, nothing will be displayed in the "Last trail matching?" row.
Also at some point I get a warning because I would reach the maximum amount of usable cells in Google Sheets (around 200000 or so?).
So is there any more elaborate/elegant way to do what I did in a super awkward and heavy loaded approach?