How do I count occurrences of a pattern of data in Excel? -


i realized after manual scanning 90 records, going painful , tedious unless made use of automation.

i have set of data, 4000 records occurs in pattern want track. first column important one. want scan through column , record in new column how man times number has occurred. possible programatically in excel?

note: i'm not looking single pattern or single occurrence of pattern.

e.g. in sample 313 occurs 1 time, 314 occurs 6 times, 315 occurs 2 times, etc.

at end of occurrences want like

--- desired output -------  313 1       343  1 314 1   344   314 2   344  314 3   344 314 4   344 314 5   344   314 1   345  6 315 2   345   315 1   346  2   -- sample data ------------------------------------ 313 1   343 314 1   344 314 2   344 314 3   344 314 4   344 314 5   344 314 1   345 315 2   345 315 1   346 316 2   346 316 1   347 317 2   347 318 1   348 318 2   348 319 1   349 319 2   349 319 3   349

5/23/13 data delimited spaces. not in 1 cell. don't know how create grid picture here. leftmost cell 1 want counted.

the desired output example of want. there 6 occurrences of 314, want count summary cell compiled in row of last occurrence.

i backed up, slowed down , went basic programming principles, slow feel @ times.

  1. flowchart
  2. pseudocode
  3. prototype
  4. test
  5. repeat 3 , 4 needed.

i found following code did needed. share follow.

sub countfoo() dim startcell range dim precell range dim counter integer dim startpoint range, endpoint range dim filename string, delimitingcharacter string, sqlpre string, sqlpost string dim sql string dim outfile integer  filename = "update_foo.sql" sqlpre = "update foo set foodata = " sqlpost = " details = '" outfile = freefile() open filename output outfile counter = 1  set startpoint = cells(2, 4) startpoint.activate  debug.print "start point:" & startpoint.address debug.print startpoint.value  set startcell = activecell set precell = startcell.offset(-1, 0)   while startcell.value <> "end"  if (startcell.value = precell.value)   counter = counter + 1   set precell = startcell   set startcell = startcell.offset(1, 0) elseif ((startcell.value <> precell.value) or (startcell.value = "end"))   startcell.offset(-1, 3).value = counter   if counter > 1     startcell.offset(-1, 0).interior.color = 5296274     startcell.offset(-1, 1).interior.color = 5296274     startcell.offset(-1, 2).interior.color = 5296274     startcell.offset(-1, 3).font.bold = true     startcell.offset(-1, 3).interior       .pattern = xlgray8       .patterncolor = 65535       .color = 5296274     end   end if   sql = sqlpre & counter & sqlpost & startcell.offset(-1, 0).value & "';"   print #outfile, sql   counter = 1   set precell = startcell   set startcell = startcell.offset(1, 0) end if loop close #outfile end sub

Comments

Post a Comment

Popular posts from this blog

SPSS keyboard combination alters encoding -

my question not have programming practical issue on spss. accident keyword combination cannot remember because did not intend press changed encoding in spss , text in data in different language english shown small boxes. nevertheless, when kind of analysis performed different language text shown in output. does know reason? tried change encoding did not produce result. first, @ edit > options , see whether setting unicode or locale. might fix it. if have text in multiple languages, want unicode. otherwise, may have specific locale set wrong. can change set locale, need know locale want use.
Read more

Add new record to the table by click on the button in Microsoft Access -

in ms access form want implement separate button, adds new record table. in order added button , attached button event: private sub btnaddrec_click() refresh codecontextobject on error resume next docmd.gotorecord , , acnewrec if err.number <> 0 btnaddrec.enabled = false end if end end sub everything ok when open window , click btnaddrec button, problem when first of perform navigation through existed records , after click on button. got runtime error: 2105: «you can't go specified record. may @ end of recordset» . how solve issue, need have ability add new record on click on specific button, no matter, have walked or not walked through records before. thanks. i created simple form field call description (and autonumber) , created button code follows click event. filled number of records , navigated through them, clicked addnewrec button. form navigated new record without issues. able click addnewrec button directly after
Read more

CSS3 Transition to highlight new elements created in JQuery -

i want use css3 color transition apply highlight-fading color effect (yellow transparent) new elements appended markup using jquery. css #content div { background-color:transparent; -moz-transition:background-color 2s; -webkit-transition:background-color 2s; -o-transition:background-color 2s; transition:background-color 2s; } #content div.new { background-color:yellow; } html <div id="content"></div> <a id="add-element" href="#">add new element</a> js $('#add-element').click(function() { var newelement = $('<div class="new">new element</div>'); $('#content').append(newelement); newelement.removeclass('new'); }); when click link, new element created. class "new" (background color yellow) , it's appended html markup. should able remove "new" class trigger background color transition transparent (or whate
Read more