Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Rearranging Data in Excel
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2014
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Rearranging Data in Excel

    Hi, all. I have a table where I need to rearrange the data. The below is a small sample size. The real table is about 100 rows and 700 columns. The first table is what it looks like now, and the second table is what I'd like it to look like. Is there an easy way to do this. I tried pivot tables but it wasn't working right (for me). Would MS Access be a better tool? Although I'm not sure how to build the right query.

    Any help would be greatly appreciated!!! Thanks!!!

    Current Input
    Qualifier Model 1 Model 2 Model 3
    A 0.1 0.2
    B 0.5 0.1
    C 0.4 0.1 0.1
    D 0.6 0.1
    E 0.3 0.5


    Desired Output (the headers in the top row can be any text as far as I'm concerned)
    Model Qualifier Value
    Model 1 A 0.1
    Model 1 B 0.5
    Model 1 C 0.4
    Model 1 D
    Model 1 E
    Model 2 A
    Model 2 B
    Model 2 C 0.1
    Model 2 D 0.6
    Model 2 E 0.3
    Model 3 A 0.2
    Model 3 B 0.1
    Model 3 C 0.1
    Model 3 D 0.1
    Model 3 E 0.5


  2. #2
    Board Regular
    Join Date
    Dec 2009
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rearranging Data in Excel

    It looks like what you are trying to do is called unpivot. If you have excel 2016+ you can use the built in power query to do this I think -- I've never done it, but you should be able to google it. If you are using an older version of excel (2007+) you can use the free nutilities add-in from iwishexcel.com. once installed, the nutility you are looking for is also called unpivot. The pic below shows the instructions from the nutilities menu.


    https://ibb.co/3Yyj2Gv
    My favorite Excel Add-in:= Nutilities

  3. #3
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,267
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Rearranging Data in Excel

    Maybe this video can help
    https://www.youtube.com/watch?v=xmqTN0X-AgY

    M.

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,827
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Rearranging Data in Excel

    or with PowerQuery aka Get&Transform

    Qualifier Model 1 Model 2 Model 3 Model Qualifier Value
    A
    0.1
    0.2
    Model 1 A
    0.1
    B
    0.5
    0.1
    Model 1 B
    0.5
    C
    0.4
    0.1
    0.1
    Model 1 C
    0.4
    D
    0.6
    0.1
    Model 1 D
    E
    0.3
    0.5
    Model 1 E
    Model 2 A
    Model 2 B
    Model 2 C
    0.1
    Model 2 D
    0.6
    Model 2 E
    0.3
    Model 3 A
    0.2
    Model 3 B
    0.1
    Model 3 C
    0.1
    Model 3 D
    0.1
    Model 3 E
    0.5


    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ReplaceNull = Table.ReplaceValue(Source,null,999,Replacer.ReplaceValue,{"Model 1", "Model 2", "Model 3"}),
        Unpivot = Table.UnpivotOtherColumns(ReplaceNull, {"Qualifier"}, "Attribute", "Value"),
        Replace = Table.ReplaceValue(Unpivot,999,null,Replacer.ReplaceValue,{"Value"}),
        Sort = Table.Sort(Replace,{{"Attribute", Order.Ascending}, {"Qualifier", Order.Ascending}}),
        Finish = Table.RenameColumns(Table.ReorderColumns(Sort,{"Attribute", "Qualifier", "Value"}),{{"Attribute", "Model"}})
    in
        Finish
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #5
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,267
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Rearranging Data in Excel

    Hi Sandy

    Maybe a silly question - starting to learn Power Query...
    I got the desired result (see below), but couldn't in Power Query replace null with "" (empty string).

    A
    B
    C
    1
    Model
    Qualifier
    Valor
    2
    Model 1
    A
    0,1
    3
    Model 1
    B
    0,5
    4
    Model 1
    C
    0,4
    5
    Model 1
    D
    6
    Model 1
    E
    7
    Model 2
    A
    8
    Model 2
    B
    9
    Model 2
    C
    0,1
    10
    Model 2
    D
    0,6
    11
    Model 2
    E
    0,3
    12
    Model 3
    A
    0,2
    13
    Model 3
    B
    0,1
    14
    Model 3
    C
    0,1
    15
    Model 3
    D
    0,1
    16
    Model 3
    E
    0,5


    The alternative way I found was:
    Replace null with zero
    Unpivot 3 columns
    Move Model column left
    Replace zero with null (reverting)
    Sort
    Load

    Is there a better way?

    M.
    Last edited by Marcelo Branco; Jul 25th, 2019 at 11:37 PM.

  6. #6
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,267
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Rearranging Data in Excel

    Complementing previous post
    M-Code (in Portuguese, I hope you can understand it )

    Code:
    let
        Fonte = Excel.CurrentWorkbook(){[Name="Tabela2"]}[Content],
        #"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"Qualifier", type text}, {"Model 1", type number}, {"Model 2", type number}, {"Model 3", type number}}),
        #"Valor Substituído" = Table.ReplaceValue(#"Tipo Alterado",null,0,Replacer.ReplaceValue,{"Model 1", "Model 2", "Model 3"}),
        #"Somente as Colunas Selecionadas Foram Transformadas em Linhas" = Table.Unpivot(#"Valor Substituído", {"Model 1", "Model 2", "Model 3"}, "Atributo", "Valor"),
        #"Colunas Reordenadas" = Table.ReorderColumns(#"Somente as Colunas Selecionadas Foram Transformadas em Linhas",{"Atributo", "Qualifier", "Valor"}),
        #"Linhas Classificadas" = Table.Sort(#"Colunas Reordenadas",{{"Atributo", Order.Ascending}, {"Qualifier", Order.Ascending}}),
        #"Valor Substituído1" = Table.ReplaceValue(#"Linhas Classificadas",0,null,Replacer.ReplaceValue,{"Valor"})
    in
        #"Valor Substituído1"
    M.

  7. #7
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,827
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Rearranging Data in Excel

    Quote Originally Posted by Marcelo Branco View Post
    The alternative way I found was:
    Replace null with zero
    Unpivot 3 columns
    Move Model column left
    Replace zero with null (reverting)
    Sort
    Load
    Is there a better way?
    Hi Marcelo,
    but this is the same method except these differences: (null to 999 then 999 to null / null to 0 then 0 to null). IMHO with 999 is harder to make mistake because 0 is very close to the values in the source.
    The important thing is that the goal has been achieved

    edit: (with Google Translate)
    Code:
    let
         Source = Excel.CurrentWorkbook () {[Name = "Table2"]} [Content],
         # "Changed Type" = Table.TransformColumnTypes (Source, {{"Qualifier", type text}, {"Model 1", type number}, {"Model 2", type number}, {"Model 3", type number }}),
         # "Replaced Value" = Table.ReplaceValue (# "Changed Type", null, 0, Replacer.ReplaceValue, {"Model 1", "Model 2", "Model 3"}),
         # "Only Selected Columns Transformed into Rows" = Table.Unpivot (# "Replaced Value", {"Model 1", "Model 2", "Model 3"}, "Attribute", "Value"),
         # "Reordered Columns" = Table.ReorderColumns (# "Only Selected Columns Transformed into Rows", {"Attribute", "Qualifier", "Value"}),
         # "Sorted Rows" = Table.Sort (# "Reordered Columns", {{"Attribute", Order.Ascending}, {"Qualifier", Order.Ascending}}),
         # "Substituted Value1" = Table.ReplaceValue (# "Sorted Rows", 0, null, Replacer.ReplaceValue, {"Value"})
    in
         # "Substituted Value1"
    Last edited by sandy666; Jul 26th, 2019 at 05:38 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  8. #8
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,827
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Rearranging Data in Excel

    btw. what if values contain zeroes?
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  9. #9
    New Member
    Join Date
    Apr 2014
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rearranging Data in Excel

    Odin, Sandy, and Marcelo... all very helpful. Both strategies worked. And I learned something new with Power Queries. Cheers!

  10. #10
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,827
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Rearranging Data in Excel

    You are welcome

    Have a nice day
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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