I would say the above grouping should be on the way to how i need the data to be presented. I need help from the excel gurus in this form to essentially transform the above data into the following format.
For the first data as an example, AAC info should be transformed into
16158174 16158253 4972
identifier of postive qty identifier of negative qty qty
The above example shows identifier where it can be linked as both qty is the same and it's just the sign that's different.
It becomes trickier when there are multiple lines for that stock code but when you total the qty, it adds up to zero due to positive and negative cancelling each other. One of the example above which illustrates this is:
In this instance, i need first of all, to find which qty is the maximum. The sign in front of the number does not make a difference. That is -85,389 and 85,389 are treated to be the same. And the above should be transformed into:
16157967 16157178 58530
16157967 16158235 26859
16157967 is the identifier of 85,389 and in this scenario is the maximum value relative to the other 2 quantity and hence that identifier has to be put in the left hand side column whilst the remaining identifier has to be put on the right hand column.
There are instances also where the subtotal does not equal to zero, just like the example below:
in this instance, i need the data to be transformed into:
16157064 16157257 455
we will ignore the identifier for 86.
not sure if this is possible. Not 100% sure on how to write the code as firstly, I'm not very good with vba and secondly i couldn't find any info on the net which show how to do this sort of sorting using vba. I have also attached a copy of the data on a spreadsheet.
I have no problems writing simple vba to do simple stuff but clearly the above exercise is way out of my league. What troubles me the most is that you have to write a code which will first check to see if how many rows are there for each stock code, then another code which essentially find the max qty for each stock code and finally rearrange the data into the way it needs to be presented for my purpose.
so any help would be deeply appreciated. thanks beforehand.
in the spreadsheet attached, the identifier is in column D. Column B is a stock code. Apology for not putting in the header. However, in the spreadsheet, Column A is A/C, Column B is Stock Code, Column C is the qty where positive is a buy and negative is a sell and lastly, Column D is the unique identifier for that particular buy or sell trade and will never be repeated.
I need the code to first of all recognise that in the data, they will be multiple groups. That is, in my example, AAC should be classified as 1 group, AAX should be another, so on and so forth.
Once it has done that, the code should then do a MAX() to find which qty is the greatest, ignoring the sign at the front.
Finally, re-arrange the data so that, e.g.
1st identifier, say paste in Column A, would be the identifier which relates to that stock code which has the maximum qty. 2nd identifier, say in Column D, should relate to the opposite quantity of 1st identifier. and Finally, say Column F, would be the qty in absolute value.
In its simplest form,
Identifier 1 Identifier 2 Qty
this could relate to this could relate to absolute value of the qty identified
positive or negative qty positive or negative qty
There is definitely a posibility whereby a subset of values does not equal to zero. In that instance, if there's a positive and quantitive qty for that code, the minimum of the two qty in absolute terms.
e.g. in the attachment, if you scroll down to code MGR (Column B), you would find the below data:
There is no way where you can find a subset which would equal to zero. However, i need to code to sort to become the following as there is a certain qty which can be contra off each other:
16158196 16157552 104051
It's important that one identifer relate to a positive qty and the other negtive qty. if a subset only has positive qty, then ignore. Likewise, if there's only negative qty, then ignore too. Only when there is a positive qty and negative qty for that stock code should the sorting then apply.
But StephenR, you have definitely got the drift. Hopefully, the above explanation should make it clearer rather than confuse you. Also, this report is generated on a daily basis and hence the list is dynamic. It maybe long 1 day and quite short on another.
But the below is what I have compiled. Of course, these are codes which I have searched through google and improvise on to suit my need.
PHP Code:
Sub Contra()
Dim LRow As Integer Dim LColARange As String Dim LContinue As Boolean Dim rng As Range Dim rng1 As Range Dim last As Long Dim last1 As Long Dim WSD As Worksheet Set WSD = ActiveSheet Dim PRange As Range Dim finalRow As Long Dim finalCol As Long Dim lngRows As Long Dim lngRows1 As Long
'check sheet1's name If ActiveSheet.name <> "Sheet1" Then ActiveSheet.name = "Sheet1"
'check if Sheet2 exist Set sht = Worksheets("Sheet2") If sht Is Nothing Then Worksheets.Add: ActiveSheet.name = "Sheet2"
'Loop through all column B values until a blank cell is found or value does not ' match cell B1's value While LContinue = True LRow = LRow + 1 LColARange = "B" & CStr(LRow)
'Found a blank cell, do not continue If Len(Range(LColARange).Value) = 0 Then LContinue = False End If
'Found first occurrence that did not match cell B1's value, do not continue If Range("B1").Value <> Range(LColARange).Value Then LContinue = False End If
Wend
'Copy data from columns A - G Range("A1:G" & CStr(LRow - 1)).Select Selection.Cut
'Paste results to cell A1 in Sheet2 Sheets("Sheet2").Select Range("A1").Select ActiveSheet.Paste
'Processing Tranposing If Range("A1").Value = "" Then
Range("H1").Select
Do
If IsEmpty(ActiveCell) Then ActiveCell.FormulaR1C1 = "=R[-1]C" End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
MsgBox "Tranposing is now complete!"
Exit Sub
Else
If Range("G1").Value = 2 Then ActiveSheet.Range("E1:E2").Select Selection.Copy
If Range("H1").Value = "" Then Range("H1").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Application.CutCopyMode = False
Thanks for posting that. To make code quicker as well as more elegant you can generally remove a lot of the Select statements thrown up my the macro recorder. For example, this
Good thinking. I'll remember next time. I didn't really use much macro recorder for the code. I wrote as I think how it will happen hence the similarity with the macro recorder. I'll give your suggestion a try.
Thanks once again.
Just a quick question, do you know if there's any VBA code that will allow me to limit the time on code execution. That is, i want the macro to use only e.g. 5 minutes to run, any longer than that, it will abort and resume next.
Just a quick question, do you know if there's any VBA code that will allow me to limit the time on code execution. That is, i want the macro to use only e.g. 5 minutes to run, any longer than that, it will abort and resume next.
As Stephen mentioned, your code can be optimised a lot by removing the ".select" and various other approaches. I think that someone will probably be able to get optimise your code & get the run time down to no more than a minute or two for everything to be done therefore removing the need for time limitations...
Can you please post the latest sample file with your code?
(If you are interested in more help, it may be best to remove the "solved" prefix from your post.)
Rob
Rob Brockett
Kiwi in the UK
Always learning & the best way to learn is to experience...
Perhaps I shouldn't asked that question in this post given the fact that I've started a new post for that question. My apology. I have updated all the relevant info in the new post. It'd be much appreciated if you could shed some light.
Thanks.
Last edited by bgunawan; 07-09-2009 at 01:01 AM.
Reason: reopen post in correct section
LinkBack URL
About LinkBacks
sorting and tranposing of data
Register To Reply
Re: sorting and tranposing of data
Bookmarks