Forum Discussion

Sandeeep's avatar
Sandeeep
Brass Contributor
Jan 16, 2023

VBA - finding unique Titles from a cell, then creating columns, then populating the data

VBA code required Imagine I had a cell in A column with the following data below cell A2 has name=Jake||Last=Smith||species=human||Language=en cell A3 has name=Steve||Language=en cell A4 has ...
Excel
Formulas and Functions
Macros and VBA
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jan 16, 2023

Sandeeep  Although you specifically asked for a VBA solution, you may want to consider Power Query. The non-working VBA codes you produced seem very complicated for a relatively simple task. The Power Query script below does exactly what you need with only a few lines of code and it's produced by clicking in the User Interface only. No manual M-coding needed. I only changed the names of the steps to make it more legible. file attached.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    Split1 = Table.ExpandListColumn(Table.TransformColumns(Index, {{"Column1", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    Split2 = Table.SplitColumn(Split1, "Column1", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    Pivot = Table.Pivot(Split2, List.Distinct(Split2[Column1.1]), "Column1.1", "Column1.2"),
    Remove = Table.RemoveColumns(Pivot,{"Index"})
in
    Remove

 

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jan 16, 2023

    Riny_van_Eekelen 

    I think you win that challenge (though it may not be what the OP wants).  I set out to achieve the same thing with an Excel formula.  The worksheet formula looks OK.

    = TabulateAttributesλ(targetList)

    but then it gets worse,

    TabulateAttributesλ
= ScanV2λ(Headersλ(tgtList), tgtList, ExtractAttributesλ)

ScanV2λ
= REDUCE(init, list, LAMBDA(acc,string, VSTACK(acc, Fnλ(init, string))))

ExtractAttributesλ
= LET(
    a, ToTableλ(s), 
    t, TAKE(a, , 1), v, TAKE(a, , -1), 
    XLOOKUP(titles, t, v, "")
  )

ToTableλ
= TEXTSPLIT(s, "=", "||")

Headersλ
= LET(
    combined,  ScanVλ({"Attribute","Value"}, targetList, ToTableλ),
    attribute, DROP(combined, 1, -1),
    TRANSPOSE(UNIQUE(attribute))
  )

     

Resources