Help on counting occurences

[Krishna]

Hi,
Could someone help on the following problem.
I have a sheet where all stock transactions are entered,each with a
unique reference id in say sheet1.I need to transfer this data to
another sheet and update the positions of the stock.Presently I have a
macro where in I enter the unique reference id and run to update the
information in the different sheet(say sheet2).The problem is that I
need to display this information in such a way that all transactions
are grouped together.For example the data on sheet1 may look like this

ref stock name transaction type qty position
260704/01 microsoft buy 50 open

after several other entries we may have the transaction for close
position

150804/01 micrsooft sell 50 close

now when I use the ref id to transfer data to sheet2 I will have this
close position after several entries.Is there a way I can push it up
to the row just after the open entry in sheet2.Also the qty 50 need
not be sold in one lot of 50 ,it could be sold in multiple
transactions (each with unique id) of differing qty(ex transaction1
-sell 25 ,transaction2-sell 25).Is there a way to count all the
occurences of the stock in a sheet1 and transfer the data together to
sheet 2.


Appreciate all the help I can get on this
regards
Krishna

 

[merjet]

Will sorting the data (on stock_name) with the menu Data | Sort
get what you want?

HTH,
Merjet

 

[Krishnakumar P]

hi Merjet,
No sorting does not help as there are multiple columns and
sorting each leads to errors.Any other idea or is there a better way to
sort.
regards
Krishna

 

[Krishna]

Hi Merjet,
I wrote a code to club the same stocks together. this will
check the stock name in row 2 with row3 and look for a match and cut
and paste the result in the row added after the first
occurence,however while executing the code I get a Runtime 1004 error
any ideas to remove that.
regards
Krishna



sub macro()



Dim i As Integer

Dim j As Integer

i = 2
Do While Not IsEmpty(Worksheets("sheet1").Range("B" & i))

j = 3

Do While Not IsEmpty(Worksheets("sheet1").Range("B" & j))
If (Worksheets("sheet1").Range("B" & i)) =
Worksheets("sheet1").Range("B" & j) Then




Worksheets("sheet1").Range("B" & j).Cut

Worksheets("sheet1").Range("B" & i + 1).Insert shift:=xlDown
End If
j = j + 1
Loop
i = i + 1
Loop

end sub