Sort If Function List Results

Question

I have a list of 200 names, with emails, year of graduation and enrollment date. I am trying to extract a list of emails depending on the YoG and ED. My current formula works (e.g. it only it shows the emails with the criteria I ask for) but the emails in the new list correspond to the cell it is in, making it so I have five emails separated by 195 blanks. I want to arrange all the TRUE answers at the top, and leave the FALSE answers at the bottom, so all the emails appear at the top of the list, and all the blanks at the bottom.

Current Formula:

=IF(AND(H4=$K$2,E4=$K$7),C4,"")

Translation: If entry has the text in Column H that corresponds with the text in K2 AND the text in Column E corresponds with the text in K7, give me the data in Column C. If not, leave the cell blank.

Right now the formula produces this list in Column N:

[Blank]

Email

[Blank]

[Blank}

Email

[Blank]

Email

But I want it to be:

Email

[Blank]

lorenzo kim · Answer

you have to sort it manually or with macro like below:

Sub SortColumnN()
    mlrc = Cells(Rows.Count, "N").End(xlUp).Row
    Range("N1:N" & mlrc).Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("N2:N" & mlrc) _
       , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
       .SetRange Range("N2:N" & mlrc)
       .Header = xlGuess
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply
    End With
End Sub

lorenzo kim · Answer

the above will only sort column N -- if there are adjoining relevant columns, you have to adjust the range in the code.try this in a test sheet... always make a backup for your work.

boborrell · Answer

To be honest, this is something that I, personally, would use a pivot table for.&nbsp; If you have your data in a table, and then create a pivot table off it, using Grad Year, and Enrollment Date for the rows, and Count of Name for the values.&nbsp; If you have some that have blank email addresses, you can set email as a filter, and not display the blanks.&nbsp; On the attached mock-up, you can see all of the information on the Data tab, but then, on the Pivot tab, you can see the pivot table.&nbsp; The first level of the rows column is the Grad Year.&nbsp; If you expand that, it lets you drill down on the year for the Enrollment Date, and then the Quarter, then the Month.&nbsp; Once you get to the level you want, you can double click on the number in the Count column, and it will create a new sheet with only the data that fits your criteria.&nbsp;&nbsp;

Forum Discussion

Sort If Function List Results

Share

Resources