Unstack, Flatten or Compress data into a single row per employee

B

Bill_Kro

New Member
Joined
Oct 28, 2017
Messages
14
I have a download from by HR system but I'm having problems getting the data into a format for the report I have to generate.

I want a report that looks like the following...
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Employee Name
[/TD]
[TD]Medical
[/TD]
[TD]Dental
[/TD]
[TD]Vision
[/TD]
[TD]Vision Pay
[/TD]
[TD]Flex
[/TD]
[/TR]
[TR]
[TD]Emp1
[/TD]
[TD]MedBasicFamily
[/TD]
[TD]DenBasicFam
[/TD]
[TD]Vision
[/TD]
[TD]$18.00
[/TD]
[TD]Flex
[/TD]
[/TR]
[TR]
[TD]Emp2
[/TD]
[TD]MedBasicSingle
[/TD]
[TD][/TD]
[TD]Vision
[/TD]
[TD]$8.50
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]






The data that I have looks like this...
[TABLE="class: grid, width: 500, align: left"]
<tbody></tbody>[/TABLE]
[TABLE="width: 1091"]
<colgroup><col width="209"><col width="198" span="3"><col width="90"><col width="198"></colgroup><tbody>[TR]
[TD="class: xl64, width: 209"][TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Employee Name
[/TD]
[TD]Medical
[/TD]
[TD]Dental
[/TD]
[TD]Vision
[/TD]
[TD]Vision Pay
[/TD]
[TD]Flex
[/TD]
[/TR]
[TR]
[TD]Emp1
[/TD]
[TD][/TD]
[TD]DenBasicFam
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Emp1
[/TD]
[TD]MedBasicFamily
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Emp1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Vision
[/TD]
[TD]$18.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Emp1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Flex
[/TD]
[/TR]
[TR]
[TD]Emp2
[/TD]
[TD]MedBasicSingle
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Emp2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Vision
[/TD]
[TD]$8.50
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




[/TD]
[TD="class: xl66, width: 198"][/TD]
[TD="class: xl66, width: 198"][/TD]
[TD="class: xl66, width: 198"][/TD]
[TD="class: xl67, width: 90"][/TD]
[TD="class: xl66, width: 198"][/TD]
[/TR]
</tbody>[/TABLE]

What is the easiest way to compress this data to one line per employee?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
is that what you want?

done with PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Employee Name[/td][td=bgcolor:#5B9BD5]Medical[/td][td=bgcolor:#5B9BD5]Dental[/td][td=bgcolor:#5B9BD5]Vision[/td][td=bgcolor:#5B9BD5]Vision Pay[/td][td=bgcolor:#5B9BD5]Flex[/td][td][/td][td=bgcolor:#70AD47]Employee Name[/td][td=bgcolor:#70AD47]Medical[/td][td=bgcolor:#70AD47]Dental[/td][td=bgcolor:#70AD47]Vision[/td][td=bgcolor:#70AD47]Vision Pay[/td][td=bgcolor:#70AD47]Flex[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Emp1[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]DenBasicFam[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]Emp1[/td][td=bgcolor:#E2EFDA]MedBasicFamily[/td][td=bgcolor:#E2EFDA]DenBasicFam[/td][td=bgcolor:#E2EFDA]Vision[/td][td=bgcolor:#E2EFDA]$18.00[/td][td=bgcolor:#E2EFDA]Flex[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Emp1[/td][td]MedBasicFamily[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Emp2[/td][td]MedBasicSingle[/td][td][/td][td]Vision[/td][td]$8.50[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Emp1[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Vision[/td][td=bgcolor:#DDEBF7]$18.00[/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Emp1[/td][td][/td][td][/td][td][/td][td][/td][td]Flex[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Emp2[/td][td=bgcolor:#DDEBF7]MedBasicSingle[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Emp2[/td][td][/td][td][/td][td]Vision[/td][td]$8.50[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
 
Upvote 0
here is a M-code for example above

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Name", type text}, {"Medical", type text}, {"Dental", type text}, {"Vision", type text}, {"Vision Pay", type text}, {"Flex", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee Name"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Medical", each List.Distinct(Table.Column([Count],"Medical"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Medical", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Added Custom1" = Table.AddColumn(#"Extracted Values", "Dental", each List.Distinct(Table.Column([Count],"Dental"))),
    #"Extracted Values1" = Table.TransformColumns(#"Added Custom1", {"Dental", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Added Custom2" = Table.AddColumn(#"Extracted Values1", "Vision", each List.Distinct(Table.Column([Count],"Vision"))),
    #"Extracted Values2" = Table.TransformColumns(#"Added Custom2", {"Vision", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Added Custom3" = Table.AddColumn(#"Extracted Values2", "Vision Pay", each List.Distinct(Table.Column([Count],"Vision Pay"))),
    #"Extracted Values3" = Table.TransformColumns(#"Added Custom3", {"Vision Pay", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Added Custom4" = Table.AddColumn(#"Extracted Values3", "Flex", each List.Distinct(Table.Column([Count],"Flex"))),
    #"Extracted Values4" = Table.TransformColumns(#"Added Custom4", {"Flex", each Text.Combine(List.Transform(_, Text.From)), type text})
in
    #"Extracted Values4"[/SIZE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Split Salary Report Formulas
Replies
1
Views
902
Drrellik
Drrellik
I
  • Question Question
Attach data in one column
Replies
2
Views
501
Islam Hamed
I
L
  • Question Question
Index Match of partial data then date drive results
Replies
2
Views
637
lndspdr
L
A
  • Question Question
Mail Merge?
Replies
0
Views
278
akeys5862
A
J
  • Question Question
Question - Data extrat from one workbook into another
Replies
0
Views
559
John_Jay
J

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

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