help with turning this forumla into a macro

[beth4nie]

Hi Everyone,

I am currently working with a huge excel spreadsheet (650000+ lines) in which I'm trying run the following formula to count the number of unique values (customer code + product brand combination):

=IF(ISNUMBER(SUM(1/COUNTIF($V$2:$V$158726,$V$2:$V$158726))),SUM(1/COUNTIF($V$2:$V$158726,$V$2:$V$158726)),"")

With one column taking.. hours to calculate (there are 9 columns altogether to calculate), I am looking to get some help to translate/put this into a macro.

Also, if I do run this formula as a macro, does it actually speed up the calculating time at all?

Any help would be greatly appreciated!

[JasperD]

Does this do what you're looking for?

Sub test()
Dim i As Integer, numu As Integer
numu = 0

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For i = 2 To UsedRange.Rows.Count
If Range("V" & i).Value = "" Then GoTo nxt
If WorksheetFunction.CountIf(Range("V:V"), Range("V" & i).Value) < 2 Then numu = numu + 1
nxt:
Next i

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

MsgBox numu & " unique values in column V"
End Sub

Please click the * below if this helps

[beth4nie]

Hi JasperD,

Thank you for your reply!

Got the chance to test the code out today, and whilst the code works, it's not 100% what I am looking for.

I'm actually wanting to pivot all the information at the very end, so instead of a pop up box with the number of unique values, I'm hoping to get the calculations visible in the next column.

I would be great to get some further advice from you!

[morevijay1982]

i am using macro for vlookuo and sumif fuction around 400000 cells it took half and hour

and you are calculating 158726 into 9 column

it will take time

or just increase RAM and PROCESSOR capacity

[JasperD]

what do you actually want in the next column then?
Just the number of times the value is in that column?

[Norie]

If you want to pivot why not use a pivot table?

Mind you with that amount of data I would probably think about using a database.

Perhaps not for the calculations but for storage at least.