Counting and Displaying

[ryan.gibson]

I have two columns, one of users and one of errors I want to produce the user's name and how many of each error if the user is associated with 2 or more errors. Here's an example of what I have:

user1 error1
user1 error2
user1 error3
user2 error1
user3 error3

Since user 1 shows up more than twice I want excel to spit out:

User Name error1 error2 error3
user1 1 1 1

 

[Andrew Fergus]

Hello and welcome to MrExcel.

I have used the following layout:

  A      B       C      
1 User   Error   Helper 
2 user 1 error 1        
3 user 1 error 2 1      
4 user 1 error 3        
5 user 2 error 1        
6 user 3 error 3        
7 user 1 error 2        
8 user 4 error 1        
9 user 4 error 3 2      
Sheet1
[Table-It] version 09 by Erik Van Geit

RANGE FORMULA (1st cell)
C2:C9 =IF(COUNTIF(A$2:A2,A2)=2,MAX(C$1:C1)+1,"")
[Table-It] version 09 by Erik Van Geit

Notice the helper column C. Enter the formula provided into cell C2 and copy down.

Then I have a results area on the same sheet built like this:

   J                       K         L       M       N       
 1 Users with many errors:                   2               
 2                                                           
 3 Results                 User Name error 1 error 2 error 3 
 4 1                       user 1    1       2       1       
 5 2                       user 4    1       0       1       
 6 3                       n/a       0       0       0       
 7 4                       n/a       0       0       0       
 8 5                       n/a       0       0       0       
 9 6                       n/a       0       0       0       
10 7                       n/a       0       0       0       
11 8                       n/a       0       0       0       
12 9                       n/a       0       0       0       
13 10                      n/a       0       0       0       
Sheet1
[Table-It] version 09 by Erik Van Geit

RANGE   FORMULA (1st cell)
K4:K13  =IF(J4< =$M$1,INDEX(A:A,MATCH(J4,C:C,0)),"n/a")
L4:N13  =SUMPRODUCT(--($A$1:$A$1000=$K4),--($B$1:$B$1000=L$3))
M1      =MAX(C:C)
[Table-It] version 09 by Erik Van Geit

You can format the numbers so the zeroes do not appear. Notice I have limited the range in the sumproduct formula to row 1000 - amend the formula to suit if you have alarger data range but make sure both parts of the formula reference the same number of rows.

I trust this helps.
Andrew