[Excel] Macro on Grouping
Closed
justoneG
justone - Mar 4, 2012 at 12:43 PM
- Posts
- 3
- Registration date
- Thursday December 15, 2011
- Status
- Member
- Last seen
- February 11, 2012
justone - Mar 4, 2012 at 12:43 PM
Related:
- [Excel] Macro on Grouping
- Excel macro to create new sheet based on value in cells - Guide
- Excel macro auto increment number - Guide
- Excel macro create new workbook and copy data ✓ - Forum - Excel
- Excel macro compare two sheets and highlight differences ✓ - Forum - Excel
- Excel macro to create new workbook based on value in cells - Forum - Excel
5 replies
venkat1926
Feb 9, 2012 at 10:03 PM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
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
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
justoneG
Feb 10, 2012 at 12:21 PM
- 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
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
venkat1926
Feb 10, 2012 at 08:12 PM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
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
justoneG
Feb 11, 2012 at 03:02 AM
- 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
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
Didn't find the answer you are looking for?
Ask a question