Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I've got a list of imported broken links. I need to sort the list based on column C (the error code) so I can find all links with error 404. However, I need to know the corresponding parent page (column A) for all the links. The problem is that the parent page in column A is only listed once before all its corresponding links are listed below it in column B.

I assume I need to somehow copy down the values in column A until you reach the next URL, and then copy that down and so fourth for all of column A. Is there a way I can do this fairly easily? It's a huge spreadsheet so manually is out of the question.

Below is a screenshot of part of the spreadsheet:

enter image description here

share|improve this question
    
How many rows are in the entire spreadsheet? –  Austin ''Danger'' Powers Feb 22 at 2:03
    
There's 2202 rows in the spreadsheet –  user2721465 Feb 22 at 2:11

1 Answer 1

up vote 3 down vote accepted

Here's what I'd try, conceptually:

Insert another column B. In that column, first row, copy current A1. For the rest, use a conditional formula: =IF(A2="", B1, A2). If A is blank, copy the value above. If A is not blank, use it. This should give you the appropriate value from A on each row.

Copy that new column and paste values onto itself.

Do your sort, delete the rows with the A value that are otherwise empty.

share|improve this answer
    
That's pretty creative. –  Austin ''Danger'' Powers Feb 22 at 23:44
    
That's a useful pattern for many situations where you are taking data that is formatted for reading and want to make it a sortable/filterable table. "Paste Values" is a key feature to know in Excel. –  Dane Feb 26 at 20:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.