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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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
914
Drrellik
Drrellik
I
  • Question Question
Attach data in one column
Replies
2
Views
506
Islam Hamed
I
L
  • Question Question
Index Match of partial data then date drive results
Replies
2
Views
642
lndspdr
L
A
  • Question Question
Mail Merge?
Replies
0
Views
280
akeys5862
A
J
  • Question Question
Question - Data extrat from one workbook into another
Replies
0
Views
561
John_Jay
J

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