Sum Column B based on Column A and format it

[Danny69]

Hello,

I need help to modifying this script, wherein i am getting the results but want them to formatted adding 2 decimal places and number #,##0.00



Sub doIt()


Dim data As Variant
Dim i As Long
Dim countDict As Variant
Dim category As Variant
Dim value As Variant


Set countDict = CreateObject("Scripting.Dictionary")


data = ActiveSheet.UsedRange 'Assumes data is in columns A/B


'Populate the dictionary: key = category / Item = count
For i = LBound(data, 1) To UBound(data, 1)
category = data(i, 1)
value = data(i, 2)
If countDict.exists(category) Then
countDict(category) = countDict(category) + value 'if we have already seen that category, add to the total
Else
countDict(category) = value 'first time we find that category, create it
End If
Next i


'Copy dictionary into an array
ReDim data(1 To countDict.Count, 1 To 2) As Variant


Dim d As Variant
i = 1
For Each d In countDict
data(i, 1) = d
data(i, 2) = countDict(d)
i = i + 1
Next d


'Puts the result back in the sheet in column D/E, including headers
With ActiveSheet
.Range("D1").Resize(UBound(data, 1), UBound(data, 2)) = data
End With


End Sub






Product
Quotation


1
4900000


1
4900000


1
100000


1
300000000


1
6000000


2
1600000


2
20000000


2
1600000


2
32879400




Result:




Product
Row Occurrence
Quotation



1
5
315900000
Result 315900000 will be converted to 31,59,000.00


2
4
56079400
Result 56079400 will be converted to 5,60,794.00




Is it possible for Each cycle to retrieve also the number of occurrences?
Example:
Product # 1 have 5 occurrences
Product # 2 have 4 occurrences

Sample File attached.

[p45cal]

Your macro does not give the same results as your sheet1; the macro puts quotation values in which are 100 times the values in your sheet1, so I'm not sure what to do.
Try:
Sub doIt()

Dim data As Variant
Dim i As Long
Dim countDict As Variant
Dim category As Variant
Dim value As Variant

Set countDict = CreateObject("Scripting.Dictionary")

data = ActiveSheet.UsedRange 'Assumes data is in columns A/B
Set origDataLocn = ActiveSheet.UsedRange.Columns("A:B")
'Populate the dictionary: key = category / Item = count
For i = LBound(data, 1) To UBound(data, 1)
category = data(i, 1)
value = data(i, 2) ' / 100 'remove the /100 if you don't want to divide by 100.
If countDict.exists(category) Then
countDict(category) = countDict(category) + value 'if we have already seen that category, add to the total
Else
countDict(category) = value 'first time we find that category, create it
End If
Next i

'Copy dictionary into an array
ReDim data(1 To countDict.Count, 1 To 2) As Variant

Dim d As Variant
i = 1
For Each d In countDict
data(i, 1) = d
data(i, 2) = countDict(d)
i = i + 1
Next d

'Puts the result back in the sheet in column D/E, including headers
With ActiveSheet
.Range("D1").Resize(UBound(data, 1), UBound(data, 2)) = data
.Range("F1").value = "Count"
With .Range("D1").Offset(1, 2).Resize(UBound(data, 1) - 1)
.FormulaR1C1 = "=COUNTIF(" & origDataLocn.Address(True, True, xlR1C1) & ",RC[-2])"
.value = .value
With .Offset(, -1)

'remove or comment-out the following 3 lines if you don't want the result divided by 100
For Each cll In .Cells
cll.value = cll.value / 100
Next cll

.NumberFormat = "[>99999]##\,##\,##0.00;[<-99999.99]-##\,##\,##0.00;##,##0.00"
End With
End With
End With
End Sub


All you're doing is what a pivot table does, and much less robustly.
In the attached is a sheet called Macro with a button which calls the macro above. There are comments in the code about the 100 times difference.
There's also a sheet called Pivot where I've added a pivot table, which can summarise the data including, if you want, grand totals and much more.

You'll see in the macro I've applied a custom number format to the Quotation values:
[>99999]##\,##\,##0.00;[<-99999.99]-##\,##\,##0.00;##,##0.00
Do check that it works with the range of numbers you expect to be working with (negative, very large etc.). I also added this format to the values in the pivot table.

You may find this page useful:
http://chandoo.org/wp/2010/07/26/indian-currency-format-excel/

[Danny69]

Hello,

Thanks p45cal your Marco has perfect answer.

Thanks a lot.