Sunday, November 2, 2014

yogi_Create Report Based On Column And Row Intersections

                 Google Spreadsheet   Post  #1817
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov-02-2014
post by  KBoyle:
(https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!category-topic/docs/spreadsheets/jjux2fCGOl4)
Create a report based on columns and row intersections (vlookup, hlookup, filter, match)
I'm working with this google sheet to create a report of high value relationships between people at our clients, and our employees. I've stripped out personally identifiable information, and much of the guts that get to the data tab. 

I have one sheet that shows data of clients and their employees in rows, and then I map them to employees at 3Pillar, with the cell intersection of row and column representing the gap in their current relationship (high, medium, low, none). I also rate each person at our client's companies in terms of their value to our relationship (are they a buyer, influencer, decision maker, etc...).

I would like to create a simple report, where I can present the most important relationships that have the biggest gaps in their relationship. i.e. select filters for importance and relationship gap, and display the results that match those criteria. 

My first choice would be to display a new row for each relationship that meets those criteria, which would require me to change the filter function in cells A7 and B7; however, it would be acceptable to show one row per name at my client's site, and then multiple columns for each employee that matches the gap value.

Can you please provide me some guidance for how to set up these formulas, and/or how to set up my data better to give me what I am looking for.  I'm happy to create hidden rows in the data table that help to make the data more usable.

Thank you
--------------------------------------------------------------------------------------------------------------------------------------