Sum Product - Macro - Unique value

[santanuKD]

Hi,

I have some data in the DATA work sheet. I need a macro which will create a summary based on the Current Status and Severity in the Summary sheet.

It should create a table where all unique Current status are listed and with the count of all based on the seveirity and final column with total. Sample is kept for you reference.

Note the Data sheet willl vary daily and hence the unique Current Status will vary to any number.

Many Thanks,
Santanu

[DonkeyOte]

Why not just use a Pivot Table ?

First create Dynamic Named Range for use as source of Pivot:

Please Login or Register  to view this content.

Then create the Pivot using _PTData as Source:
Set Severity as Row Label and Current Status as Column Label
Set Current Status as Data Field (set to COUNT)

You can use VBA if really necessary to automate the "refresh" of the Pivot Cache as and when the Data sheet is altered else just right click on Pivot and click Refresh as and when needed.

[santanuKD]

I am aware of Pivot Table but not aware how this "Dynamic Named Range for use as source of Pivot". It would be indeed great if you could edit my excel with what ever changes u have suggested, with that I could learn as well.

Many Thanks,
Santanu

[santanuKD]

We can use the below dynamic range for Pivot.

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

Thanks.