excel - How to count same values in a column for certain users -


i have column appear different users , other 1 appear value. each user can have several of these values , can repeated. know how count same values in same column need know quantity of identical values each user. example: 

  user            value  user1            100  user1            300  user1            100  user1            200  user1            300  user1            100  user1            100  user1            400  user2            100  user2            100  user2            100  user2            400  user2            100  user2            200  user2            200

then should appear like: 

 user          value     count user1          100         4 user1          200         1 user1          300         2 user1          400         1

and same second user , on.

edit - sorry misunderstood had do... had more values , though had focus in 1 value apparently had take in count of them: need following: 

  user       v1      v2     v3     v4   user1            c            b   user2      b       d            b   user1            f            c   user3      c            c       d   user3      d       b

what should appear user1

   user1      v1           2     b       0     c       0     d       0      v2           1     b       0     c       0     d       0     e       0     f       1      v3           2     b       0     c       0     d       0      v4           0     b       1     c       1     d       0

any ideas that? anyway help!

edit

added new answer below based on new data structure original poster.

so couple things have happen work. reccomend converting data range table. makes easy dynamic column , value references. highlight data range , hit ctrl+t.

now data in cell a1:e6 have above

table1=a1:e6=

user    v1  v2  v3  v4 user1     c     b user2   b   d     b user1     f     c user3   c     c   d user3   d   b
  • in cell f2 enter
  • =iferror(index(table1[user],match(0,index(countif($f$1:f1,table1[user]),0,0),0)),"-")
  • this return first unique user in "user" column. show rest drag down copy formula in cells below f2.
  • then in cell g2 made drop down list of range f2:f4, unique users.
  • in cell g3 made drop down list of range b1:e1, headers v1,v2...
  • in cell g4 enter
  • =iferror(index(indirect("table1["&$g$3&"]"),match(0,index(countif($g$3:g3,indirect("table1["&$g$3&"]")),0,0),0)),"-")
  • you can copy formula down cells below until see "-" means there no more unique values column "v1"
  • in cell h4 enter
  • =countifs(table1[user],$g$2,indirect("table1["&$g$3&"]"),g4)

this give looks this... hope helps,

unique users unique values unique count 2

end edit

first answer...

here how cell formulas.

  • range of users header in a:a
  • range of values header in b:b
  • range a:b sorted users , values
  • in d1:f1 type in new headers, unique users, unique values, user values count.
  • there 3 formulas, 1 each column.
  • d2 = =index(a:a,row($f$2)+sum(f$1:f1))
  • e2 = =index(b:b,row($f$2)+sum(f$1:f1))
  • f2 = =countifs(a:a,d2,b:b,e2)

with 3 in second row should able drag down formula fill list out more , more showing unique users , unique values count of values. if formula returns 0 means @ end of unique data.

-scheballs

edit- typo, jerry

unique users unique values unique count


Comments

Post a Comment

Popular posts from this blog

php - cannot display multiple markers in google maps v3 from traceroute result -

i'm newbie in php mysql , google maps v3. have problems in google maps v3. made traceroute web based , map traceroute's result in google maps v3 final project of college education. have try many tutorials didn't work. have tried tutorials in forum nothing worked. problem cannot display multiple markers got location data database. maybe because i'm newbie , not know how do. confuse looping show markers. here code <?php error_reporting(e_all ^ (e_notice)); ini_set('max_execution_time', 360); $enable_log_user = false; global $ip, $host_name, $host_ip; $host = @$_post['host']; $trace = @$_post['trace']; $self = $_server['php_self']; include("phpsqlajax_dbinfo.php"); $connection = mysql_connect ('127.0.0.1', $username, $password); if (!$connection) { die('not connected : ' . mysql_error());} $db_selected = mysql_select_db($database, $connection); if (!$db_selected) { die ('can\'t use db : ...
Read more

c# - DetailsView in ASP.Net - How to add another column on the side/add a control in each row? -

i have detailsview control on page used edit various fields of record, works in respect. i looking way add 1 column (and if works, why not more) right, absolutely read-only, show same fields of record comparison purposes. i aware there no obvious way such thing out of box detailsview . have looked other controls (transposed gridview , recommended formview , listview ), nothing satisfies. have special data binding setup using detailsview , can't out of without losing features. anyone on how "hack in" additional columns (for display only) on detailsview ? the solution have now, use second detailsview , visible set false in aspx. in code, make sure databind hidden detailsview hosts data third column first, initial detailsview named itemdetails . and in item created event, pass third column html rendering of hidden controls (in last code block) : protected void itemdetails_itemcreated(object sender, eventargs e) { if (dataite...
Read more

javascript - firefox memory leak -

i have following script (i use raphaeljs also) draws circle, waits 1 second, draws circle again, etc (you can copy paste - functional, need include raphael raphaeljs.com): <div id="holder"> </div> <script src="raphael.js" type="text/javascript"></script> <script> var paper = raphael(10, 50, 320, 200); var rad = math.pi / 180; var finangle = 0; function sector(cx, cy, r, startangle, endangle, params) { var x1 = cx + r * math.cos(-startangle * rad), x2 = cx + r * math.cos(-endangle * rad), y1 = cy + r * math.sin(-startangle * rad), y2 = cy + r * math.sin(-endangle * rad); return ["m", cx, cy, "l", x1, y1, "a", r, r, 0, +(endangle - startangle > 180), 0, x2, y2]; } var path = sector(100,100,50,0,180); var arc = paper.path(path); function draw() { path = sector(100,100,50,0,finangle); ...
Read more