Aggregate text data into table

[Sirod]

Hello,

I want to create an aggregate data table (similar to a pivot table result) that shows text values, not numeric. I have a number of rows of data that show an individual ID number, name, district, and grade - see sample table below:

ID # Name District Grade 12301 John Smith North A 12304 Jane Doe West C 13568 Robert Smith West B 23568 Bob Doe East B 87567 Jane Smith South D <colgroup><col width="64" style="width: 48pt;"> <col width="83" style="width: 62pt; mso-width-source: userset; mso-width-alt: 3035;"> <col width="64" style="width: 48pt;" span="2"> <tbody> </tbody>
I need to display this data so that the ID and name of each person is listed at the intersection their District and Grade. For example: North East South West A 12301 John Smith B 23568 Bob Doe 13568 Robert Smith C 12304 Jane Doe D 87567 Jane Smith <colgroup><col width="64" style="width: 48pt;"> <col width="120" style="width: 90pt; mso-width-source: userset; mso-width-alt: 4388;" span="4"> <tbody> </tbody>
No VBA solutions please, as I am not familiar with it. Thanks.

<colgroup><col width="64" style="width: 48pt;"> <col width="83" style="width: 62pt; mso-width-source: userset; mso-width-alt: 3035;"> <col width="64" style="width: 48pt;" span="2"> <tbody> </tbody>

 

[DRSteele]

That's a tough task with formulas. PowerQuery makes quick work of it, though. I hope this helps you, and I'll post it here for everyone's benefit.

Here is the M code for our Table1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID #", Int64.Type}, {"Name", type text}, {"District", type text}, {"Grade", type text}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"ID #", type text}}, "en-CA"),{"ID #", "Name"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[District]), "District", "Merged")
in
    #"Pivoted Column"

 

[Fazza]

Without formulas or code, this can be done with a query (table). Definition,

TRANSFORM MAX([ID #] & ' ' & Name)
SELECT Grade
FROM [YourWorksheetName$]
GROUP BY Grade
PIVOT District

should be ok in all excel versions since Excel 95

 

[Fazza]

FYI, old thread has more description of how to implement, and links to other similar old threads
https://www.mrexcel.com/forum/excel-questions/396182-pivot-table-text-values-2.html