Can I create a special Pivot Table?

[HelpMeWithExcelPlease]

Hi all,

I'm hoping someone has an answer to this issue that I am am running in to. I am working with a not so eloquently designed spreadsheet to begin with, but hopefully that won't bog down what I am trying to do. Here is a simplified version:

Table 1:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Defect ID (Column A)
[/TD]
[TD="align: center"]State 1 (Column B)
[/TD]
[TD="align: center"]State 2 (Column C)
[/TD]
[TD="align: center"]State 3 (Column D)
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Texas[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]California [/TD]
[TD]Texas[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Colorado[/TD]
[TD]Maine[/TD]
[TD]Texas[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]New York[/TD]
[TD]Colorado[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Table 2:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]State Name (Column A)[/TD]
[TD]# of Defects (Column B)[/TD]
[/TR]
[TR]
[TD]Texas[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]California[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Colorado[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Maine[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


What formula do I need to put in column B of Table2 to be able to double-click on the cell and open up a filtered view of the rows that make up the count in column B, such as what you can do with a Pivot Table? Do I need to create some kind of special Pivot Table?

Any insight into this issue would be greatly appreciated, thank you in advance!