Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Is there a formula to sort names?

Posted on 2015-01-24
Medium Priority
48 Views
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!
0
Question by:Geekamo
[X]
###### 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 2000 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.
EE.xlsx
0

LVL 1

Author Comment

ID: 40568719
@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

LVL 1

Author Comment

ID: 40568721
And too boot, it's an array formula! Haha!
0

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
0

LVL 26

Expert Comment

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

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.
0

LVL 1

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! :)
0

LVL 26

Expert Comment

ID: 40579137
Thanks for feedback.

You are welcome
0

## Featured Post

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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 the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
###### Suggested Courses
Course of the Month11 days, 2 hours left to enroll

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

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