Good morning all,
Firstly - first time post but I have been lurking here for years. The ozgrid forums have solved
so, so many of my problems over the years and have really taken me from knowing nothing about VBA to being semi-competent.
Thanks to all users, admins and anybody else involved.
Ok, to my problem.
Goal - to obtain the "Top 20"
from a Pivot table, sourced from multiple worksheets (2011 data, 2012 data).
Problem - I cannot manipulate the
source data in each worksheet, and a Pivot Table with multiple sources seems to automatically define the row label as the
left-most value in my individual worksheets. The data I wish to have as a row label is in fact contained in column G of each
Essentially I am looking to return the Top 20 clients by revenue earned - with client name in column G,
and revenue earned in column P. Column A, currently being used as the row labels, contains "2011" and "2012" respectively.
What would be some other options to emulate this feature?
I have considered temporarily combining both
worksheets, creating a pivot on the combined data, extracting the top 20, then deleting the new worksheet & pivot. It will
likely work....but seems inelegant and cumbersome.
Also possibly relevant to that point - 2011 data contains
headers then data in rows 1 & 2. 2012 data contains a criteria table in rows 1-3 with the matching headers/data commencing
rows 4 & 5.
I have also considered using VBA to re-order the columns so that the client name column moves to
column A, but again this seems like a lot of work and I figure there is an easier solution.
I also considered
array formulas but wrote them off due to the sheer number of unique client names.
Any advice or direction would be