Using Excel to display the number of occurrences in a date range

Question

I have a list of transaction dates and the user ID of the person who committed the transaction on that date (only 1 Tx / day is allowed). For instance:

I would like to create a matrix that shows for each date the number of users who have completed 1 transaction, 2-10 transactions, 10-20 transactions, etc. For example (note: the data below doesn't match the transaction data above):

Is a pivot table my best mechanism here? If yes (or no), how would I approach this?

Answer 1

2007 -

1) , 2) 3) Loabel 4) = > 5) yoiu 6) , , 1-10 ..

Answer 2

, " ", , R, . ( R, sql, excel)

"trans_data", . : "trans_date" "user_id". , , .

library(plyr)
adply(table(trans_date),1,function(x) {
     d = NULL
     d["1"] <- sum(x==1)
     d["2_to_5"] <- sum(x > 1 & x <= 5) 
     d["6_to_27"] <- sum(x > 5 & x <= 27)
     d["28_to_120"] <- sum(x > 27 & x <= 120)
     d["121_to_398"] <- sum(x > 120 & x <= 398)
     d[">_398"] <- sum(x > 398)
     return(d)
   }
)

  trans_date   1 2_to_5 6_to_27 28_to_120 121_to_398 >_398
1 2009-01-25 257    169      61         7          1     0
2 2009-01-26 145    125      53         3          1     0
3 2009-01-27 175    117      44        12          0     0
4 2009-01-28 171    138      49         7          4     0
5 2009-01-29 756    217      71         5          3     0