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

%3CLINGO-SUB%20id%3D%22lingo-sub-2986614%22%20slang%3D%22en-US%22%3EExcel%20help%2C%20to%20consolidate%20cells%20in%20different%20columns%2C%20and%20make%20them%20read%20in%20a%20new%20column%20legibly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2986614%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Mgarcia7777_0-1637344984817.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F328240i52A38A055A3F3A22%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Mgarcia7777_0-1637344984817.png%22%20alt%3D%22Mgarcia7777_0-1637344984817.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EWould%20like%20it%20to%20read%20like%20this%3C%2FP%3E%3CP%3EThe%20Livingston%3C%2FP%3E%3CP%3E6301%20windhaven%20pkwy%3C%2FP%3E%3CP%3EPlano%20Tx%26nbsp%3B%2075093%3C%2FP%3E%3CP%3E972-801-9595%3C%2FP%3E%3CP%3EBuilt%201998%3C%2FP%3E%3CP%3EWebsite%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fgrandpeaks.com%2Ftexas%2Fthe-livingston%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgrandpeaks.com%2Ftexas%2Fthe-livingston%2F%3C%2FA%3E%3C%2FP%3E%3CP%3ETtl%20Units%3A%20180%3C%2FP%3E%3CP%3ETtl%20toilets%3A%20266%3C%2FP%3E%3CP%3EUnit%20mix%3A%26nbsp%3B1%20%2B%201%20%3D%206%2C%201%20%2B%201%20%3D%206%2C%201%20%2B%201%20%3D%202%2C%201%20%2B%201%20%3D%2016%2C%201%20%2B%201%20%3D%2018%2C%201%20%2B%201%20%3D%2018%2C%202%20%2B%202%20%3D%206%2C%202%20%2B%202%20%3D%2016%2C%3C%2FP%3E%3CP%3E2%20%2B%202%20%3D%2018%2C%202%20%2B%202%20%3D%202%2C%202%20%2B%202.5%20%3D%20N%2FA%2C%202%20%2B%202.5%20%3D%208%2C%203%20%2B%202%20%3D%2028%2C%203%20%2B%202%20%3D%2018%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2986614%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New 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.