Geek Answers Handbook

Pivot table - counting unique values ​​- Excel 2010

I have a pivot table in excel 2010 based on network output. I would like to have the number of unique values ​​per week of users posted on the network.

I found this topic: A simple pivot table for counting unique values that would add an extra column to my data. The problem is for me - I need unique values ​​per week, not across the entire table.

Input Example:

week 1 USER_A message1 week 1 USER_B message2 week 1 USER_A message3 week 2 USER_A message4 week 2 USER_B message5 week 2 USER_C message6

What kind of superiority actually happens when I request an account, it gives 3 both an account for week 1 and week 2. I need 2 for 1 week (since there are 2 users), and an account for week 2 will be 3 (since there are 3 users).

Does anyone know how to do this?

+4
source share
4 answers

You can create a new column and add the formula:

 =IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)>1,0,1)

And rotate in this column:

enter image description here

+7
source

Wouldn't it be another option to combine the two columns and then remove the duplicates? Using the image in Jerry's message, follow these steps:

  • Insert Column D
  • In column D, enter the formula:

= CONCATENATE (A2, "", B2)

This should give you the result "week 1 USER_A" for line 2, "week 1 USER_B" for line 2, etc.

  • On the Data tab, select Select Duplicates
  • Select only column D, then click OK

Now you can only count unique instances of columns A and B. This is what I have done in the past; I hope I did not do it completely wrong! :-)

+1
source

In the description I will use the image of your table (I am not allowed to leave my photo). The first sorting table is Week , then Users , and then in cell D2, enter = IF (AND (A3 = A2, B3 = B2), 0,1) . Copy the formula into the column. On the large student table A1: P141736, which I use, this formula works instantly.

0
source

Since you did not indicate where your data came from.

I assume that your data is stored in an SQL database, you can use the partition counter or the number of columns row_number to achieve these results.

Concluding the decision provided by Jerry, the column "Number of Users" can be achieved as follows from SQL

case when row_number() over (partition by Week order by Users) = 1 then 1 else 0 end as [Unique?]

So, this is actually a bit more, first splits the result set into a separate week, orders the Users column and assigns a sequential identifier to each row in the sections. We basically filter out line number 1 in each section.

If the data does not come from SQL, ignore it.

0
source

More articles:


All Articles