Search code examples
excelcellcolumnheaderrowheader

Make a cell equal to a concatenation of its column and row headers in Excel

I have a (large) table with row and column headers of the following format: table with row and column headers

I'd like to set the cells marked with 'x' to a concatenation of the column header and the row header, separated by a comma. For example, cell B2 should be set to "c1_HEADER, r1_HEADER".

Is there a formula I can use to achieve this? At least by clicking the 'x' marked cells and applying the formula? I'd hate to take the manual route all the way :/.

TIA.

Solution

  • If we start with:

    enter image description here

    running this macro:

    Sub luxation()
    Dim r As Range

    For Each r In Range("B2").CurrentRegion
        If r.Value = "x" Then
            r.Value = r.EntireColumn.Cells(1).Value & "," & r.EntireRow.Cells(1).Value
        End If
    Next r
End Sub

    will produce:

    enter image description here

    Note:

    in this macro Range("B2").CurrentRegion represents the block of cells that we are looping through. The variable r is a single-cell range that facilitates the loop.