VBA user defined function result to spill in two columns

[dipkchopra]

Hi all,
I am trying to create a user defined function using VBA.
The goal is to create a formula based pivot out of two columns - one containing names and the other containing amounts.
The formula should throw out unique list of names in first column and sum of those names in second column


I tried creating Function Sumpivot(getnames As Range, getamounts As Range)

Code to get first column of my answer = Application.Unique(getnames)
and code for second column = Application.SumIfs(getamounts, getnames, Application.Unique(getnames))
I tried - sumpivot = Application.Unique(getnames) & Application.SumIfs(getamounts, getnames, Application.Unique(getnames)) - this does not work
My problem is how do I load both of these in one output?


This is what I am trying to achieve:

DATA:
C	1​
B	2​
B	3​
A	4​
A	1​
A	2​
Output
C	1​
B	5​
A	7​

Please guide
and thanks in advance.

 

[Fluff]

You can do it like

Function Sumpivot(getnames As Range, getamounts As Range) As Variant
   Dim a, b
   a = Application.Unique(getnames)
   b = Application.SumIfs(getamounts, getnames, a)
   Sumpivot = Application.Choose(Array(1, 2), a, b)
End Function

but why not just use a formula, it will be quicker.

 

[dipkchopra]

You can do it like

Function Sumpivot(getnames As Range, getamounts As Range) As Variant
   Dim a, b
   a = Application.Unique(getnames)
   b = Application.SumIfs(getamounts, getnames, a)
   Sumpivot = Application.Choose(Array(1, 2), a, b)
End Function

but why not just use a formula, it will be quicker.

yes but if I have to do this over and over again, UDF will be quicker and easier

 

[Fluff]

Did my suggestion work?

 

[dipkchopra]

Did my suggestion work?

Oh yes - that worked Thank you very much....
Sorry missed the solution in the previous response of yours and only saw the comment

Thank you once again.

 

[Fluff]

You're welcome & thanks for the feedback.