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!
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

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

Technology Partners: 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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

739 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