Alternative to pivot table ?

[darkblue]

Column A: Sales Person
Column B: Item
Column C: Amount

Sample data is like (Max number of rows: 5000)

John, Item1, 2000
George, Item4, 800
Anna, Item2, 340
Adam, Item7, 960
John, Item2, 200

I know i can easily apply a pivot table to analyse the sales
as i am currently doing. But i was wondering if i can do
it by vba code. I know how to pull the unique names out of the list.
And the rest seems to be looping and summing. Maybe you guys are
using a different method that is why i am asking.Or should i stick to
pivot ?
Thank you in advance.

 

[AB]

What makes Pivot to not be the obvious choice? What don't you like
about it?

 

[darkblue]

What makes Pivot to not be the obvious choice? What don't you like
about it?

Borders, for one
Coloring and formatting, for two
Having to use supporting functions like:
Refreshing, deleting missing items, sorting, disabling/enabling etc.

Rgds

 

[Donald Guillett]

Column A: Sales Person
Column B: Item
Column C: Amount

Sample data is like (Max number of rows: 5000)

John,     Item1, 2000
George, Item4,   800
Anna,    Item2,   340
Adam,   Item7,   960
John,     Item2,   200

I know i can easily apply a pivot table to analyse the sales
as i am currently doing. But i was wondering if i can do
it by vba code. I know how to pull the unique names out of the list.
And the rest seems to be looping and summing. Maybe you guys are
using a different method that is why i am asking.Or should i stick to
pivot ?
Thank you in advance.

I am one who does NOT like pivot tables. You can use SUMPRODUCT
formulas or vba to do the same thing

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[AB]

For what it's worth, my considerations:
- As per Don, you can use Sumproduct. You'd need to write slightly
more sophisticated formulas than usual and you might get a performance
hit. But in this case it's all real time once the formula has been
written.
- You could still use the benefits of pivot table by using the
GetPivotDate formula. You still need to have a pivot somewhere (hidden
sheet or something) that needs to be refreshed but you'll have your
own formatting as desired. The formula is more user friendly than
Sumproduct.
- You can do it via VBA but then you'll need to run it on regular
basis anyway.

 

[darkblue]

Thank you AB. I think i'd better stick to my current method.
Hide the colums where the pivot table resides on and move everything
somewhere else.
And thank you Don for your kind offer.
Rgds

 

[Donald Guillett]

Thank you AB. I think i'd better stick to my current method.
Hide the colums where the pivot table resides on and move everything
somewhere else.
And thank you Don for your kind offer.
Rgds

Let's see you ask for solutions that you doin't use. As long as your
are happy....

 

[darkblue]

Hi Don

As i said earlier i can pull the unique names of the sales person
from the data and put it on column A starting from row 2.
But what about items ? How can i put the unique item names
on row 1 starting from colum B ? Surely i wouldn't know the number of
items, would i ?
Like:
item1, item2, item3
John
George
Adam

Regards

 

[darkblue]

I found it - in case someone else needs it:

Sub MoveUniqueNamesHorizontally()
Dim X As Long
Dim Z As Long
Dim UniqueNames As String
UniqueNames = "*"
Z = 1
With Worksheets("Sheet1")
For X = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
If InStr(UniqueNames, "*" & .Cells(X, "A").Value & "*") = 0 Then
UniqueNames = UniqueNames & .Cells(X, "A").Value & "*"
Worksheets("Sheet2").Cells(1, Z).Value = .Cells(X, "A").Value
Z = Z + 1
End If
Next
End With
End Sub


Sub MoveUniqueNamesVertically()
Dim X As Long
Dim Z As Long
Dim UniqueNames As String
UniqueNames = "*"
Z = 1
With Worksheets("Sheet1")
For X = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
If InStr(UniqueNames, "*" & .Cells(X, "A").Value & "*") = 0 Then
UniqueNames = UniqueNames & .Cells(X, "A").Value & "*"
Worksheets("Sheet2").Cells(Z, "A").Value = .Cells(X,
"A").Value
Z = Z + 1
End If
Next
End With
End Sub

Many thanks Rick.