Is there a formula to sort names?

Posted on 2015-01-24
Last Modified: 2015-01-29
Hello Experts!

Please view the attached photo.

Column J, looks at column H.  If it says "Active", then it displays "Last Name, First Name" from columns B & C.
Column K, looks at column H.  If it says "In Active", then it displays "Last Name, First Name" from columns B & C.

In columns L & M, while it is displaying the same names - there is NO FORMULA at work there.  It's an example of what I am trying to accomplish.

I would like column L, to look at column J and (1) group all names to top of list and (2) names are sorted alphabetically.

Is it possible to do what I am looking for, while only using a formula - and a basic formula at that? :)

I know what I want is possible via the use of formulas, I would just prefer the solution to be fairly easy to incorporate into my workbook.

Thank you in advance for your help!
Question by:Geekamo
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
LVL 26

Accepted Solution

ProfessorJimJam earned 500 total points
ID: 40568705
here is the perfect solution for you.
 it would have been easy for me if you would have attached the dummy data.  i had to type them all over again.

just remember that formula in columns L and M are array formula and require Control + Shift + Enter  
it will not give correct result if you just press enter.

this is the only solution with formulas, there is no basic formula to sort a range that has blank cells in ascending order.

Author Comment

ID: 40568719

Whoa,... that's a massive formula! Haha!  It appears to be working, and it appears, this is only possible via a formula as complex as that. I was hoping for a solution that would be a little easier to incorporate into my spreadsheet.

I'm going to take a longer look at the formula, I'm not sure if I can recreate it.


Author Comment

ID: 40568721
And too boot, it's an array formula! Haha!
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

LVL 26

Expert Comment

ID: 40568731
i guess, you dont have to recreate typing formula, basically you can change the range of $J$2:$J$11  to your original unsorted Active data column and then the L1:$L$1 change it to the desired column where you place the formula. i mean just change the letter from L to desired column Letter.  do not change the rest.   similarly for unsorted column $K$2:$K$11  and M1:$M$2
LVL 26

Expert Comment

ID: 40568734
hahaha  you asked complex question that could only be answered with complex array formula :)
LVL 26

Expert Comment

ID: 40568736
not to forget, this array formula is not only for sorting text, but it also takes care of numbers if your range would have any numbers.

Author Comment

ID: 40578968
@ ProfessorJimJam -

So after playing around with your solution, ultimately - I wasn't comfortable using it.  Not because it didn't work, it appeared to work flawlessly. :)  But, I try to keep all of my formulas on a level that I understand.

Yes, after looking at it - I can see how I would only need to change the references to fit my own setup.

I have spent some time, revising my code (since the screenshot I posted above) - and I rewritten many of the formulas.  My current setup, while it involves the use of "helper columns" - it'll have to do, until I get more comfortable using very long complex formulas/array formulas.

My workbook already has one array formula, - and I'll do anything to avoid more.  Haha!

That being said, thank you very much for providing this solution. I will be saving it for sure, because as I get more comfortable with Excel - your solution is a much better way than my own.

Thanks! :)
LVL 26

Expert Comment

ID: 40579137
Thanks for feedback.

You are welcome

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question