Excel help, to consolidate cells in different columns, and make them read in a new column legibly

Copper Contributor

Mgarcia7777_0-1637344984817.png

Would like it to read like this

The Livingston

6301 windhaven pkwy

Plano Tx  75093

972-801-9595

Built 1998

Website: https://grandpeaks.com/texas/the-livingston/

Ttl Units: 180

Ttl toilets: 266

Unit mix: 1 + 1 = 6, 1 + 1 = 6, 1 + 1 = 2, 1 + 1 = 16, 1 + 1 = 18, 1 + 1 = 18, 2 + 2 = 6, 2 + 2 = 16,

2 + 2 = 18, 2 + 2 = 2, 2 + 2.5 = N/A, 2 + 2.5 = 8, 3 + 2 = 28, 3 + 2 = 18

 

3 Replies

@Mgarcia7777 

If something like this

image.png

it could be done by Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Duplicates" = Table.Distinct(Source, {"Property Street"}),
    Location = Table.CombineColumns(
        Table.TransformColumnTypes(
            #"Removed Duplicates",
                {
                    {"Property Zip", type text}}, "en-GB"),
                    {"Property City", "Property State", "Property Zip"
                },Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Location"),
    #"Keep Columns to Transform" = Table.SelectColumns(
        Location,
        {"Property Name", "Property Street", "Location", "Property Phone", "Year Built",
         "Property Website", "Units Total", "Toilets Total", "Unit Mix"}),
    #"Values as List" = Table.AddColumn(
        #"Keep Columns to Transform",
        "Values", each Record.FieldValues(_)),
    #"Keep Lists only" = Table.SelectColumns(
        #"Values as List",{"Values"}),
    #"Table from lists" = Table.FromColumns( #"Keep Lists only"[Values] ),
    #"Promoted Headers" = Table.PromoteHeaders(
        #"Table from lists", [PromoteAllScalars=true])
in
    #"Promoted Headers"
Very good info, however I am unfamiliar with Power Query, so I would need major walk through

@Mgarcia7777 

Yes, Power Query requires to invest some time to start working with it, that's not copy/paste the pattern approach.