none
Split columns to rows with no delimiter RRS feed

  • Question

  • I've something like this

    and on the end I'd like to achieve this one or similar

    Problem is how to remove duplicates from rows of Data. I stopped in the first picture and not a step further.

    I will be grateful for any advice approaching the final result

    Best regards

    Wednesday, March 28, 2018 2:22 PM

Answers

  • Hi andy.

    This is the code for the custom column:

    =Text.Combine(List.Distinct(Text.Split([Data], " ")), " ")


    Maxim Zelensky Excel Inside

    • Marked as answer by andy.lev Wednesday, March 28, 2018 4:09 PM
    Wednesday, March 28, 2018 3:46 PM

All replies

  • Hey Andy,

    What would be the definition of a unique row or a duplicate row for you? is it the full contents of the cell? or are you looking for something else? If it is the full contents of a cell then you can do a "Remove Duplicates" and it should do the job.

    Wednesday, March 28, 2018 2:37 PM
  • 1st pic is my last step - I'd like to split 1st col & 2nd column "move" to the one row with space delimiter. I'll delete first column after that

    0 11:21:00 11:29:00

    1 15:56:00 23:57:00 23:54:00 23:37:00  and so on

    2nd is a source (blue) and result (green) what I want to achieve. It could be fine because this is what I want but if I add new row to the blue table it wont work. Every refresh do nothing

    anyway result should be like in the green table (it's a little twisted because I added first pic needlessly I think)


    • Edited by andy.lev Wednesday, March 28, 2018 3:31 PM
    Wednesday, March 28, 2018 3:23 PM
  • Hi andy.

    This is the code for the custom column:

    =Text.Combine(List.Distinct(Text.Split([Data], " ")), " ")


    Maxim Zelensky Excel Inside

    • Marked as answer by andy.lev Wednesday, March 28, 2018 4:09 PM
    Wednesday, March 28, 2018 3:46 PM
  • Hi Maxim,

    You are my Crystal Ball :)

    Many Big Thanks.

    5 hours without result and voile, few minutes :)

    Again Many Tanks

    Have a good day

    Wednesday, March 28, 2018 4:05 PM
  • Glad to help, Andy.

    Please mark my reply as answer if it worth it :)


    Maxim Zelensky Excel Inside

    Wednesday, March 28, 2018 4:08 PM
  • Of course, Maxim

    Done

    Wednesday, March 28, 2018 4:09 PM