Is there a formula to sort names?

Hello Experts!

Please view the attached photo.

Example
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!
LVL 1
GeekamoAsked:
Who is Participating?
 
ProfessorJimJamConnect With a Mentor Commented:
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.
EE.xlsx
0
 
GeekamoAuthor Commented:
@ProfessorJimJam

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.

Thanks!
0
 
GeekamoAuthor Commented:
And too boot, it's an array formula! Haha!
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
ProfessorJimJamCommented:
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
0
 
ProfessorJimJamCommented:
hahaha  you asked complex question that could only be answered with complex array formula :)
0
 
ProfessorJimJamCommented:
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.
0
 
GeekamoAuthor Commented:
@ 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! :)
0
 
ProfessorJimJamCommented:
Thanks for feedback.

You are welcome
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.