Solved

Is there a formula to sort names?

Posted on 2015-01-24
8
43 Views
Last Modified: 2015-01-29
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!
0
Comment
Question by:Geekamo
  • 5
  • 3
8 Comments
 
LVL 25

Accepted Solution

by:
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.
EE.xlsx
0
 
LVL 1

Author Comment

by:Geekamo
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

by:Geekamo
ID: 40568721
And too boot, it's an array formula! Haha!
0
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
LVL 25

Expert Comment

by:ProfessorJimJam
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 25

Expert Comment

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

Expert Comment

by:ProfessorJimJam
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

by:Geekamo
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 25

Expert Comment

by:ProfessorJimJam
ID: 40579137
Thanks for feedback.

You are welcome
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

803 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