Need to show the Unique Value and related Count Based on Other Column

Team,

For Example I have two sheets in my file. I need to Create Final sheet as in the Third Table.

In Final Sheet I need to remove the duplicate and show in First Column and Number of FAIL in second Column and Number of PASS in Third Column.

Please Note that the logic should work automatically even if the Client add a New sheet or delete the Existing Sheet. Please help me on this.

Shee1

Table Name	Test Case	Test Result
ABC	ABC_Test Case 1	PASS
ABC	ABC_Test Case 2	FAIL
ABC	ABC_Test Case 3	FAIL
XYZ	XYZ_Test Case 1	FAIL
XYZ	XYZ_Test Case 2	FAIL
CDE	CDE_Test Case 1	PASS
CDE	CDE_Test Case 2	PASS

Sheet 2

Table Name	Test Case	Test Result
CCC	CCC_Test Case 1	PASS
CCC	CCC_Test Case 2	FAIL
DDD	DDD_Test Case 1	FAIL
DDD	DDD_Test Case 2	PASS
DDD	DDD_Test Case 3	FAIL
DDD	DDD_Test Case 4	FAIL

Final Sheet Should be created as below

Table Name	COUNT OF FAILS	COUNT OF PASS
ABC	2	1
XYZ	2	0
CDE	0	2
CCC	1	1
DDD	3	1

Thanks!
Kiran

Duplicate thread - different name/UserId/handle:

http://www.mrexcel.com/forum/excel-q...er-column.html

A message to forum cross posters.

Please read this:
http://www.excelguru.ca/node/7

Duplicate thread - different name/UserId/handle:

http://www.mrexcel.com/forum/excel-q...er-column.html

A message to forum cross posters.

Please read this:
http://www.excelguru.ca/node/7

Add the 2 sheets together manualy or with a macro.

After that it is a piece of cake with a pivot table.