Excel Forum

Report on multiple entries (bad wording)

ron S

New Member

Members

Forum Posts: 2

Member Since:
February 24, 2016

Offline

February 11, 2020 - 11:44 am

We have data that looks like this

First	Last	Prior degrees earned before Doctorate
Jim	Adams	MBA,BA
Helena	Adams	BS,MBA
Victoria	Ashworth	JD
Anabela	Domingues	MBA,CPA,BS
Martin	Donnell	JD,BS,CPA
Scott	Johnson	BS,CPA
Mary	Jones	CPA,MBA
Matti	Karttunen	CPA,BS
Martin	Kotas	BS,CPA
Thomas	Neipper	MBA
Lucy	Reynolds	CPA,BS,JD
Greg	Robinson	BA
Mary	Saveley	MBA,CPA
John	Sergienko	MBA,CPA,JD,BS,BA
Bernard	Truman	BA,CPA
Andrew	Zare	MBA,BA

We want to be able generate reports listing people who have 2 or more of the Degrees in the 3rd column by selecting the specific combinations of degrees

(I will be concatenating the first and last names)

I unpivoted the data in PQ. I tried using a slicer on the resulting table or Pivot table. But multi select slicers report using "OR" condition. Can slicers to work with "And" condition?

Is there a (easy) way to setup a dynamic report where I can select 1 or more of the degrees to filter the output?

I have a vague idea teasing me. I can create a pivot listing the degrees each person has. But I don't know how to create a UI to filter for rows that only contain values in specific columns.

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

February 11, 2020 - 2:51 pm

Hi Ron,

Assuming that you have your data in Table1, you can add the list you want to search for in a new table-Table3.

The query looks like this:

let
Degrees =Excel.CurrentWorkbook(){[Name="Table3"]}[Content][Search For],
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"First", type text}, {"Last", type text}, {"Prior degrees earned before Doctorate", type text}}),
#"Merged Columns" = Table.CombineColumns(#"Changed Type",{"First", "Last"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Grouped Rows" = Table.Group(#"Merged Columns", {"Merged"}, {{"Grouped", each _, type table [Merged=text, Prior degrees earned before Doctorate=text]}}),
#"Filled Down" = Table.TransformColumns(#"Grouped Rows",{"Grouped",each Text.Split(Text.Combine(_[Prior degrees earned before Doctorate],","),",")}),
#"Added Custom" = Table.AddColumn(#"Filled Down", "Matching Degrees", each List.Intersect({[Grouped],Degrees})),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Matching Degrees", each Text.Combine(List.Transform(_, Text.From),","), type text})
in
#"Extracted Values"