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%3CLINGO-SUB%20id%3D%22lingo-sub-2986876%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%2C%20to%20consolidate%20cells%20in%20different%20columns%2C%20and%20make%20them%20read%20in%20a%20new%20column%20legibl%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2986876%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1224257%22%20target%3D%22_blank%22%3E%40Mgarcia7777%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20something%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20653px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F328262iFEB1B4D5559D8823%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eit%20could%20be%20done%20by%20Power%20Query%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%23%22Removed%20Duplicates%22%20%3D%20Table.Distinct(Source%2C%20%7B%22Property%20Street%22%7D)%2C%0A%20%20%20%20Location%20%3D%20Table.CombineColumns(%0A%20%20%20%20%20%20%20%20Table.TransformColumnTypes(%0A%20%20%20%20%20%20%20%20%20%20%20%20%23%22Removed%20Duplicates%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%22Property%20Zip%22%2C%20type%20text%7D%7D%2C%20%22en-GB%22)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%22Property%20City%22%2C%20%22Property%20State%22%2C%20%22Property%20Zip%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7D%2CCombiner.CombineTextByDelimiter(%22%20%22%2C%20QuoteStyle.None)%2C%22Location%22)%2C%0A%20%20%20%20%23%22Keep%20Columns%20to%20Transform%22%20%3D%20Table.SelectColumns(%0A%20%20%20%20%20%20%20%20Location%2C%0A%20%20%20%20%20%20%20%20%7B%22Property%20Name%22%2C%20%22Property%20Street%22%2C%20%22Location%22%2C%20%22Property%20Phone%22%2C%20%22Year%20Built%22%2C%0A%20%20%20%20%20%20%20%20%20%22Property%20Website%22%2C%20%22Units%20Total%22%2C%20%22Toilets%20Total%22%2C%20%22Unit%20Mix%22%7D)%2C%0A%20%20%20%20%23%22Values%20as%20List%22%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20%23%22Keep%20Columns%20to%20Transform%22%2C%0A%20%20%20%20%20%20%20%20%22Values%22%2C%20each%20Record.FieldValues(_))%2C%0A%20%20%20%20%23%22Keep%20Lists%20only%22%20%3D%20Table.SelectColumns(%0A%20%20%20%20%20%20%20%20%23%22Values%20as%20List%22%2C%7B%22Values%22%7D)%2C%0A%20%20%20%20%23%22Table%20from%20lists%22%20%3D%20Table.FromColumns(%20%23%22Keep%20Lists%20only%22%5BValues%5D%20)%2C%0A%20%20%20%20%23%22Promoted%20Headers%22%20%3D%20Table.PromoteHeaders(%0A%20%20%20%20%20%20%20%20%23%22Table%20from%20lists%22%2C%20%5BPromoteAllScalars%3Dtrue%5D)%0Ain%0A%20%20%20%20%23%22Promoted%20Headers%22%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%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.