Complicated Index / Database: If "column", Find "value", Return "row"

[jolyeu]

I need to develop a dynamic report that, given an analyst's initials, the report will return all of the projects that analyst is working on.


The source data is a staffing database where each project has an index number, and each project is assigned 1 or more analysts by initials (AVB, LHO, CLE, etc.). This enables us to rotate staffings on an ongoing basis and it is meant to be dynamic:

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Index[/TD]
[TD]Project[/TD]
[TD]Analyst 1[/TD]
[TD]Analyst 2[/TD]
[TD]Analyst 3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Company Research[/TD]
[TD]AVB[/TD]
[TD]CLE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Client Materials[/TD]
[TD]AVB[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Weekly Comps File[/TD]
[TD]CLE[/TD]
[TD]DRA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Quarterly Comps File[/TD]
[TD]AVB[/TD]
[TD]KSD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Internal Committee[/TD]
[TD]LHO[/TD]
[TD]CLE[/TD]
[TD]KSD[/TD]
[/TR]
</tbody>[/TABLE]



This database feeds the below table where each row represents the index number and each column represents the analyst (this is set up in this way to also be dynamic). The values in the table represent whether the analyst is staffed on each project, in an ordinal fashion.

[TABLE="class: cms_table_grid, width: 300"]
<tbody>[TR]
[TD]Index[/TD]
[TD]AVB[/TD]
[TD]LHO[/TD]
[TD]CLE[/TD]
[TD]KSD[/TD]
[TD]DRA[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]



Given analyst's initials as input, the report needs to return all of the projects that analyst is working on.

For example, if I want to run the analyst initials "AVB," the output would look something like:

[TABLE="class: cms_table_grid, width: 200"]
<tbody>[TR]
[TD]#[/TD]
[TD]AVB[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Company Research[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Client Materials[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Quarterly Comps File[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



If "KSD", it would look like:

[TABLE="class: cms_table_grid, width: 200"]
<tbody>[TR]
[TD]#[/TD]
[TD]KSD[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Quarterly Comps File[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Internal Committee[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


etc.

Last thing - I can't use <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help;">VBA</acronym> since email attachments with macros slow down our email servers.

Thanks in advance!