Need some help from our local Excel gurus.

For a project at work, need to be able to filter across multiple tabs in Excel.

We are doing a deep dive review of our website (750+ pages) for broken links. Data will be broken into Excel sheets based on page type (topic, news, success story, etc.) Then within each sheet a row will have the page name, bad URL, link text, and updated URL.

If the same bad URL shows up on multiple pages, I want to be able to fix it across the board while I have the correct URL copied to my clipboard. Basically I want to be able to set a filter for LINK / and have that data row come up no matter if it is on Sheet 1, Sheet 2, or Sheet 5.

Everything I am seeing online says this takes VB code to accomplish, and I haven't written VB in 20+ years.

3 ...

Report Post

Posted by Korkstand

Member since Nov 2003

28996 posts

Posted on 8/12/24 at 1:49 pm to PJinAtl

Man, I don't want to tell you how to do your work, but if I had to use Excel for this I would quit.

I would use Python to crawl the site and spit out what you want. You can probably find an example already written that you can use.

Might also suggest rebuilding the site with a CMS that can manage broken links for you.

2 ...

Report Post

Posted by Lonnie Utah

Utah!

Member since Jul 2012

28912 posts

Posted on 8/12/24 at 2:19 pm to Korkstand

quote:
I would use Python to crawl the site and spit out what you want. You can probably find an example already written that you can use.

ChatGTP: Write a python script to crawl the site https://www.tigerdroppings.com and look for broken links

This post was edited on 8/12/24 at 2:20 pm

0 ...

Report Post

Posted by PJinAtl

Atlanta

Member since Nov 2007

13395 posts

Posted on 8/12/24 at 3:37 pm to Korkstand

The site is running in a very highly customized version of WordPress that handles our site as a stepchild of the main site. I can create/edit/delete pages on the site, but most of the higher level functions are handled by a dedicated team of devs/designers that are outside my purview.

There is a link checker at the page level, but not above that (or at least not one I can access).

My team lead wants the Excel file for both record-keeping but also to divvy up the job of tracking down the correct updated URLs where there are broken links.

0 ...

Report Post

Posted by low end

504/985/225/713

Member since May 2024

391 posts

Posted on 8/15/24 at 8:42 am to PJinAtl

quote:
Sub tFilter()

Dim badURL As Variant
Dim ws As Worksheet

badURL = InputBox("Paste the bad URL here")

For Each ws In ActiveWorkbook.Worksheets
ws.Range("B1").AutoFilter Field:=2, Criteria1:=badURL
Next

End Sub

This should work assuming the bad URL is in the second column of your table.

Just assign the macro to a button and, when you click it, a pop-up window will appear asking for the bad URL. Once you click OK, the filter will be set across all sheets.

Edit: If you need more (or different) functionality let me know. I have some time today.

This post was edited on 8/15/24 at 8:46 am

1 ...

Report Post

Posted by msutiger

Houston

Member since Jul 2008

70548 posts

Posted on 8/15/24 at 9:26 am to PJinAtl

Unless I’m misunderstanding the situation, couldn’t you combine the data of all sheets onto a single sheet using VSTACK and then filter that data set by the link column?

1 ...

Report Post

Posted by Chromdome35

Fast lane, behind a slow driver

Member since Nov 2010

7661 posts

Posted on 8/15/24 at 3:38 pm to msutiger

Combining it all on the same sheet is the easiest way I think. Add a column at the front that has the tab name for each row. Then when you filter, you can see what tabs the bad URL appears on.

This post was edited on 8/15/24 at 3:40 pm

2 ...

Report Post

Posted by PJinAtl

Atlanta

Member since Nov 2007

13395 posts

Posted on 8/16/24 at 11:43 am to low end