Pivot table group simliar values in column

P

projectile

Board Regular
Joined
Dec 14, 2007
Messages
193
Hi

Have a pivot table and want excel to stop grouping similar items , e.g.

Name ---Surname ---
Paul --- ---Jones
(Cell is blank) --- --- ------Smith <------ first name here is Paul, But excel shows as blank cell
Jack ---Williams



Instead I want the output to be

Paul --- ---Jones
Paul --- ------Smith
Jack ---Williams


Hope this make sense.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Projectile,

Only for Excel 2010:

1) Take a right click on the Name field in the PivotTable and choose Field settings.

2) Then activate the Layout & Print tab, and check the Repeat item labels.

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="FONT-WEIGHT: bold">Name</TD><TD style="FONT-WEIGHT: bold">Surname</TD><TD style="FONT-WEIGHT: bold">Sold</TD><TD style="TEXT-ALIGN: right"></TD><TD>Soma de Sold</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Name1</TD><TD>Surname1</TD><TD style="TEXT-ALIGN: right">7.549 </TD><TD style="TEXT-ALIGN: right"></TD><TD>Name</TD><TD>Surname</TD><TD>Total</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Name1</TD><TD>Surname2</TD><TD style="TEXT-ALIGN: right">1.877 </TD><TD style="TEXT-ALIGN: right"></TD><TD>Name1</TD><TD>Surname1</TD><TD style="TEXT-ALIGN: right">7.549</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Name2</TD><TD>Surname3</TD><TD style="TEXT-ALIGN: right">5.862 </TD><TD style="TEXT-ALIGN: right"></TD><TD>Name1</TD><TD>Surname2</TD><TD style="TEXT-ALIGN: right">1.877</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>Name3</TD><TD>Surname4</TD><TD style="TEXT-ALIGN: right">1.753 </TD><TD style="TEXT-ALIGN: right"></TD><TD>Name2</TD><TD>Surname3</TD><TD style="TEXT-ALIGN: right">5.862</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Name4</TD><TD>Surname5</TD><TD style="TEXT-ALIGN: right">7.003 </TD><TD style="TEXT-ALIGN: right"></TD><TD>Name3</TD><TD>Surname4</TD><TD style="TEXT-ALIGN: right">1.753</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>Name4</TD><TD>Surname6</TD><TD style="TEXT-ALIGN: right">7.332 </TD><TD style="TEXT-ALIGN: right"></TD><TD>Name4</TD><TD>Surname5</TD><TD style="TEXT-ALIGN: right">7.003</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>Name4</TD><TD>Surname7</TD><TD style="TEXT-ALIGN: right">4.941 </TD><TD style="TEXT-ALIGN: right"></TD><TD>Name4</TD><TD>Surname6</TD><TD style="TEXT-ALIGN: right">7.332</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Name4</TD><TD>Surname7</TD><TD style="TEXT-ALIGN: right">4.941</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>*********</TD><TD>*********</TD><TD>*********</TD><TD>*********</TD><TD>*********</TD><TD>*********</TD><TD>*********</TD></TR></TBODY></TABLE>


Markmzz
 
Upvote 0
hi
Thanks for your detailed response.

Any solutions for Excel 2003?

Thanks

markmzz said:
Projectile,

Only for Excel 2010:

1) Take a right click on the Name field in the PivotTable and choose Field settings.

