Filter cells by row and then lookup. How do I do this?

[robmack]

Hi,

I have an audit evidence grid I'm maintaining that relates the audit reference (eg. 6.1.a, 6.1.b, 6.2.a, etc.) to the collateral (indicated as DOC-1, DOC-2, etc.). So, if I was to filter on a column, I'd see every collateral item that addresses any given audit reference. This is shown in the example I've attached.

What I need to do is the opposite - for a given collateral document, list all the audit references that collateral addresses - and put this into another sheet in the workbook. The sheet would have one row for each collateral document reference (eg. DOC-1, DOC-2, etc.) and subsequently a list of all the audit references which that document addresses.

Since the content of the first sheet is somewhat dynamic, I'd like to create the second sheet using a programatic method, like VBA or pivot table or formula. In essence, I'm looking to do a filter cells horizontally and then lookup the corresponding column header where-ever there is an entry in the cell. But, I'm not skilled enough in Excel to figure this out. Can someone help me please, and thank you?

[shank_mis]

Hi @robmack,

I have implemented the formula in working sheet attached herewith.

On a New Sheet:

=IFERROR(INDEX('Doc by Req.'!$C$1:$FQ$1,,SMALL(IF(('Doc by Req.'!$A$3:$A$6=$B$3)*('Doc by Req.'!$C$3:$FQ$6<>"")*(COLUMN('Doc by Req.'!$C$1:$FQ$1)-2)>0,COLUMN('Doc by Req.'!C1:FQ1)-2),COLUMN(A$1))),"")

where B3 is reference number. Drag the formula rightwards.

Please hit the "Add Reputation" button if you liked the answer.