CODEWITHSUNDEEP
vbaexcel
Randy Hall
Randy Hall

Reputation: 8167

Create list from column-row matches

I have a table with the first row being column headers, and the first column being row labels. I have put TRUE in every cell where these match up.

I need to generate a list in a separate sheet for each column, containing only the matching row names where the cells are TRUE.

So if B13, B15, and C12 in sheet1 are TRUE, then sheet2 will show column B with 13 and 15 under it, and column C with 12 under it. Except using the labels I've input, instead of letters and numbers.

I haven't managed to wrap my head around where to start with this one, so any pointer in the right direction is appreciated.

I've tried creating a defined name for the column headers and row labels.

Upvotes: 0

Views: 99

Answers (1)

TheGuyThatDoesn'tKnowMuch
TheGuyThatDoesn'tKnowMuch

Reputation: 1250

I don't fully understand the layout of your table, but assuming it looks like the following example I tested, this VBA should work.

Sheet1 table format

enter image description here

Sheet2 output results

enter image description here

VBA

Sub test()

Dim i As Long
Dim j As Long
Dim lRow As Long
Dim lCol As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
lRow = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
lCol = ws1.Cells(1, ws1.Columns.Count).End(xlToLeft).Column

With ws1

    For j = 2 To lCol
    ws2.Cells(1, j).Value = .Cells(1, j).Value

        For i = 2 To lRow

            If .Cells(i, j).Value = True Then

                ws2.Cells(ws2.Rows.Count, j).End(xlUp).Offset(1, 0).Value = .Cells(i, 1).Value 'This will probably need to be changed to 'i' to represent the row number for your purposes

            End If

        Next i

    Next j

End With

End Sub

Upvotes: 3

Related Questions