Results 1 to 8 of 8

Advanced INDEX MATCH (I think)

Threaded View

  1. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,754

    Re: Advanced INDEX MATCH (I think)

    This is a fairly easy exercise using Power Query/Get and Transform. Here is the Mcode

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Email", type text}, {"Choice 1", type text}, {"Choice 2", type text}, {"Choice 3", type text}, {"Choice 4", type text}, {"Choice 5", type text}, {"Choice 6", type text}, {"Choice 7", type text}, {"Choice 8", type text}}),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Email"}, "Attribute", "Value"),
        #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
        #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Value", "Email"}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Value", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Value.1", "Value.2"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", Int64.Type}}),
        #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Value.2", Order.Ascending}}),
        #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Sorted Rows", {{"Value.2", type text}}, "en-US"),{"Value.1", "Value.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Topic")
    in
        #"Merged Columns"
    The steps involved:
    1. Load data to PQ
    2. Unpivot all columns except first
    3. Split Topic Column into two columns
    4. Sort numeric column of split
    5. Merge two columns
    6. Delete unneeded columns
    7. Close and Load to Excel Workbook.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Advanced Match Index Function
    By takeawalkk in forum Excel General
    Replies: 3
    Last Post: 08-22-2017, 07:57 AM
  2. [SOLVED] Advanced Match & Index
    By Ahmed Al-Boghdady in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-09-2017, 04:07 AM
  3. Advanced Index/Match Functions
    By Frog85 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-07-2016, 12:27 AM
  4. Advanced INDEX/MATCH Formula - is this possible?
    By h2holbro22 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-29-2016, 01:42 AM
  5. Advanced If or Match(index) vba... not really sure.
    By TexasBobcat in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-04-2015, 03:09 PM
  6. [SOLVED] Advanced Match Help (possibly Index Match)
    By dfxryanjr in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-08-2013, 09:13 AM
  7. advanced lookup/index-match problem
    By tx12345 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-11-2006, 12:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1