Create Pivot Table When duplicate data is in two columns

[JohnQPublique]

I have spent a lot of time researching this topic and am coming here as a last resort. Thank you for reading.

A table is for loans and the reasons they were denied. The columns are as follows:

[TABLE="width: 415"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Loan#[/TD]
[TD]Narrative[/TD]
[TD]ReasonForDenial1[/TD]
[TD]ReasonForDenial2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD] xxx[/TD]
[TD]Reason A[/TD]
[TD]Reason B[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD] xxx[/TD]
[TD]Reason E[/TD]
[TD]Reason A[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD] xxx[/TD]
[TD]Reason D[/TD]
[TD]Reason B[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD] xxx[/TD]
[TD]Reason C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD] xxx[/TD]
[TD]Reason B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"] 1[/TD]
[TD] xxx[/TD]
[TD]Reason C[/TD]
[TD]Reason A[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD] xxx[/TD]
[TD]Reason B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD] xxx[/TD]
[TD]Reason A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD] xxx[/TD]
[TD]Reason A[/TD]
[TD]Reason E[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD] xxx[/TD]
[TD]Reason B[/TD]
[TD]Reason D[/TD]
[/TR]
</tbody>[/TABLE]

There is duplication-- loan numbers appear multiple times and the reasons for denial 1 and 2 are the same. The narratives need to be separate. There are multiple narratives for each loan and each narrative ties to one or two reasons for denial. The table is dynamic and information may be added or deleted depending on bosses review, so the pivot table needs to be dynamic. I am trying to come up with a pivot table the gives the following:

Loan# Reason A Reason B Reason C Reason D Reason E
1 2 1 1
2 1 1 1
etc...

How do I accomplish this??? I have PowerPivot built into and functioning in Excel 2013. Please help. Thank a lot.

JohnQ

 

[JohnQPublique]

Not sure how to edit my post...
The resulting table should be:
[TABLE="width: 588"]
<colgroup><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Loan#[/TD]
[TD]Reason A[/TD]
[TD]Reason B[/TD]
[TD]Reason C[/TD]
[TD]Reason D[/TD]
[TD]Reason E[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]
1

[/TD]
[/TR]
</tbody>[/TABLE]

 

[JohnQPublique]

Boy, sorry to add, but I am also trying to get a count of UNIQUE reasons for the denial in the last column. There doesn't seem to be a way to total the non-blank cells in a row in a pivot table. Can anyone help here also?

 

[SpillerBD]

What type of data populates the Narrative field?

 

[SpillerBD]

You need to Unpivot the data, which can be done with the PowerQuery(2013 ProPlus) or Get and Transform(Excel 2016) functions.
There are other solutions for unpivot too.
You can then use the Data Model to get to a unique count but you can't mix the count and unique counts elegantly. They're both available.

 

[JohnQPublique]

What type of data populates the Narrative field?

Spiller BD, the narrative section contains text. Sometimes many sentences explaining the issue at hand and why the loan was denied. Because each narrative has different issues, the narratives need to be separate, and thus the problem.

Thanks for the time. Please let me know any answers. I am researching the unpivot suggestion.