• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 132
  • Last Modified:

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
0
zepold
Asked:
zepold
  • 6
  • 5
1 Solution
 
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now