Formula to arrange rows in numerical order.
Page 3 of 3 FirstFirst 123
Results 21 to 30 of 30

Thread: Formula to arrange rows in numerical order.
Thanks Thanks: 0 Likes Likes: 0

  1. #21
    Board Regular
    Join Date
    Mar 2011
    Posts
    215
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with formula to arrange rows in numerical order.

    Hello Peter!

    Thank you so much! What I meant by "a large amount of data" is the number of rows. I have nearly 5000 rows of numbers to put in numerical order. Whew! lol.
    I've put in the data per your instructions, no brackets, just CTR + Shift + Enter and for some reason, I'm getting a 'name error'.
    I have Office 13 so I think it's Excel 13?
    Is there any order that I should put in the formula? Maybe that's the problem? Looking forward to your answer. Thank you so much kind Sir.

    I nearly forgot to add, yes, the numbers are 1 or 2 digit and there are 5 individual numbers in each row. I tried to copy and paste all of the numbers but there were too many and it crashed. Thanks again!!
    Last edited by MrsFrankieH; Aug 17th, 2019 at 11:11 PM.

  2. #22
    Board Regular
    Join Date
    Mar 2011
    Posts
    215
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with formula to arrange rows in numerical order.

    Hello Peter!
    Here's a screenshot of the error message I'm getting. This is after Ctrl + Shift + Enter.
    Thanks again.

    Last edited by MrsFrankieH; Aug 17th, 2019 at 11:56 PM.

  3. #23
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,444
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Help with formula to arrange rows in numerical order.

    Quote Originally Posted by MrsFrankieH View Post
    I'm getting a 'name error'.
    That is why I asked about your Excel version. Earlier version do not have the CONCAT function.
    Since you only have 5 columns of data, try this formula in G2 instead. This is a standard formula not Control+Shift+Enter.

    =(1&TEXT(A2,"00")&TEXT(B2,"00")&TEXT(C2,"00")&TEXT(D2,"00")&TEXT(E2,"00"))+0
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #24
    Board Regular
    Join Date
    Mar 2011
    Posts
    215
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with formula to arrange rows in numerical order.

    Hello Peter!!! Thank you!! That newest formula worked like a charm but after I dragged it down, it produced errors after 6 rows. Is there a certain trick to having it drag down for indefinite rows?
    Thanks again, I truly appreciate it!


  5. #25
    Board Regular
    Join Date
    Mar 2011
    Posts
    215
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with formula to arrange rows in numerical order.

    Hello Peter! I think I figured it out. I'll keep you posted. Thanks again!!

  6. #26
    Board Regular
    Join Date
    Mar 2011
    Posts
    215
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with formula to arrange rows in numerical order.

    Hi Peter! Ok, update. I thought if I extended the range of the red and blue 'reference lines' (I don't know what they're called), it would extend the range of the formula. That didn't work. It was a try.

  7. #27
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,444
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Help with formula to arrange rows in numerical order.

    Quote Originally Posted by MrsFrankieH View Post
    Hi Peter! Ok, update. I thought if I extended the range of the red and blue 'reference lines' ...
    That sounds like you might still be using the J2 formula from post 17, not the updated one from post 18?

    For a larger range the ..
    G2 formula should be exactly as shown in post 23 and copied down to the last row of data in columns A:E.

    H2 formula should change the blue 6 to whatever the last row of data is in columns A:E then copied down to the last row of data in columns A:E.
    =RANK(G2,G$2:G$6,1)+COUNTIF(G$1:G1,G2)

    J2 formula should change the blue 6s to whatever the last row of data is in columns A:E then copied across to column N and down to the last row of data in columns A:E.
    =INDEX(A$2:A$6,MATCH(ROWS(J$2:J2),$H$2:$H$6,0))
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #28
    Board Regular
    Join Date
    Mar 2011
    Posts
    215
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with formula to arrange rows in numerical order.

    Hello Peter! I hope you had a wonderful Sunday!
    The formula works. I did as you said, copied the formula and put in the appropriate cells and it really works (very cool by the way! )
    The problem I'm facing is when I drag the cells. I'm dragging cells G2 to N2 down. When I do that, I still have errors. Are there particular cells I should drag down? For example, when I dragged down from G6, with the exception of a few "Value" errors, everything dragged down great. I tried the same with dragging down from H6, J6, K6, L6, M6., and N6 and I am still getting errors. I'm not sure why it's not working out. Would you like me to do a screenshot of the results? Maybe it's something I'm doing. Thanks again kind Sir.

  9. #29
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,444
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Help with formula to arrange rows in numerical order.

    Quote Originally Posted by MrsFrankieH View Post
    For example, when I dragged down from G6, with the exception of a few "Value" errors,
    If you are getting #VALUE errors when dragging the G2 formula down, then you need to investigate those rows. It means that those rows are somehow different to the sample rows. Since I cannot see them I don't know what the issue is, but surely you need to eliminate whatever is causing those errors before you can successfully arrange your rows.

    If you are providing any more sample data (eg with those errors) then please do so using a method that we can copy/paste into our sheets to test with. We are not able to copy/paste the data from an image like you have been posting. My signature block below has help on that.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  10. #30
    Board Regular
    Join Date
    Mar 2011
    Posts
    215
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with formula to arrange rows in numerical order.

    Wow thank you Peter! I'm going to check out your screenshot help. Using photobucket is what my Dad used to call, a "shlepp". lol.
    I appreciate your help more than i can express!! Thanks again!

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
  •