2) Then activate the Layout & Print tab, and check the Repeat item labels.

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="FONT-WEIGHT: bold">Name</TD><TD style="FONT-WEIGHT: bold">Surname</TD><TD style="FONT-WEIGHT: bold">Sold</TD><TD style="TEXT-ALIGN: right"></TD><TD>Soma de Sold</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Name1</TD><TD>Surname1</TD><TD style="TEXT-ALIGN: right">7.549 </TD><TD style="TEXT-ALIGN: right"></TD><TD>Name</TD><TD>Surname</TD><TD>Total</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Name1</TD><TD>Surname2</TD><TD style="TEXT-ALIGN: right">1.877 </TD><TD style="TEXT-ALIGN: right"></TD><TD>Name1</TD><TD>Surname1</TD><TD style="TEXT-ALIGN: right">7.549</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Name2</TD><TD>Surname3</TD><TD style="TEXT-ALIGN: right">5.862 </TD><TD style="TEXT-ALIGN: right"></TD><TD>Name1</TD><TD>Surname2</TD><TD style="TEXT-ALIGN: right">1.877</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>Name3</TD><TD>Surname4</TD><TD style="TEXT-ALIGN: right">1.753 </TD><TD style="TEXT-ALIGN: right"></TD><TD>Name2</TD><TD>Surname3</TD><TD style="TEXT-ALIGN: right">5.862</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Name4</TD><TD>Surname5</TD><TD style="TEXT-ALIGN: right">7.003 </TD><TD style="TEXT-ALIGN: right"></TD><TD>Name3</TD><TD>Surname4</TD><TD style="TEXT-ALIGN: right">1.753</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>Name4</TD><TD>Surname6</TD><TD style="TEXT-ALIGN: right">7.332 </TD><TD style="TEXT-ALIGN: right"></TD><TD>Name4</TD><TD>Surname5</TD><TD style="TEXT-ALIGN: right">7.003</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>Name4</TD><TD>Surname7</TD><TD style="TEXT-ALIGN: right">4.941 </TD><TD style="TEXT-ALIGN: right"></TD><TD>Name4</TD><TD>Surname6</TD><TD style="TEXT-ALIGN: right">7.332</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Name4</TD><TD>Surname7</TD><TD style="TEXT-ALIGN: right">4.941</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>*********</TD><TD>*********</TD><TD>*********</TD><TD>*********</TD><TD>*********</TD><TD>*********</TD><TD>*********</TD></TR></TBODY></TABLE>


Markmzz
Click to expand...
 
Upvote 0
projectile said:
hi
Thanks for your detailed response.

Any solutions for Excel 2003?

Thanks
Click to expand...

Projectile,

Unfortunately, I don't know a way to do the same for a pivot table in Excel 2003.

Another "solution", but not very good, would be:

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Name</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Surname</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Sold</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Soma de Sold</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Name1</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Surname1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">7.549 </TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Name</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Surname</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Total</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Name</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Surname</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Total</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Name1</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Surname2</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">1.877 </TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Name1</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Surname1</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">7.549 </TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Name1</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Surname1</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">7.549 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Name2</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Surname3</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">5.862 </TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">Surname2</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">1.877 </TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Name1</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">Surname2</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">1.877 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Name3</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Surname4</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">1.753 </TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Name2</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Surname3</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">5.862 </TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Name2</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Surname3</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">5.862 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Name4</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Surname5</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">7.003 </TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Name3</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Surname4</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">1.753 </TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Name3</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Surname4</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">1.753 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Name4</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Surname6</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">7.332 </TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Name4</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Surname5</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">7.003 </TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Name4</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Surname5</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">7.003 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Name4</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Surname7</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">4.941 </TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">Surname6</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">7.332 </TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Name4</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">Surname6</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">7.332 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">Surname7</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">4.941 </TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Name4</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">Surname7</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">4.941 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>List</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>PivotTable</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Final Result</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>


1) To create the PivotTable.

2) Copy and paste the data needed for the PivotTable (E2:G9 - in my example) to another location on the worksheet (I2 in my example).

3) Select the data copied from the Name column (I2:I9 - in my example), pressing the F5 key, click the Special button (in the go to dialog box), choose Blank (in the go to dialog box) and click OK.

4) With the Blank cells in the column Name selected, type the following formula =I3 (a cell above the active cell - in my example) and pressing Ctrl+Enter.

Markmzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Pivot Table Date Field
2
Replies
14
Views
213
Alex Blakenburg
A
A
  • Question Question
Auto populate columns in one tab based on entries in another
Replies
6
Views
122
dermie_72
D
A
  • Question Question
Refresh a Pivot table with group fields gives errors.
Replies
0
Views
391
axelg
A
A
  • Question Question
Two dimensional dynamic named range over a pivot table not working as I thought
Replies
4
Views
922
ashleighbrown
A
I
  • Question Question
Look for value in a range and if it matches display cell contents
Replies
1
Views
436
JoeMo
J

Forum statistics

Threads
1,224,609
Messages
6,179,876
Members
452,949
Latest member
Dupuhini

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top