Consolidating Data

LizzieW71

New Member
Joined
Mar 3, 2017
Messages
5
Hi All!

I have a spreadsheet with more than 15K lines of data. This is the product of combining about 10 different versions of the same spreadsheet (there was no real version control before I started working on it). The data are grouped by content in cells in column A (VBA code to insert blank row where data changes).

The data is repetitive and can be eliminated; however, within each group there, something prevents it from being a duplicate.

Short of going through each group (some have 5 lines, some have 10, etc), how can I consolidate each group of rows into one?

For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]PN[/TD]
[TD]Model Code[/TD]
[TD]Model[/TD]
[TD]SOP[/TD]
[TD]Desc[/TD]
[TD]Cost[/TD]
[TD]Due Date[/TD]
[/TR]
[TR]
[TD]ABCD[/TD]
[TD]88B[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]New Version[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]201901[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]Widget[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]201903[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]ABCD[/TD]
[TD]88B[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]201901[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]Widget[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"] $ 89.99 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]201903[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]ABCD[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]New Version[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]201901[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]Widget[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"] $ 89.99 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]201903[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Of course, there are about 50 columns of information.

How can I combine these three rows into one to look like:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Model Code[/TD]
[TD]Model[/TD]
[TD]SOP[/TD]
[TD]Desc[/TD]
[TD]Cost[/TD]
[TD]Due Date[/TD]
[/TR]
[TR]
[TD]ABCD[/TD]
[TD]88B[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]New Version[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]201901[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]Widget[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]201903[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks!
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
your expected result is a little weird so maybe like this with PowerQuery:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]PN[/td][td=bgcolor:#5B9BD5]Model Code[/td][td=bgcolor:#5B9BD5]Model[/td][td=bgcolor:#5B9BD5]SOP[/td][td=bgcolor:#5B9BD5]Desc[/td][td=bgcolor:#5B9BD5]Cost[/td][td=bgcolor:#5B9BD5]Due Date[/td][td][/td][td=bgcolor:#70AD47]PN[/td][td=bgcolor:#70AD47]Model Code[/td][td=bgcolor:#70AD47]Model[/td][td=bgcolor:#70AD47]SOP[/td][td=bgcolor:#70AD47]Desc[/td][td=bgcolor:#70AD47]Cost[/td][td=bgcolor:#70AD47]Due Date[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ABCD[/td][td=bgcolor:#DDEBF7]88B[/td][td=bgcolor:#DDEBF7]New Version[/td][td=bgcolor:#DDEBF7]
201901​
[/td][td=bgcolor:#DDEBF7]Widget[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
201903​
[/td][td][/td][td=bgcolor:#E2EFDA]ABCD[/td][td=bgcolor:#E2EFDA]88B[/td][td=bgcolor:#E2EFDA]New Version[/td][td=bgcolor:#E2EFDA]201901[/td][td=bgcolor:#E2EFDA]Widget[/td][td=bgcolor:#E2EFDA]$ 89.99[/td][td=bgcolor:#E2EFDA]201903[/td][/tr]

[tr=bgcolor:#FFFFFF][td]ABCD[/td][td]88B[/td][td][/td][td]
201901​
[/td][td]Widget[/td][td]$ 89.99[/td][td]
201903​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ABCD[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]New Version[/td][td=bgcolor:#DDEBF7]
201901​
[/td][td=bgcolor:#DDEBF7]Widget[/td][td=bgcolor:#DDEBF7]$ 89.99[/td][td=bgcolor:#DDEBF7]
201903​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]


Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"PN"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Model Code", each List.Distinct(Table.Column([Count],"Model Code"))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Model", each List.Distinct(Table.Column([Count],"Model"))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "SOP", each List.Distinct(Table.Column([Count],"SOP"))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Desc", each List.Distinct(Table.Column([Count],"Desc"))),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Cost", each List.Distinct(Table.Column([Count],"Cost"))),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Due Date", each List.Distinct(Table.Column([Count],"Due Date"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom5", {"Model Code", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Model", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Extracted Values2" = Table.TransformColumns(#"Extracted Values1", {"SOP", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Extracted Values3" = Table.TransformColumns(#"Extracted Values2", {"Desc", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Extracted Values4" = Table.TransformColumns(#"Extracted Values3", {"Cost", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Extracted Values5" = Table.TransformColumns(#"Extracted Values4", {"Due Date", each Text.Combine(List.Transform(_, Text.From)), type text})
in
    #"Extracted Values5"[/SIZE]
 
Upvote 0
your expected result is a little weird so maybe like this with PowerQuery:

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]PN[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Model Code[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Model[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]SOP[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Desc[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Cost[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Due Date[/COLOR][/TD]
[TD][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]PN[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Model Code[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Model[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]SOP[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Desc[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Cost[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Due Date[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]ABCD[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]88B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]New Version[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]
201901​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Widget[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]
201903​
[/TD]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]ABCD[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]88B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]New Version[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]201901[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]Widget[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]$ 89.99[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]201903[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]ABCD[/TD]
[TD]88B[/TD]
[TD][/TD]
[TD]
201901​
[/TD]
[TD]Widget[/TD]
[TD]$ 89.99[/TD]
[TD]
201903​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]ABCD[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]New Version[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]
201901​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Widget[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]$ 89.99[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]
201903​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"PN"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Model Code", each List.Distinct(Table.Column([Count],"Model Code"))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Model", each List.Distinct(Table.Column([Count],"Model"))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "SOP", each List.Distinct(Table.Column([Count],"SOP"))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Desc", each List.Distinct(Table.Column([Count],"Desc"))),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Cost", each List.Distinct(Table.Column([Count],"Cost"))),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Due Date", each List.Distinct(Table.Column([Count],"Due Date"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom5", {"Model Code", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Model", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Extracted Values2" = Table.TransformColumns(#"Extracted Values1", {"SOP", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Extracted Values3" = Table.TransformColumns(#"Extracted Values2", {"Desc", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Extracted Values4" = Table.TransformColumns(#"Extracted Values3", {"Cost", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Extracted Values5" = Table.TransformColumns(#"Extracted Values4", {"Due Date", each Text.Combine(List.Transform(_, Text.From)), type text})
in
    #"Extracted Values5"[/SIZE]
Hi Sandy666,

Thanks for replying so quickly. Sorry, when I pasted my example it came out funny. It should look like your example.

Basically, I'd have to do this for each column using its heading?

Thanks
Liz
 
Upvote 0
headers should be exactly the same like in your example from your post (or from blue table from post #2 )
You need group by first column, and use List.Distinct(Table.Column(...)) for the rest... etc... (edit: for each column except first)
here is excel file with your example.

or your example was not representative ?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

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