Pivot Table: Compare and display value that is repeated

**zouk01** · 11-11-2011, 11:55 PM

Hi,

Any kind souls well-versed in pivot table kindly give your valuable advice. Thank you!

I'm trying to extract and count only similiar data from Table 'A' by pivoting. Those that do not have repeated values more than once will not be highlighted will automatically be generated into Table 'B'.

Referring to below example, Date 10005, 10007 & 10008 does not have repeated data over the week. Can anyone advice how pivot table can do this?

TABLE 'A'
01-Nov-11 02-Nov-11 03-Nov-11 04-Nov-11 05-Nov-11 06-Nov-11 07-Nov-11
10001 10001 10001 10004 10002 10004 10001
10002 10002 10002 10006 10003 10002
10003 10003 10003 10004 10003
10004 10004 10004 10006 10004
10005 10006 10006
10006
10007
10008

TABLE 'B'
01-Nov-11 02-Nov-11 03-Nov-11 04-Nov-11 05-Nov-11 06-Nov-11 07-Nov-11 Total
10001 1 1 1 1 4
10002 1 1 1 1 1 5
10003 1 1 1 1 1 5
10004 1 1 1 1 1 1 1 7
10006 1 1 1 1 1 5

**Azam Ali** · 11-12-2011, 02:01 AM

You could do it without pivot

see attatchment

**zouk01** · 11-14-2011, 01:28 AM

Hi Azam,

Thanks for the reply.

The method you provided is hardcoding on each cell on Table 'B' get get reference.

However, Table 'B' that I've mentioned in the enquiring summary 'does not exists'. It only exists when a formula/pivot table is created to sieve through the data from Table 'A' then subsequently produced Table 'B' as an end results.. More automation on my queries.

Any help on such instance?

**Azam Ali** · 11-14-2011, 01:50 AM

in other case you have to change your table and should insert the data in column formate becouse existing table A is not suitable to apply pivot table

see attachment

pivot.xls