Consolidating Data

L

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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
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
sandy666 said:
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]
Click to expand...
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
You must log in or register to reply here.

Similar threads

J
  • Question Question
Combine Data Tables to Analyze in Pivot Table Excel for Mac
Replies
0
Views
2K
jan_d
J
R
  • Question Question
Generate dynamic templates with varying mandatory and non-mandatory fields depending on the product type chosen.
Replies
1
Views
1K
RajK2005
R
G
  • Question Question
Select folder, unformat & clean up certain sheets within workbooks & consolidate those certain sheets into a single workbook
Replies
0
Views
721
griff78
G
R
  • Question Question
VBA Help for Hiding rows from a data validation list choice.
Replies
1
Views
1K
realred2
R
L
  • Question Question
Use Table Column Headers as .Cells(x,header)
Replies
1
Views
669
LlebKcir
L

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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