I have been very busy with VBA Classes background work. Today, I want to quickly share a few things about the upcoming VBA Classes.
I have been running online training programs since Jan, 2010. I have trained more than 900 students till date. Still, whenever I am launching a new program, I could feel that familiar sense of eagerness, tension and tremendous enthusiasm building up. I feel eager because I want to meet you, teach you and learn from you. I feel tensed because I want to do it right. I feel enthusiastic because these training programs give me a lot of new ideas and open-up new possibilities.
1. Can we learn about Excel Dashboards too?
Of course yes. After receiving at least a dozen emails, I am now adding a 4th option to VBA Classes. You can get everything in Excel School + Dashboard Lessons apart from VBA Classes – all in one place. I think this is a great value for money as you will get 50 hours of lessons + at least 75 workbooks with lots of examples, tips & ideas.
Please look at the below table to understand various VBA School options: 
2. How much Excel (and VBA) should I know to join this course?
This program is not aimed at Excel Newbies. But if you know what a spreadsheet is, can create a simple formula and chart without choking yourself, then you are a good candidate for this course.
If you do not know much about Excel (ie not familiar with concepts like conditional formatting, pivot tables, charts, formulas beyond SUM & AVERAGE…) then you should consider joining VBA Classes along with Excel School. This way, you can learn various Excel Topics before plunging in to VBA.
3. What if I get busy after joining and could not attend a week or two?
No problemo!
Every week, we will be posting new lessons to the classroom area. You can visit the online classroom once a week (or two) and finish the lessons. If you are away for a few weeks, you can always catch-up. Moreover, you can also download the lesson videos and watch them at leisure.
4. Any Discounts for my team?
Yes, you can get 25% discount if you enroll 3 or more people in to the program. During checkout, enter the quantity to get the discount applied automatically.
5. Can I pay by Bank transfer or in Indian Rupee?
Yes. Send me an email at chandoo.d @ gmail.com and I will give you my bank account details.
Also, We have special pricing for you if you chose to pay in Indian Rupee (this is because, I get to save on Credit Card processing charges & other commissions). Refer to the table in (1) to know how much to pay.
Just visit http://chandoo.org/wp/vba-classes/inr-pricing/ (after 8th May) or drop me an email for details about my bank account details.
A Demo Lesson:
Well, this not entirely a demo lesson, more like a VBA Example that I created to answer a question one of the blog reader’s asked. In this 4 minute video, you can learn how to write a simple 1 line macro to change date format of selecte cell(s).
See it below (or here)
Doubts or Questions about VBA Classes?
Please send me an email at chandoo.d@gmail.com or call me at +1 206 792 9480 +91 814 262 1090. I will be glad to help you out.
Remember: Our VBA Class‘ first batch registrations open on Monday – 9th of May.
13 Responses to “Using pivot tables to find out non performing customers”
To avoid the helper column and the macro, I would transpose the data into the format shown above (Name, Year, Sales). Now I can show more than one year, I can summarize - I can do many more things with it. ASAP Utilities (http://www.asap-utilities.com) has a new experimental feature that can easily transpose the table into the correct format. Much easier in my opinion.
David
Of course with alternative data structure, we can easily setup a slicer based solution so that everything works like clockwork with even less work.
David, I was just about to post the same!
In Contextures site, I remember there's a post on how to do that. Clearly, the way data is layed out on the very beginning is critical to get the best results, and even you may thinkg the original layout is the best way, it is clearly not. And that kind of mistakes are the ones I love ! because it teaches and trains you to avoid them, and how to think on the data structure the next time.
Eventually, you get to that place when you "see" the structure on the moment the client tells you the request, and then, you realized you had an ephiphany, that glorious moment when data is no longer a mistery to you!!!
Rgds,
Chandoo,
If the goal is to see the list of customers who have not business from yearX, I would change the helper column formula to :
=IF(selYear="all",sum(C4:M4),sum(offset(C4:M4,,selyear-2002,1,columns(C4:M4)-selyear+2002)))This formula will sum the sales from Selected Year to 2012.
JMarc
If you are already using a helper column and the combox box runs a macro after it changes, why not just adjust the macro and filter the source data?
Regards
I gotta say, it seems like you are giving 10 answers to 10 questions when your client REALLY wants to know is: "What is the last year "this" customer row had a non-zero Sales QTY?... You're missing the forest for the trees...
Change the helper column to:
=IFERROR(INDEX(tblSales[[#Headers],[Customer name]:[Sales 2012]],0,MATCH(9.99999999999999E+307,tblSales[[#This Row],[Customer name]:[Sales 2012]],1)),"NO SALES")
And yes, since I'm matching off of them for value, I would change the headers to straight "2002" instead of "Sales 2002" but you sort the table on the helper column and then and there you can answer all of your questions.
Hi thanks for this. Just can't figure out how you get the combo box to control the pivot table. Can you please advise?
Cheers
@Kevin.. You are welcome. To insert a combo box, go to Developer ribbon > Insert > form controls > combo box.
For more on various form controls and how to use them, please read this: http://chandoo.org/wp/2011/03/30/form-controls/
Thanks Chandoo. But I know how to insert a combobox, I was more referring to how does in control the year in the pivot table? Or is this obvious? I note that if I select the Selected Year from the PivotTable Field List it says "the field has no itens" whereas this would normally allow you to change the year??
Thanks again
worked it out thanks...
when =data!Q2 changes it changes the value in column N:N and then when you do a refreshall the pivottable vlaues get updated
Still not sure why PivotTable Field List says “the field has no itens"?? I created my own pivot table and could not repeat that.
Hi, I put the sales data in range(F5:P19) and added a column D with the title 'Last sales in year'. After that, in column D for each customer, the simple formula
=2000+MATCH(1000000,E5:P5)
will provide the last year in which that particular customer had any sales, which can than easily be managed by autofilter.
Somewhat longer but perhaps a bit more solid (with the column titles in row 4):
=RIGHT(INDEX($F$4:$P$19,1,MATCH(1000000,F5:P5)),4)
[…] Finding non-performing customers using Pivot Tables […]