Aggregate text data into table

S

Sirod

New Member
Joined
Aug 6, 2009
Messages
47
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]
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
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
Code: 
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"
 
Last edited:
Upvote 0
Without formulas or code, this can be done with a query (table). Definition,
Code: 
TRANSFORM MAX([ID #] & ' ' & Name)
SELECT Grade
FROM [YourWorksheetName$]
GROUP BY Grade
PIVOT District
should be ok in all excel versions since Excel 95
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
649
smozgur
smozgur
L
  • Question Question
vlookup with approximate values
Replies
2
Views
846
Marcelo Branco
M
L
  • Question Question
reversing the order of 2 columns
Replies
7
Views
1K
Peter_SSs
Peter_SSs
N
  • Question Question
Un-flatten Survey Data
Replies
9
Views
2K
Peter_SSs
Peter_SSs
A
  • Question Question
Add Sequence numbers Based on Dates
Replies
1
Views
3K
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top