Update – I don’t think all the process below is required.

Just download the 404 – inlinks report from Screaming Frog

Copy the “Destination” column and paste into an new Excel tab/sheet and remove duplicates

In the first sheet, copy and paste the source column into column C

In the second sheet, do a vlookup using the first destination URL, and “lookup” in the first sheet – columns B and C, to return the relevant source URL

Copy the vlookup and Paste – values – into column A into the second sheet

You can also copy and paste the anchor text and location into column C

Follow this protocol, to produce a sheet you can send to devs etc, to remove 404s

• This will get rid of the site-wide 404s and some individual 404s

Run a crawl with Screaming Frog

Export the report –> Screaming Frog – Bulk Export  – Response Codes – Internal – Internal Client Error (4xxs)  (check 500s too)

In Excel – Copy and paste the “destination” URLs into a new sheet – into column A

Remove duplicates from the destination URLs that you’ve just copied into a new sheet

rename the colum – 404s Destination

• Copy and paste the Source URLs and the Anchor Text into a new sheet.

Paste Source URLs in column A, and Anchor Text into column C

In cell B1 type – ” | ”

In cell D1 – give the column the heading “Source | Anchor”

In cell D2 concatenate – =CONCATENATE(A2,$B$1,C2)

Drag the formula down.

You’ll now have the anchor text and the source URL together, so you can vlookup the destination (404) URL

• create a new sheet
• Copy and paste all of the source URLs | Anchor Text (from the concatentate formula – paste special -values only
• Copy & Paste Destination URLs from the original sheet into columns B and C in the new sheet you just made.

You need “destination” in column B and “Source | Anchor Text” in column C, as vlookup has to go left to right

• So you’ll have – 404s Destination – Destination – Source | Anchor Text

Name column D in the new sheet “Example Source URL & Anchor Text” and in cell D2 enter the lookup – VLOOKUP(B2,B:C,2,0) (put “equals sign” before the V. Drag the formula down

Copy column A and paste into a new sheet. Name the sheet “Final”.

Copy column D with the vlookup and paste values into column B in the “Final Spreadsheet”

In “final”, you should now have all the unique 404s and an example of a page that links to those 404s with the anchor text.

• You can use “text to columns” to seperate the source URLS and anchor text if you wish

• If you’re sending the spreadsheet onto a dev or someone to fix the 404s, you are probably best sending the full sheet with all the inlinks to the 404s, plus the one you’ve just made. It depends how they go about fixing the 404s.
  
  Once 404s have been fixed, rerun a crawl and recheck them.

look out for 404s that are classed as  HTTP Redirects in the “type” column – these don’t seem to have a unique source URL. You may have to search for the URL in the search box in Screaming Frog and click the “inlinks” tab to see original link to the non-secure http page

If you like, before you send off the report to someone, you can double check the “destination” URLs definitely are 404s, by pasting them into screaming frog in “list” mode