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:

[TABLE="width: 206"]
<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>[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"][TABLE="width: 206"]
<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>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]ID #[/TD]
[TD="class: xl65, width: 83, bgcolor: transparent"]Name[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]District[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Grade[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]12301[/TD]
[TD="class: xl68, bgcolor: transparent"]John Smith[/TD]
[TD="class: xl67, bgcolor: transparent"]North[/TD]
[TD="class: xl67, bgcolor: transparent"]A[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]12304[/TD]
[TD="class: xl68, bgcolor: transparent"]Jane Doe[/TD]
[TD="class: xl67, bgcolor: transparent"]West[/TD]
[TD="class: xl67, bgcolor: transparent"]C[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]13568[/TD]
[TD="class: xl68, bgcolor: transparent"]Robert Smith[/TD]
[TD="class: xl67, bgcolor: transparent"]West[/TD]
[TD="class: xl67, bgcolor: transparent"]B[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]23568[/TD]
[TD="class: xl68, bgcolor: transparent"]Bob Doe[/TD]
[TD="class: xl67, bgcolor: transparent"]East[/TD]
[TD="class: xl67, bgcolor: transparent"]B[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]87567[/TD]
[TD="class: xl68, bgcolor: transparent"]Jane Smith[/TD]
[TD="class: xl67, bgcolor: transparent"]South[/TD]
[TD="class: xl67, bgcolor: transparent"]D[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl64, width: 83, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]
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:

[TABLE="width: 408"]
<colgroup><col width="64" style="width: 48pt;"> <col width="120" style="width: 90pt; mso-width-source: userset; mso-width-alt: 4388;" span="4"> <tbody>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 120, bgcolor: transparent"]North[/TD]
[TD="class: xl65, width: 120, bgcolor: transparent"]East[/TD]
[TD="class: xl65, width: 120, bgcolor: transparent"]South[/TD]
[TD="class: xl65, width: 120, bgcolor: transparent"]West[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]A[/TD]
[TD="class: xl69, bgcolor: transparent"]12301 John Smith[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]B[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]23568 Bob Doe[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]13568 Robert Smith[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]C[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]12304 Jane Doe[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]D[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]87567 Jane Smith[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]No VBA solutions please, as I am not familiar with it.

Thanks.[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[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