« List File Names in a Webpage (no FUNCTIONS) | Excel Time Stamp (quick tip) » |
Sort Data Using Formulas
How-to's, Excel TipsHi there,
After a long gap I am again connecting with you to share Excel tips. I was very busy in recording videos and preparing worksheets (templates) for Excel Master School. However, here is a short tip for you. Sorting range of data using formulas.
...
To sort the data range first need to use helper column next to data column. Example, Column C is a data range then ColumnB will be helper column.
Then, use COUNTIF formula to extract the order range in numeric values. The complete formula goes like this:
COUNTIF(DATA RANGE, "<="&FIRST DATA CELL)
The real formula looks like this:
=COUNTIF($C$3:$C$10,"<="&C3)
This will give where the data stands in a range of cells. Then, it is very easy to sort this text using VLOOKUP, right? But, here we need to use ROW() formula to sort from ascending to descending or vice-versa.
See how your faces are glowing by knowing this tip. :)
If you still needs clarification how VLOOKUP does. Here we go:
VLOOKUP(ROW()-ROW($F$2),TABLE RANGE,2,FALSE)
the real formula looks like this:
VLOOKUP(ROW()-ROW($F$2),$B$3:$C$10,2,0)
That's it. Magic works!!!
DID YOU LIKE THIS TIP?
Have you used this tip in your work. Did you find any challenges, ideas, thoughts, then why are you waiting for. Use this space to share what you think with our readers using the below comments section.
If you like this post then, please do share it on your social networking sites using our icons placed around the blog.
If you want to support me for maintaining this blog, then please do REGISTER here for free and spread this site with your co-hearts.
SOME IMPORTANT LINKS:
Thanks,
You must be logged in to see the comments. Log in now!
If you have no account yet, you can register now! (It only takes a few seconds)
Recent comments