Matching contents of a pivot table with the row and column

I have a pivot table I am using to pull information from audits I have been conducting for the past few months.  Each week I filter the pivot table by area and match the row and column to determine which specific questions are the most deficient.  I am sure that there is an easier way to do this so that I don't spend so much time each week.   Ideally I would like to create some sort of code or formula that would pull the top 10 deficient questions from each area and reference its destination, for example G5 is the most deficient question followed by I4.   Here is what my pivot table looks like, currently the area is not filtered so that you can see the compiled results of every area.  Pivot-Table.png
zepoldAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Can you post your sample workbook along with results you are expecting to come out automatically as it will be helpful to design a solution for you accordingly.

Saurabh...
0
zepoldAuthor Commented:
Saurabh,

On the first tab is the data.  Every week there are reviews completed (reviews A-I) in different areas of the facility (areas 1-12).  Depending on the review there are 4-10 questions.  I want to track and pareto the top 10 deficient questions in each area (currently the filter is on "all").  If the review question results in a "no" response, then it is considered deficient.  The pivot table in tab 2 compiles all of the deficiencies with the rows as the review and the column as the questions.  I have put together a table under the pivot table to show you what I would like this automated solution to provide.  As you can see it pulls the top 15 deficient questions and matches the review with the question.  I have also listed the review questions themselves.  In tab three is the sheet I provide to each area allowing them to review and correct their top deficiencies.
0
zepoldAuthor Commented:
0
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Saurabh Singh TeotiaCommented:
Quick question you open to a macro approach for this? Or you want to do this by formula??
0
zepoldAuthor Commented:
Whatever is easier for you at this point, but yes I am open to using macros.
0
zepoldAuthor Commented:
Do you need anymore information from me?
0
Saurabh Singh TeotiaCommented:
zepold,

I'm in middle of somethings..will provide you a solution by today EOD on this..

Saurabh...
0
Saurabh Singh TeotiaCommented:
Zepold,

Here you go i wrote the udf to do what you are looking for basically the way udf works..it looks for values in the range and checks whether its the first value or the second and gets you the necessary result accordingly..

Also i made changes in your pivot table where i removed yes one entries to get the desired results..

Saurabh...
Copy-of-EEData-Pareto.xlsm
0
zepoldAuthor Commented:
Is there any way to make the results auto populate on the third tab?
0
Saurabh Singh TeotiaCommented:
You can just auto link the third sheet by equal to sign from results and it will do what you are looking for...

Saurabh...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
I've requested that this question be closed as follows:

Accepted answer: 500 points for Saurabh Singh Teotia's comment #a40803418

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
zepoldAuthor Commented:
Thank you for your patience and support.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.