Previous Topic Next Topic

[Excel] Macro on Grouping

Closed
justoneG Posts 3 Registration date Thursday December 15, 2011 Status Member Last seen February 11, 2012 - Feb 9, 2012 at 11:29 AM
 justone - Mar 4, 2012 at 12:43 PM
Hello,

I need help on developing a macro on grouping a set of negative amount & quantity against a set of positive amount & quantity. Using positive as base.

Example of raw data:

Amount Quantity
1223 2
-1222 2
1224 2
-1222 2
1223 2
-1222 2

Example of output :

Amount Quantity
1223 4
-1222 4
1224 2
-1222 2


Related:

5 replies

Answer 1 / 5
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Feb 9, 2012 at 10:03 PM
in the result there are two 1222.is it typo error

data is like this form A1 down and across

hdng1 hdng2
1223 2
-1222 2
1224 2
-1222 2
1223 2
-1222 2 

Sub test()
Dim ra As Range, rdata As Range, filt As Range
Dim runique As Range, cunique As Range, j As Long
Set ra = Range(Range("A1"), Range("a1").End(xlDown))
Set rdata = Range("A1").CurrentRegion
Set runique = Range("A1").End(xlDown).Offset(5, 0)
ra.AdvancedFilter xlFilterCopy, , runique, True
Set runique = Range(runique.Offset(1, 0), runique.End(xlDown))
For Each cunique In runique
rdata.AutoFilter field:=1, Criteria1:=cunique.Value
Set filt = rdata.SpecialCells(xlCellTypeVisible)
j = WorksheetFunction.Subtotal(9, rdata.Columns("B:B"))
'MsgBox j
cunique.Offset(0, 1) = j
ActiveSheet.AutoFilterMode = False
Next cunique

End Sub



Sub undo()
Range(Range("A1").End(xlDown).Offset(1, 0), Cells(Rows.Count, "A")).EntireRow.Delete
End Sub
0
Answer 2 / 5
justoneG Posts 3 Registration date Thursday December 15, 2011 Status Member Last seen February 11, 2012
Feb 10, 2012 at 12:21 PM
Hi,

Thanks for the reply.
Yes, there were 2 "-1222" and it was not typo.
You can assume that the "-1222" is the price.
The main objective is to group all the similar positive amount with the negative amount attached. Separated by the positive amount as per the "output". The positive amount will be the base and in ascending order.

Hope I don't confuse.
Thanks
0
Answer 3 / 5
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Feb 10, 2012 at 08:12 PM
these are only 1222 positive and negative. are there other numbers like this in the input? if so give two more examples
0
Answer 4 / 5
justoneG Posts 3 Registration date Thursday December 15, 2011 Status Member Last seen February 11, 2012
Feb 11, 2012 at 03:02 AM
the inputs will be paste in a blank sheet with a macro assigned button.

let's try make it this way,
positive represents cost
negative is the sales price
the quantity will be the same but in opposite direction to reflect the sales position

Example 1:

1222 2
-1224 -2
1222 1
-1223 -1
1223 4
-1225 -4
1222 3
-1224 -3

Output 1:

1222 1
-1223 -1
1222 5
-1224 -5
1223 4
-1225 -4

Example 2:

34 1
-32 -1
34 1
-33 -1
33 5
-32 -5
34 6
-31 -6
34 2
-32 -2
33 1
-32 -1

Output 2:

33 6
-32 -6
34 6
-31 -6
34 3
-32 -3
34 1
-33 -1

With this macro we are able to calculate exact amount of the proceeds for the cost against the sales price.

Thanks
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 5
justone
Mar 4, 2012 at 12:43 PM
Hi,

Any updates?

Thank you.
0