How to count similar values in pivot as 1

[Mr.Jay]

Hi,

is there a way to count similar values in a pivot table.

Lets say, 4 names with same values but it should only count as 1.

Thanks!

 

[Aladin Akyurek]

Hi,

is there a way to count similar values in a pivot table.

Lets say, 4 names with same values but it should only count as 1.

Thanks!

Example: Calculate the number of unique items in A2:A7.

<TABLE style="WIDTH: 442pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=590 x:str><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4937" width=135><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=64>X</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=64>Y</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=64>Unique</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 101pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=135>X</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=100>Data</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=35>Total</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>Loc-1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>20</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num x:fmla="=1/COUNTIF($A$2:$A$7,A2)">0.5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>Loc-1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>Sum of Y</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>70</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>Loc-2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>30</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num x:fmla="=1/COUNTIF($A$2:$A$7,A3)">0.5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>Sum of Unique</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>Loc-1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num x:fmla="=1/COUNTIF($A$2:$A$7,A4)">0.5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>Loc-2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>Sum of Y</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>95</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>Loc-2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>25</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num x:fmla="=1/COUNTIF($A$2:$A$7,A5)">0.5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>Sum of Unique</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17>E</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>Loc-1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>35</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num x:fmla="=1/COUNTIF($A$2:$A$7,A6)">1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>Total Sum of Y</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>165</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17>F</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>Loc-2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>40</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num x:fmla="=1/COUNTIF($A$2:$A$7,A7)">1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>Total Sum of Unique</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>4</TD></TR></TBODY></TABLE>

The Unique field is created with:

=1/COUNTIF($A$2:$A$7,A2)

Total Sum of Unique in PT is obtained with Unique in the DATA field.

You can also calculate this number directly from the source, that is, without the Unique field...

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A7<>"",MATCH("~"&A2:A7,A2:A7&"",0)),ROW(A2:A7)-ROW(A2)+1),1))

 

[Fazza]

Welcome, Mr Jay.

It would be great if you can post some sample data, both input data & corresponding expected result, to better understand the requirement.

regards, Fazza

 

[Mr.Jay]

Alright, sorry for the inconvenience.

Below is the sample data (Region, Sales Rep, Product)

On the right is the pivot table. It showed on the count for northeast is "9" but there is are only two staff for that region. How will i be able to do this in the pivot table to show the result as 2 for northeast?

Thanks!

<title>Excel Jeanie HTML</title>Sheet1

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Century Gothic,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 98px;"> <col style="width: 93px;"> <col style="width: 112px;"> <col style="width: 64px;"> <col style="width: 95px;"> <col style="width: 130px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td style="font-weight: bold;">REGION</td> <td style="font-weight: bold;">Sales Rep</td> <td style="font-weight: bold;">PRODUCT</td> <td> </td> <td style="background-color: rgb(204, 255, 204); color: rgb(0, 128, 0); font-weight: bold;">Row Labels</td> <td style="background-color: rgb(204, 255, 204); color: rgb(0, 128, 0); font-weight: bold;">Count of Sales Rep</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td>Northeast</td> <td style="text-align: left;">Smith</td> <td>Golf Balls</td> <td> </td> <td style="text-align: left;">Central</td> <td style="text-align: right;">5</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td>Northeast</td> <td style="text-align: left;">Smith</td> <td>Basketballs</td> <td> </td> <td style="text-align: left; background-color: rgb(255, 255, 153); color: rgb(153, 51, 0);">Northeast</td> <td style="text-align: right; background-color: rgb(255, 255, 153); color: rgb(153, 51, 0);">9</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td>Northeast</td> <td style="text-align: left;">Smith</td> <td>Basketballs</td> <td> </td> <td style="text-align: left;">Southwest</td> <td style="text-align: right;">5</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td>Northeast</td> <td style="text-align: left;">Smith</td> <td>Basketballs</td> <td> </td> <td style="text-align: left; background-color: rgb(204, 255, 204); color: rgb(0, 128, 0); font-weight: bold;">Grand Total</td> <td style="text-align: right; background-color: rgb(204, 255, 204); color: rgb(0, 128, 0); font-weight: bold;">19</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td>Southwest</td> <td style="text-align: left;">Brown</td> <td>Footballs</td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td>Southwest</td> <td style="text-align: left;">Brown</td> <td>Footballs</td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td>Southwest</td> <td style="text-align: left;">Brown</td> <td>Footballs</td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">9</td> <td>Southwest</td> <td style="text-align: left;">Brown</td> <td>Footballs</td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">10</td> <td>Southwest</td> <td style="text-align: left;">Brown</td> <td>Footballs</td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">11</td> <td>Central</td> <td style="text-align: left;">Wallace</td> <td>Baseballs</td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">12</td> <td>Central</td> <td style="text-align: left;">Wallace</td> <td>Golf Balls</td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">13</td> <td>Central</td> <td style="text-align: left;">Wallace</td> <td>Baseballs</td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">14</td> <td>Central</td> <td style="text-align: left;">Wallace</td> <td>Golf Balls</td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">15</td> <td>Central</td> <td style="text-align: left;">Wallace</td> <td>Baseballs</td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">16</td> <td>Northeast</td> <td style="text-align: left;">Adams</td> <td>Golf Balls</td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">17</td> <td>Northeast</td> <td style="text-align: left;">Adams</td> <td>Basketballs</td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">18</td> <td>Northeast</td> <td style="text-align: left;">Adams</td> <td>Footballs</td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">19</td> <td>Northeast</td> <td style="text-align: left;">Adams</td> <td>Baseballs</td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">20</td> <td>Northeast</td> <td style="text-align: left;">Adams</td> <td>Baseballs</td> <td> </td> <td> </td> <td> </td></tr></tbody></table>

 

[Fazza]

Thanks. Aladin has suggested one solution. I'll describe another approach, that you may or may not prefer; using SQL.

In this instance, where a pivot table doesn't intrinsically provide the required functionality, SQL modifies the initial dataset to suit: this is necessarily specific to the requirement. So, for this example, I will confine my answer to the specific question for the REGION and `Sales Rep`fields.

Say the file is saved and the source data table range "A1:B20" has the defined name "MyData" - not a dynamic named range. (Using a defined name isn't necessary, BTW.) From a new workbook, start the pivot table wizard and at the first step take the 'external data' option. Follow the wizard and at the last step take the option to edit in MS Query. In MS Query hit the 'SQL' button and edit the SQL text by inserting the word DISTINCT after SELECT. (Not case sensitive, btw.)The SQL will change as below. OK to enter the SQL then hit the 'open door' icon to exit MS Query, then complete the pivot table. If you like, the resultant worksheet containing the pivot table can be moved into the source data workbook. This approach with SQL suits large amounts data, does not require additional fields to be added to the source data, and certainly no extra formulas.

Also, there are other approaches that can be used if you don't need a pivot table. I'm thinking of a query table, with or without VBA, or a recordset, using VBA & ADO. And there are other progammatic solutions via VBA but these would be specific to the task and not usually preferred over built-in functionality (such as pivot tables).

regards, Fazza

'SQL before editing
SELECT REGION, `Sales Rep`
FROM MyData
 
'after editing
SELECT DISTINCT REGION, `Sales Rep`
FROM MyData

 

[jayjavina]

Thanks Fazza. AM not that familiar with SQL but let try to study it first.

Appreciate it