combine columns from Table and displaying filtered list from data

[mbreese]

I have been searching and trying to find a solution to pulling filtered text data from cells from a pretty large table that tracks (among a bunch of other things) partners that participate in events throughout the year (there can be up to 10 partners per event, so ten possible columns to combine into one list with out duplicate items and ignoring blanks.

The data needs to end up in a dashboard. I think pivot tables is the way to go because of the filtering needs, and because new data is continually added to the table. For the life of me I can find no good way to return text in a pivot table that works. There is, however, Conditional formatting is one way I found to go (assign a value to a partner name and format to return text), but the problem is that there are always new partners coming and going and I'd have to update the conditional formatting all the time.

So, the problems I can not solve are:
1) returning text from a table using a PivotTable, which may not be possible as far as I can find
2) dynamically combining several columns with the partner names into a single column on the dashboard skiping blanks and not duplicating partner names
3) being able to filter that list on the dashboard by year and month, and getting a count of the number of unique partners

I've been working on this over several months and just can't make it happen. Has anyone done something like this before. I have a simplified workbook with a simplified mockup of the table and how it might look on the dashboard.

[kersplash]

In your sample can you show exactly what your output should be from the DataTable tab.

[mbreese]

In your sample can you show exactly what your output should be from the DataTable tab.

Thanks for replying kersplash. Do you mean in terms of formating? Or do you mean using actual real world
Data from the actual table with actual names of the partners?

[kersplash]

What I mean is from your DataTable tab data, create your expected output tables exactly how you want them to appear based on that data. You've sort of hinted at the layout without filling in the complete tables.

[mbreese]

So Ive updated the workbook file.
If by "without filling in the complete tables" you mean there are cells without data, that is how it will actually look in the wild.

I've placed the output table in the location on the "PartnersList_Dashboard_element" Tab where it will be on the final dashboard.

I have also placed an image of the full dashboard (to date anyway) on the "Dash-Picture" Tab.

I hope this is what you needed.

After I solve this problem, I have to address the section of the dashboard that outputs and filters the notes (on the right side of the dashboard).
One thing at a time however.

For a bit of background, I am the Chaplain at a non-profit agency that offers a homeless shelter and Mental Illness services.
I provide opportunities for our clients to engage in faith and spiritual development on a voluntary basis and in the faith tradition of their choice.
We serve a very diverse population and that means I need many partners to help (community groups, places of worship, other service agencies,
local businesses, etc.). I used to have 15 or so different workbooks to track all this and it was a pain and confusing to others.
So, I made one massive table to rule them all. I use the data to get more people involved and find funding. Having it all in one table will
also let me find other connections and relationships among the data. Probably more than you need to know, but there it is.

[sandy666]

is that what you want?
Done with PowerQuery & PivotTable - it doesn't work without PowerQuery
or
you need reorganise your source table and get result with PivotTable only

Power Query for

• Excel 2010 Pro Plus or Excel 2013 Pro Plus can be downloaded as free add-in from MS site (choose correct version). I suggest Power Pivot also
• Excel 2016 or Excel 365 is built-in, aka Get&Transform
• PowerQuery works with M-language and does not contain any classic formulae or vba code
• What PowerQuery is? see here: Getting Started with PowerQuery (Get&Transform) in Excel

[mbreese]

Yes, but the pivot is leaving out partner names 8,9 & 10. But I see what you are pointing to.
I had installed power query but it looked like it was not what I needed. I will go back and re-visit that as you have obviously been able to do it.
I could not play with the pivot in your uploaded file, however, because I am using 2010. Excel keeps telling me my version won't work with what you created.
I'll spend some time reading up on power query and let you know how it goes.
Thank you for pointing me in a direction that seems like it will work.