Excel - Copy paste values based on matching values

Question

I'm getting close to pulling out my hairs of frustration for the simple fact that I just don't get it. Have done a fair share of searching but did not stumble upon a similar question and aligning answer. Hoping that someone here can help me out on this one as I am not looking forward in doing this manually.

What I am trying to do is to enrich rows an existing worksheet in Excel with information from that same worksheet on other rows. The reference to enrich is that the value in column A needs to be matching. In that case the values of the other columns need to be filled with content from the row which contains the full data. I'll provide an example below, with illustrative data of course.

This is what I have now:

Column A    Column D       Column E      Column F

hash1          AA             BB            CC  
hash1            
hash1  
hash2          FF             GG            HH  
hash3          PP             YY            QQ  
hash3  
hash3

This is the result I am aiming for, where the changes/enrichment is marked in bold:

Column A       Column D       Column E      Column F

hash1          AA             BB            CC  
hash1          **AA             BB            CC**  
hash1          **AA             BB            CC**  
hash2          FF             GG            HH  
hash3          PP             YY            QQ  
hash3          **PP             YY            QQ**  
hash3          **PP             YY            QQ**

The rows are always in such an order that the first row contains the full data and the subsequent rows without data in the other columns follow after that. In case it's beneficial to know; this sheet contains approx. 20000 rows.

Thanks a lot in advance!!

Answer 1

An xlR1C1 style formula can return matching values from rows above.

Sub rtweqr()

    With Worksheets("sheet10")
        With .Range(.Cells(2, "A"), .Cells(Rows.Count, "A").End(xlUp).Offset(0, 6))
            .Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
                        Key2:=.Columns(6), Order2:=xlAscending, _
                        Orientation:=xlTopToBottom, Header:=xlYes
            .Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
                        Key2:=.Columns(4), Order2:=xlAscending, _
                        Key3:=.Columns(5), Order3:=xlAscending, _
                        Orientation:=xlTopToBottom, Header:=xlYes
        End With

        With .Range(.Cells(2, "E"), .Cells(Rows.Count, "A").End(xlUp).Offset(0, 6))
            On Error Resume Next
            .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
              "=index(r1c:r[-1]c, match(rc1, r1c1:r[-1]c1, 0))"
            On Error GoTo 0
            'optional reversion to formulas' returned values
            '.value=.value
        End With
    End With

End Sub