Howto see if duplicate values exist in two colums in excel and consolidate them using vba -


i need transform table looks this:

oi  buy securities upp          100000  0.622   0.624   62381.05 oi  buy securities dic          30000   1.57    1.575   47239.525 oi  buy securities dic          26220   1.57    1.574   41278.605 oi  buy securities dic          10000   1.57    1.574   15743.175 oi  buy securities dic          30000   1.57    1.574   47229.525 oi  buy securities dic          3780    1.57    1.574   5950.92 oi  buy securities dib          50000   3.18    3.189   159447.25 oi  buy securities emaar    50000   5.3 5.315   265738.75 oi  buy securities airarabia    100000  1.22    1.223   122345.5

to this:

oi  buy securities upp          100000  0.622   0.624   62381.05 oi  buy securities dic          100000  1.57    1.574   157441.75 oi  buy securities dib          50000   3.18    3.189   159447.25 oi  buy securities emaar    50000   5.3 5.315   265738.75 oi  buy securities airarabia    100000  1.22    1.223   122345.5

this table log of daily transactions have incurred. need consolidate trades of same security , same price single trade. columns : security description, volume, share price, average price after commission, total transaction value. in order. total transaction value volume*the average price after commission. need create second table besides first 1 column m onwards on excel sheet. everyday new file broker looks , need modify second table. can please suggest someway can automate using vba. or perhaps macro. cannot use pivottable unfortunately otherwise have been quite simple.

this should want. both sheet1 , sheet2 must exist in example. sheet1 must contain starting data.

sub consolidatespecial()
   ' first copy sheet work area
   for irow = 1 65535
      if worksheets("sheet1").cells(irow, 1) = "" exit for
      for icolumn = 1 5
         worksheets("sheet2").cells(irow, icolumn) = worksheets("sheet1").cells(irow, icolumn)
      next
   next
   
   ' colidate cells
   for irowfirstset = 1 65535
      if worksheets("sheet2").cells(irowfirstset, 1) = "" , worksheets("sheet2").cells(irowfirstset, 2) = "" exit for
      for irowsecondset = irowfirstset + 1 65535
         if worksheets("sheet2").cells(irowsecondset, 1) = "" , worksheets("sheet2").cells(irowsecondset, 2) = "" exit for
         if worksheets("sheet2").cells(irowfirstset, 1) = worksheets("sheet2").cells(irowsecondset, 1) then
            if worksheets("sheet2").cells(irowfirstset, 3) = worksheets("sheet2").cells(irowsecondset, 3) then
               worksheets("sheet2").cells(irowfirstset, 2) = worksheets("sheet2").cells(irowfirstset, 2) + worksheets("sheet1").cells(irowsecondset, 2)
               worksheets("sheet2").cells(irowfirstset, 4) = worksheets("sheet2").cells(irowsecondset, 4)
               worksheets("sheet2").cells(irowfirstset, 5) = worksheets("sheet2").cells(irowfirstset, 5) + worksheets("sheet1").cells(irowsecondset, 5)
               worksheets("sheet2").cells(irowsecondset, 1).clear
            end if
         end if
      next
   next
   
   ' delete blank rows
   for irow = 1 65535
      if worksheets("sheet2").cells(irow, 1) = "" , worksheets("sheet2").cells(irow, 2) = "" exit for
      if worksheets("sheet2").cells(irow, 1) = "" then
         worksheets("sheet2").rows(irow).delete
         irow = irow - 1
         if irow < 1 irow = 1
      end if
   next
   
   msgbox "done"
end sub


Comments

Post a Comment

Popular posts from this blog

java.util.scanner - How to read and add only numbers to array from a text file -

i'm learning java , have question regarding reading file want read numbers file contains strings well. here example of file: 66.56 "3 java 3-43 5-42 2.1 1 and here coding: public class test { public static void main (string [] args){ if (0 < args.length) { file x = new file(args[0]); try{ scanner in = new scanner( new fileinputstream(x)); arraylist<double> test = new arraylist<>(); while(in.hasnext()){ if(in.hasnextdouble()){ double f=in.nextdouble(); test.add(f);} else {in.next();} } catch(ioexception e) { system.err.println("exception during reading: " + e); } } my problem add 66.56,2.1 , 1 doesn't add 3 after "3 or ignores 3-43 , 5-42 can tell me how skip strings , add doubles here? thanks all said 3 ie; "3, 3-43 , 4-42 strings either u read string , split , check number @ " , - or put in space between characters , integers. jvm after compilation tre...
Read more

rewrite - Trouble with Wordpress multiple custom querystrings -

i'm working on adding multiple querystring , doesn't seem work. i've found codes seems work others might i'm missing something. i want like: http://mydomain.com/board/?getyear=2013&getsometext2=sometext to http://mydomain.com/board/2013/sometext/ i added code below functions.php file. doesn't seem work should though. this works. echoing querystring gets both variable without problem. http://mydomain.com/board/2013/sometext/ but doesn't. tested querystring vis isset() seems nothing being set/get http://mydomain.com/board/2013/ function add_query_vars($vars) { $vars[] = "getyear"; $vars[] = "getsometext2"; return $vars; } add_filter('query_vars', 'add_query_vars'); function add_rewrite_rules($arules) { $anewrules = array('board/([^/]+)/([^/]+)/?$' => 'index.php?pagename=board&getyear=$matches[1]&getsometext2=$matches[2]'); $arules = $a...
Read more