Expert: Victor Lan - 11/7/2009

Question
QUESTION: Dear Victor,

I'm making a financial spreadsheet.  I need to be able to sort 5 columns and up to 20 rows (can be less) of data automatically and alphabetically, putting the empty rows last, using formulas.  The columns and rows would be as follows:

Creditor   Interest   Total     Months   Date
we         $2722.84   $7722.84  64       March-15

and up to 19 more rows of data.

I need it automatic so that when stuff changes, these rows sort automatically and without using macros.

I hope you can help, I can't figure it out and must get an answer from somewhere.

Thank You,

Greg

ANSWER: Hi Greg,

In order to sort it automatically, you will need to use VBA macro to capture worksheet events. However, it may not be efficient if the worksheet contains large amount of data.

Let me know if you need a VBA solution and I'll assist.
Thanks.

Regards,
Victor LAN

---------- FOLLOW-UP ----------

QUESTION: Victor,

Thank you for your quick response.  Due to the nature of what I'm trying to do, I can't use VBA macros.  I actually originally used a macro to do this but have found I can't leave it like that,  I intend to use a program to turn my spreadsheet into a program itself, but the program I am using is unable work with macros, I really need to be able to sort these rows using formulas.  I found some data on how to do it using the OFFSET and COUNTA functions together, but I didn't really understand what it was doing.  The link to where this info was is:

http://www.sulprobil.com/html/sorting.html

I think this is how I can do it but this does not explain how to organize the rows alphabetically when you have 5 columns of data to sort.

Does that help?  Could you use this to figure out how to do it for my application?

Thank You,

Greg

Answer
Hi Greg,

I looked at the website and it shows us how to use Excel formulas (without VBA) to generate a sorting key (shown as a rank column on the website) using the data. It works for both alphabetical and numerical data. I tested it using sample data and it works well.

After the sorting key is generated by following the procedure on the website, you will have to sort the data using the sorting key. I can think of a few ways that one could do that.

- Sort feature in Excel.
- Using VBA to sort the key in Excel. This could be done by a button, or a Workbook Open event.

I'm not sure what program you are using and its limitation, but if you have a sorting key it should be possible to sort the data easily.

I hope the above helps, if not, please do not hesitate to write back.
Thank you.

Regards,
Victor