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:

PNModel CodeModelSOPDescCostDue Date
ABCD88B
New Version

<tbody>
</tbody>
201901

<tbody>
</tbody>
Widget

<tbody>
</tbody>
201903

<tbody>
</tbody>
ABCD88B
201901

<tbody>
</tbody>
Widget

<tbody>
</tbody>
$ 89.99

<tbody>
</tbody>
201903

<tbody>
</tbody>
ABCD
New Version

<tbody>
</tbody>
201901

<tbody>
</tbody>
Widget

<tbody>
</tbody>
$ 89.99

<tbody>
</tbody>
201903

<tbody>
</tbody>

<tbody>
</tbody>


Of course, there are about 50 columns of information.

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

Model CodeModelSOPDescCostDue Date
ABCD88B
New Version

<tbody>
</tbody>
201901

<tbody>
</tbody>
Widget

<tbody>
</tbody>
201903

<tbody>
</tbody>

Thanks!
 

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
your expected result is a little weird so maybe like this with PowerQuery:

PNModel CodeModelSOPDescCostDue DatePNModel CodeModelSOPDescCostDue Date
ABCD88BNew Version
201901​
Widget
201903​
ABCD88BNew Version201901Widget$ 89.99201903
ABCD88B
201901​
Widget$ 89.99
201903​
ABCDNew Version
201901​
Widget$ 89.99
201903​

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:

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]PN[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Model Code[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Model[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]SOP[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Desc[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Cost[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Due Date[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]PN[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Model Code[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Model[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]SOP[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Desc[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Cost[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Due Date[/COLOR]
ABCD88BNew Version
201901​
Widget
201903​
ABCD88BNew Version201901Widget$ 89.99201903
ABCD88B
201901​
Widget$ 89.99
201903​
ABCDNew Version
201901​
Widget$ 89.99
201903​

<tbody>
</tbody>


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

Forum statistics

Threads
1,214,875
Messages
6,122,040
Members
449,063
Latest member
ak94

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