Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to pivot table ?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to pivot table ?
What makes Pivot to not be the obvious choice? What don't you like
about it? On Mar 14, 12:47*pm, darkblue wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to pivot table ?
On Mar 14, 3:39*pm, AB wrote:
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to pivot table ?
On Mar 14, 7:47*am, darkblue wrote:
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." |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to pivot table ?
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. On Mar 14, 1:47*pm, darkblue wrote: On Mar 14, 3:39*pm, AB wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to pivot table ?
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to pivot table ?
On Mar 14, 2:36*pm, darkblue wrote:
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.... |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to pivot table ?
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to pivot table ?
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Pivot Tables: Alternative to PivotSelect? | Excel Programming | |||
Alternative way to change data in Pivot Table | Excel Programming | |||
Macro as alternative to limitations of data table... | Excel Programming | |||
Help required with setting up a pivot table with the source on sheet1 to have the pivot table created on sheet called "report" | Excel Programming |