Creating value lists with multiple values in same cell

[pacecar81]

I need to create value lists for fields from large tables of data where there are multiple values within the same cell separate by ";". The data is contained in the table as follows:

	Column 1	Column 2	Column 3
Row 1	Value1;Value2;Value3	ValueA	Valuei;Valueii
Row 2	Value1	ValueA;ValueB;ValueC	Valuei;Valueii;Valueiii
Row 3	Value1;Value4;Value5	ValueB;ValueD	Valueiii;Valueiv
Row 4	Value2;Value6	ValueD;ValueE	Valueiv;Valuev
Row 5	Value3;Value7	ValueF	Valuevi

And I wish to generate the following value lists:

Column 1 Value List	Column 2 Value List	Column 3 Value List
Value1	ValueA	Valuei
Value2	ValueB	Valueii
Value3	ValueC	Valueiii
Value4	ValueD	Valueiv
Value5	ValueE	Valuev
Value6	ValueF	Valuevi
Value7

Is there a way to do this without disrupting the original data table. Originally I thought I might be able to delimit the data within Pivot Tables, but inquiries I've made so far indicate that the data needs to be delimited first before generating the Pivot Table.

Does anyone have any proposed solutions?

Thanks in advance.

[#2]

@pacecar81 

That could be done by Power Query.

Let name the source data as Range, query it, unpivot all columns, split texts to lists, groups by columns with sum aggregation (or any other) and change in formula List.Sum on conversion to sequential list (see script), create final table based on result.

The script is

let
    Source = Excel.CurrentWorkbook(){[Name="Range"]}[Content],
    PromotHeaders = Table.PromoteHeaders(
        Source,
        [PromoteAllScalars=true]
    ),
    UnpivotColumns = Table.UnpivotOtherColumns(
        PromotHeaders,
        {},
        "Attribute", "Value"
    ),
    TextToList = Table.AddColumn(
        UnpivotColumns,
        "Custom",
        each Text.Split([Value],";")
    ),
    GroupRows = Table.Group(
        TextToList,
        {"Attribute"},
        {{"Count", each List.Distinct(List.Sort(List.Combine([Custom])))}}
    ),
    CreateTable = Table.FromColumns(
        GroupRows[Count],
        GroupRows[Attribute]
    )
in
    CreateTable