[Excel] Macro on Grouping
Closed
justoneG
Posts
3
Registration date
Thursday 15 December 2011
Status
Member
Last seen
11 February 2012
-
9 Feb 2012 à 11:29
justone - 4 Mar 2012 à 12:43
justone - 4 Mar 2012 à 12:43
Related:
- [Excel] Macro on Grouping
- Excel mod apk for pc - Download - Spreadsheets
- Excel online macros - Guide
- Excel run macro on open - Guide
- Vat calculation excel - Guide
- Kernel for excel repair - Download - Backup and recovery
5 responses
venkat1926
Posts
1863
Registration date
Sunday 14 June 2009
Status
Contributor
Last seen
7 August 2021
811
9 Feb 2012 à 22:03
9 Feb 2012 à 22:03
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
Posts
3
Registration date
Thursday 15 December 2011
Status
Member
Last seen
11 February 2012
10 Feb 2012 à 12:21
10 Feb 2012 à 12:21
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
Posts
1863
Registration date
Sunday 14 June 2009
Status
Contributor
Last seen
7 August 2021
811
10 Feb 2012 à 20:12
10 Feb 2012 à 20:12
these are only 1222 positive and negative. are there other numbers like this in the input? if so give two more examples
justoneG
Posts
3
Registration date
Thursday 15 December 2011
Status
Member
Last seen
11 February 2012
11 Feb 2012 à 03:02
11 Feb 2012 à 03:02
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
