5

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

I found this topic: Simple Pivot Table to Count Unique Values which would add an extra column to my data. Problem for me is, I need unique values per week, not over all the table.

Example input:

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 excel actually does is when I ask for a count, is it gives 3 as a count both for week 1 as for week 2. I need the count for week 1 to be 2 (as there are 2 users) and the count for week 2 to be 3 (as there are 3 users).

Anyone know how this can be done?

Community
  • 1
  • 1
NillsF
  • 118
  • 1
  • 1
  • 5
  • Check out this answer: http://stackoverflow.com/questions/11876238/simple-pivot-table-to-count-unique-values – Jack Sep 19 '13 at 15:24

4 Answers4

8

You can create a new column and add the formula:

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

And pivot on this column:

enter image description here

Jerry
  • 70,495
  • 13
  • 100
  • 144
1

Wouldn't another option be to concatenate the two columns and then remove duplicates? Using the image in Jerry's post, the steps would be:

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

=CONCATENATE(A2," ",B2)

This should give you the result "week 1 USER_A" for row 2, "week 1 USER_B" for row 2, and so on.

  • Under the 'Data' tab, select "Highlight Duplicates"
  • Select only column D, then click OK

You should now be able to count only unique instances of columns A and B. That's what I've done in the past; hopefully I wasn't doing it entirely the wrong way! :-)

Jay
  • 11
  • 1
1

Since you did not specify where your data is coming from.

I will assume your data is stored on an SQL database, one can use a partition count or a row_number partition count to achieved those results.

Completing the solution provided by Jerry, the 'Count of users' column can be achieve 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 actually does a little bit more, first partitions the result set by distinct Week, orders the Users column and assigns a sequential id to every row on the partitions. We basically filter out the number 1 row in every partition.

If data does not come from SQL, disregard.

Pedro Rodrigues
  • 2,520
  • 2
  • 27
  • 26
0

In description I will use picture of yours table (I not allowed to post my picture). First sort table by Week, then by Users and then in cell D2 insert =IF(AND(A3=A2,B3=B2),0,1). Copy formula down the column. On a big student table A1:P141736 that I use, this formula works instantly.

Ante Franjic
  • 1