Create Pivot table with different count on Value column

I am new to pivot tables, and there something I can't figure out the way we can do.

I have a worksheet where there are several columns of categorical data. Each column has rating with same type of categorical data, i.e. each cell is filled with one of these five values:

Current input format
LOB Rep Manage Domain Process Technology Tools / Scripts App Knolwedge Quality
Typ1 Hari Karthik 2-3 2-3 2-3 3-4 2-3 4-5
Typ1 Hari Karthik 3-4 2-3 2-3 3-4 3-4 2-3
Typ1 Hari Karthik 2-3 2-3 2-3 2-3 3-4 2-3
Typ1 Hari Karthik 2-3 2-3 2-3 2-3 0-1 2-3
Typ1 Hari Raja 1-2 2-3 2-3 1-2 1-2 1-2
Typ1 Hari Raja 1-2 2-3 1-2 1-2 1-2 1-2
Typ1 Hari Raja 2-3 2-3 3-4 2-3 1-2 2-3
Typ1 Hari Guna 1-2 1-2 1-2 0-1 0-1 0-1



Required output format


LOB REP Manage Ranking - need to add this column Domain Process Technology Tools / Scripts App Knolwedge Quality
TYP1 Hari Karthi 0-1 0 0 0 0 1 0
TYP1 Hari Karthi 1-2 0 0 0 0 0 0
TYP1 Hari Karthi 2-3 3 4 4 2 1 3
TYP1 Hari Karthi 3-4 1 0 0 2 2 0
TYP1 Hari Karthi 4-5 0 0 0 0 0 1
TYP1 Hari Raj 0-1 0 0 0 0 0 0
TYP1 Hari Raj 1-2 2 0 1 2 3 2
TYP1 Hari Raj 2-3 1 3 1 1 0 1
TYP1 Hari Raj 3-4 0 0 1 0 0 0
TYP1 Hari Raj 4-5 0 0 0 0 0 0
TYP1 Hari Guna 0-1 0 0 0 1 1 1
TYP1 Hari Guna 1-2 1 1 1 0 0 0
TYP1 Hari Guna 2-3 0 0 0 0 0 0
TYP1 Hari Guna 3-4 0 0 0 0 0 0
TYP1 Hari Guna 4-5 0 0 0 0 0 0

I have tried different ways, but not able to figure out how to create the above output.

Is what I am trying to do possible? If so, could someone tell me how to do it?

Thanks,
Guna

This cannot be done with a pivot table. It can be done with VBA. I see that it is an XLSX file and assume you have Excel 2007 or later in contrast to what is in your header file. So this solution makes heavy use of Excel Tables and their properties such as a table’s ability to remember a formula and copy it down.

I created a small pivot table off the source data. This is to get a unique listing of names.

I loop through this list to get the information for columns A, B and C. Then I use a counter loop (K = 0 to 4) to create the 0-1, 1-2, etc. for Column D.

I fill in the Report Table with these values. The remainder of the cells contain COUNTIFS formulas such as: =COUNTIFS(Table_Data[LOB],[@LOB],Table_Data[Rep],[@REP],Table_Data[Manage],[@Manage],Table_Data[Domain],[@Ranking])

These formulas are remembered even when the report table is cleared and the get copied down automatically when rows are added to the table.