exceltoxl.com

Hi there,

 Share on Facebook

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!!!

DOWNLOAD WORKING FILE.xls

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:

OFFSET function tricks

Crack Formula Errors

ISERROR vs IFERROR

Thanks,