Conditional rearrangement of data

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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
    let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null)),
#"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Column2", "Column3"})
in
#"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.

    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],"")))


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login