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.
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
Hello Peter! I think I figured it out. I'll keep you posted. Thanks again!!
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.
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
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.
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
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!
Like this thread? Share it with others