Conditional rearrangement of data

  • Hi All! I am seeking help and any advice on following challenge.

    1. There are in total 65000 data entries in original file that need to be sorted and rearranged.

    2. Column A has unique number for each row.

    3. Column B has unique entry for multiple rows

    4. Column C contains data that needs to be:

    4.1. Checked for duplicates

    4.2. Remove duplicates

    4.3. Rearrange and move data in a new (specific) cell horizontally and with comma separated each value.

    Example is in attached file.

    Any comments and advise is highly appreciated.

    Thanks,
    Aivars

  • With Power Query, here is the Mcode that results in the following presentation shown in the attached file

    Code
    1. let
    2. Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    3. #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}}),
    4. #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null)),
    5. #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Column2", "Column3"})
    6. in
    7. #"Removed Duplicates"
  • Hi alansidman, thank you for taking the time to respond!

    The code works perfectly but it loops thru just those 10 sample entries. I have 65000 entries that need to be sorted. Was trying to figure it out but so far haven't learned how to do that.

  • Don't know what you are doing with the Mcode. This is universal and should work for 65000 rows. If you are unfamiliar with Power Query, then here is some additional information and links to help.


    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").



    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.



    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.



    - Follow this link for an introduction to Power Query functionality.



    - Follow this link for a video which demonstrates how to use Power Query code provided.

  • Hi! I got the table to work as it should. Now I am trying to rearrange the output data for each unique SKU horizontally in one cell.

    Like with Concatenate function but I am not sure how to make conditional concatenate function.

    Attached is file with desired output.

    Thanks!

  • Try formula solution ,


    1] In "Sheet1" E4, formula copied right to F4 and all copied down :


    =IFERROR(INDEX(A$4:A$24,AGGREGATE(15,6,ROW($A$1:$A$21)/((TEXT($A$3:$A$23,"0;;0;\0")="0")),ROW($A1))),"")


    2] In "Sheet1" G4, array (confirm pressing with Ctrl+Shift+Enter 3 keystrokes altogether) formula copied down :


    =IF(F4="","",TEXTJOIN(", ",,IF(Table1_2[Column2]=F4,Table1_2[Column3],"")))