Need help with data consolidation

I

iamhere_18

New Member
Joined
Dec 4, 2018
Messages
4
Hi All!

I could use some help with this, if anyone has any ideas on how to tackle it I'd be really grateful. Here's an example of what I'm working with:

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Location[/TD]
[TD]Contact[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]123 Main St[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Jane Doe[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Jack Frost[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]456 Second St[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Lacie Lou[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]John Doe[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]789 Third St[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


My files have hundreds of rows and atleast 20 columns so this is a much smaller example obviously.. How can I condense something like this without corrupting the data? Is it something that can only be done manually?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
You didn't show what you want to achieve but maybe...

with your example & PowerQuery:

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Location"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"ID", "Location"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Contact", each List.Distinct(Table.Column([Count],"Contact"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Contact", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    #"Extracted Values"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]ID[/td][td=bgcolor:#5B9BD5]Location[/td][td=bgcolor:#5B9BD5]Contact[/td][td][/td][td=bgcolor:#70AD47]ID[/td][td=bgcolor:#70AD47]Location[/td][td=bgcolor:#70AD47]Contact[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]123 Main St[/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]123 Main St[/td][td=bgcolor:#E2EFDA]Jane Doe, Jack Frost[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td][/td][td]Jane Doe[/td][td][/td][td]
2​
[/td][td]456 Second St[/td][td]Lacie Lou, John Doe[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Jack Frost[/td][td][/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]789 Third St[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2​
[/td][td]456 Second St[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
2​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2​
[/td][td][/td][td]Lacie Lou[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
2​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]John Doe[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
3​
[/td][td]789 Third St[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
3​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
 
Last edited:
Upvote 0
Thanks for your response! Here is an example of what I need achieved:

Some of the rows need a blank contact cell, they might have info in a different column (ie: accountspayable@somebusiness.com in the email column not pictured in this example)

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD]Id[/TD]
[TD]Location[/TD]
[TD]Contact[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]123 Main St[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]123 Main St[/TD]
[TD]Jane Doe[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]123 Main St[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Jack Frost[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]456 Second St[/TD]
[TD]Lacie Lou[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]456 Second St[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]John Doe[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]789 Third St[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
something like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]ID[/td][td=bgcolor:#70AD47]Location[/td][td=bgcolor:#70AD47]Contact[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]123 Main St[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td]123 Main St[/td][td]Jane Doe[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]123 Main St[/td][td=bgcolor:#E2EFDA]Jack Frost[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2​
[/td][td]456 Second St[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]456 Second St[/td][td=bgcolor:#E2EFDA]Lacie Lou[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2​
[/td][td]456 Second St[/td][td]John Doe[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]789 Third St[/td][td=bgcolor:#E2EFDA][/td][/tr]
[/table]
 
Upvote 0
sure

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Location"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"ID", "Location"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Contact", each List.Distinct(Table.Column([Count],"Contact"))),
    #"Expanded Contact" = Table.ExpandListColumn(#"Added Custom", "Contact")
in
    #"Expanded Contact"[/SIZE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Need Help Consolidating/Condensing rows together if similar information
Replies
2
Views
206
Handienak
H
J
  • Question Question
If column B is empty and column C contains a value, show column A (also using Indirect to draw data from another workbook)
Replies
1
Views
448
Fluff
Fluff
T
  • Question Question
Function that will collapse 4 rows of data into one row based on the first two columns information.
Replies
1
Views
464
James006
James006
A
  • Question Question
Trying to combine data from two Excel sheets
Replies
0
Views
423
alex_345
A
P
  • Question Question
VBA for sending rows of data if column A = current date. (Via email)
Replies
1
Views
749
paun_shotts
P

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