Return column header based on row header and cell value
I have the following grid of data:
---------Header 1 Header 2 Header 3 Header 4 Row 1 x x x Row 2 x x Row 3 x Row 4 x x x x
I then have a second sheet that looks like this:
Row 1 Row 2 Row 3 Row 4
I would like the second sheet to end up looking like this:
Row 1 Row 2 Row 3 Row 4 Header 1 Header 2 Header 3 Header 1 Header 3 Header 3 Header 2 Header 4 Header 3 . Header 4
Ignore that last period, I just used it to format it properly.
I've been playing with MATCH and INDEX for a couple hours and while I can get pieces of it, I can't seem to get it to all work together.
I use 'Header 1' and 'Row 1' as examples only. The actual data is text in Column A and Row 1, respectively. Also, since the source data will be modified, I'd prefer to have something that would automatically update the second sheet.
Here is one way to do it with a VBA function:
In the Developer Tab(*) Click on Visual Basic, then click on the "Insert" menu there and choose "Module" to insert a new module. Then paste in the Following code:
Option Explicit Public Function GetHeaderMatchingRow(RowText As String, _ SearchRange As Range, _ iHdrNo As Integer) As String Dim rng As Range Set rng = SearchRange Dim cel As Range 'Get the Row to scan Dim i As Long, rowOff As Long For i = 2 To rng.Rows.Count Set cel = rng.Cells(i, 1) If cel.Value = RowText Then rowOff = i Exit For End If Next i 'Now, scan horizontally for the iHdrNo'th non-blank cell Dim cnt As Integer For i = 2 To rng.Columns.Count Set cel = rng.Cells(rowOff, i) If Not CStr(cel.Value) = "" Then cnt = cnt + 1 If cnt = iHdrNo Then GetHeaderMatchingRow = rng.Cells(1, i).Value Exit Function End If End If Next i GetHeaderMatchingRow = "" End Function
Click on the "Debug" menu and select "Compile VBAProject".
Now go back to Excel and in your first sheet define a Named Range to cover all of your data in the grid. The Row names should be the first column in this range and the Header text should be the first row in it.
Now go to your second sheet and enter a formula like this in every output cell:
=GetHeaderMatchingRow(A$1, RowHeaderRange, 1)
Where the First parameter is the Row text that it will try to match in the first column of the range. I have "A$1" here because the in my test, my second sheet's column headers are also the Row-names in my first sheet, just like yours.
The second argument is the range to search (in this case, the Named Range we defined earlier), and the third argument is the count of the match that it is looking for (1st, 2nd, 3rd, etc.).
Note that the first and third parameters should change based on what column and row the output is for.
Does it have to use worksheet functions? It would be quite a bit simpler to create a macro to do it (I've made an example)
Edited the function to work with row headers in col a and column headers in row 1 and changed it to read from "Source" sheet and write the result to "Output" sheet
Public Sub Example() Dim Output As Worksheet Dim Sheet As Worksheet Dim Row As Integer Dim Column As Integer Set Sheet = ThisWorkbook.Worksheets("Source") Set Output = ThisWorkbook.Worksheets("Output") Output.Cells.Clear ' Since were going to rebuild the whole thing, just nuke it. For Row = Sheet.UsedRange.Rows(Sheet.UsedRange.Rows.Count).Row To 2 Step -1 Output.Cells(1, Row - 1).Value = Sheet.Cells(Row, 1).Value For Column = Sheet.UsedRange.Columns(Sheet.UsedRange.Columns.Count).Column To 1 Step -1 If Not IsEmpty(Sheet.Cells(Row, Column)) Then Sheet.Cells(1, Column).Copy Output.Cells(2, Row - 1).Insert xlShiftDown End If Next Column Next Row End Sub
I had a look at doing it with worksheet functions and as others have said its going to be pretty tricky to do it without some vba mixed in there.
If you add this to a new module then you can access it as a workbook function. (not that this is the best way to do it, just fancied having a go)
'Return The Column Header of the Nth Non-Blank Cell on Specified Row Public Function NonBlankByIndex(ByVal Row As Integer, ByVal Index As Integer) As Range Dim Sheet As Worksheet Dim Column As Integer Dim Result As Range Set Sheet = ThisWorkbook.Worksheets("Source") ' Change to your source sheet's name Set Result = Nothing Column = 2 ' Skip 1 as its the header Do If Column > Sheet.UsedRange.Columns(Sheet.UsedRange.Columns.Count).Column Then Exit Do End If If Sheet.Cells(Row, Column) = "" Then Column = Column + 1 Else If Index = 1 Then Set Result = Sheet.Cells(1, Column) Exit Do Else Column = Column + 1 Index = Index - 1 End If End If Loop Set NonBlankByIndex = Result End Function
If you are happy with blanks in the listing try this in sheet2!A2:
Just copy the formula over range A2:D5