Results 1 to 9 of 9

Thread: Reverse Colums

  1. #1
    Acolyte Heyjoe's Avatar
    Join Date
    Jan 2019
    Location
    USA
    Posts
    48
    Articles
    0
    Excel Version
    2019

    Post Reverse Colums



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello Fellow Excel Users

    I set up a spreadsheet like this:

    Date 2018 2017 2016 2015
    Sales 200 229 150 100

    I don't know why I did this. Now the dates seem backwards to me.

    I would like to reverse the columns so that 2015 is on the left and 2018 is on the right.

    Is there a command in excel to do this?

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    136
    Articles
    0
    Excel Version
    2019
    Load your SS into Power Query/Get and Transform then apply the following Mcode

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"2018", Int64.Type}, {"2017", Int64.Type}, {"2016", Int64.Type}, {"2015", Int64.Type}}),
        #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Date", "2015", "2016", "2017", "2018"})
    in
        #"Reordered Columns"

  3. #3
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,166
    Articles
    0
    Excel Version
    Office 365 Subscription
    Alan's suggestion will work, but might fail if the column headings subsequently change. This might be more robust:

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Date"}, "Attribute", "Value"),
        #"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Attribute", Order.Ascending}}),
        #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Attribute]), "Attribute", "Value")
    in
        #"Pivoted Column"
    Ali
    Enthusiastic self-taught user of MS Excel!

  4. #4
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    136
    Articles
    0
    Excel Version
    2019
    Ali

    Nice Solution. Never thought of doing it that way.

    Alan

  5. #5
    Acolyte Heyjoe's Avatar
    Join Date
    Jan 2019
    Location
    USA
    Posts
    48
    Articles
    0
    Excel Version
    2019
    Thank you both so much. I am not familiar with power queries. Would it work if the spreadsheet has a lot of rows in it?

  6. #6
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,166
    Articles
    0
    Excel Version
    Office 365 Subscription
    Yes, it will work with lots of rows - try it!

    Alan - the more I use PQ, the more I realise that there are, inevitably, pitfalls. Anything specifically defined in a stage of the M code (especially column names) can cause the query to break if things change. Gil Raviv's book has made me think a lot more about this when building queries from scratch.
    Ali
    Enthusiastic self-taught user of MS Excel!

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,523
    Articles
    0
    Excel Version
    365
    Late, I know, but there's a horizontal sort built in.
    Before:
    Click image for larger version. 

Name:	2019-03-27_225131.jpg 
Views:	17 
Size:	17.7 KB 
ID:	9001
    after:
    Click image for larger version. 

Name:	2019-03-27_225254.jpg 
Views:	12 
Size:	2.9 KB 
ID:	9002

  8. #8
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    877
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by Heyjoe View Post
    Is there a command in excel to do this?
    Try formula below (copy across)
    Code:
    =INDEX($B1:$E1;COUNTA(B1:$E1))
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  9. #9
    Acolyte Heyjoe's Avatar
    Join Date
    Jan 2019
    Location
    USA
    Posts
    48
    Articles
    0
    Excel Version
    2019
    Thanks everyone. The simple horizontal sort is what I was looking for.

Posting Permissions

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