Combining text from two column's and creating a new list

[Michael Colwell]

Hi, hoping someone can help.

Please see the table below.

I'm trying to combine the "Base Code" and "Variant" column to produce the "Combined" column result. Hope that makes sense.

Basically I want to take the first code in the "Base Code" list and then combine that with the values in the "Variant" column separated by the ',' delimiter (and also add a '-').

Any advice, pointers etc would be much appreciated.

Regards,

Michael.

[#2]

Michael,

would you consider a Power Query solution?

[#3]

Hi Detlef,

Am not familiar with Power Query.

I've just Googled it and will need to look into it a bit further before I decide what to do, but thanks fo the advice.

Regards,

Michael.

[#4]

Hi Detlef,

Can't install Power Query as it requires the 64 bit Excel version, I'm reatricted to the 32bit version.

Thanks for the suggestion.

Any other ideas as to how I might achieve my column extraction & combining !!

Am wondering if I need to look into some sort of VB option.

Regarsds.

[#5 · Accepted solution]

Microsoft Power Query for Excel is available for 32-bit (x86) and 64-bit (x64) platforms, your selection must match architecture of the installed version of Office.

https://www.microsoft.com/en-us/download/details.aspx?id=39379

[#6]

Hi Sergi,

Thanks for the link, not sure how I missed that..!!

Have installed and will try to work through combining the column's as per my original post.

Regards.

[#7]

Michael,

first change the list into an Excel Table (CTRL-T) and query from table.

And then change the preliminary code to:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Base Code", type text}, {"Variant", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Variant", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Variant"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Variant", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Variant", type text}}, "de-DE"),{"Base Code", "Variant"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Combined")
in
    #"Merged Columns"

[#8]

Hi Detlef,

Thank you for the reply. I "attempted" to do as you outlined but ran into an error.

I'm just not that familiar with Excel, especially the table query and data dunctions etc.

I'll work away at this, but wanted to than you for your time and knowledge, it was much appreciated.

It will takle me a while as I'm coming from a limited Excel background and don't want to use up your time with fundamental questions that I need to go away and learn myself.

Thanks & Regards,

Michael.