Home

Sort If Function List Results

%3CLINGO-SUB%20id%3D%22lingo-sub-271755%22%20slang%3D%22en-US%22%3ESort%20If%20Function%20List%20Results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-271755%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20list%20of%20200%20names%2C%20with%20emails%2C%20year%20of%20graduation%20and%20enrollment%20date.%20I%20am%20trying%20to%20extract%20a%20list%20of%20emails%20depending%20on%20the%20YoG%20and%20ED.%20My%20current%20formula%20works%20(e.g.%20it%20only%20it%20shows%20the%20emails%20with%20the%20criteria%20I%20ask%20for)%20but%20the%20emails%20in%20the%20new%20list%20correspond%20to%20the%20cell%20it%20is%20in%2C%20making%20it%20so%20I%20have%20five%20emails%20separated%20by%20195%20blanks.%20I%20want%20to%20arrange%20all%20the%20TRUE%20answers%20at%20the%20top%2C%20and%20leave%20the%20FALSE%20answers%20at%20the%20bottom%2C%20so%20all%20the%20emails%20appear%20at%20the%20top%20of%20the%20list%2C%20and%20all%20the%20blanks%20at%20the%20bottom.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrent%20Formula%3A%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3DIF(AND%3CSPAN%20class%3D%22s1%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3EH4%3C%2FSPAN%3E%3D%3CSPAN%20class%3D%22s1%22%3E%24K%242%3C%2FSPAN%3E%2C%3CSPAN%20class%3D%22s3%22%3EE4%3C%2FSPAN%3E%3D%3CSPAN%20class%3D%22s4%22%3E%24K%247%3C%2FSPAN%3E%3CSPAN%20class%3D%22s1%22%3E)%3C%2FSPAN%3E%2C%3CSPAN%20class%3D%22s5%22%3EC4%3C%2FSPAN%3E%2C%22%22)%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3ETranslation%3A%20If%20entry%20has%20the%20text%20in%20Column%20H%20that%20corresponds%20with%20the%20text%20in%20K2%20AND%20the%20text%20in%20Column%20E%20corresponds%20with%20the%20text%20in%20K7%2C%20give%20me%20the%20data%20in%20Column%20C.%20If%20not%2C%20leave%20the%20cell%20blank.%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3ERight%20now%20the%20formula%20produces%20this%20list%20in%20Column%20N%3A%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%5BBlank%5D%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%5BBlank%5D%3C%2FP%3E%3CP%20class%3D%22p1%22%3EEmail%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%5BBlank%5D%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%5BBlank%7D%3C%2FP%3E%3CP%20class%3D%22p1%22%3EEmail%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%5BBlank%5D%3C%2FP%3E%3CP%20class%3D%22p1%22%3EEmail%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3EBut%20I%20want%20it%20to%20be%3A%3C%2FP%3E%3CP%20class%3D%22p1%22%3EEmail%3C%2FP%3E%3CP%20class%3D%22p1%22%3EEmail%3C%2FP%3E%3CP%20class%3D%22p1%22%3EEmail%3C%2FP%3E%3CP%20class%3D%22p1%22%3EEmail%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%5BBlank%5D%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%5BBlank%5D%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-271755%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EIF%20Formula%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EINDEX%20MATCH%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-272254%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%20If%20Function%20List%20Results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-272254%22%20slang%3D%22en-US%22%3E%3CP%3ETo%20be%20honest%2C%20this%20is%20something%20that%20I%2C%20personally%2C%20would%20use%20a%20pivot%20table%20for.%26nbsp%3B%20If%20you%20have%20your%20data%20in%20a%20table%2C%20and%20then%20create%20a%20pivot%20table%20off%20it%2C%20using%20Grad%20Year%2C%20and%20Enrollment%20Date%20for%20the%20rows%2C%20and%20Count%20of%20Name%20for%20the%20values.%26nbsp%3B%20If%20you%20have%20some%20that%20have%20blank%20email%20addresses%2C%20you%20can%20set%20email%20as%20a%20filter%2C%20and%20not%20display%20the%20blanks.%26nbsp%3B%20On%20the%20attached%20mock-up%2C%20you%20can%20see%20all%20of%20the%20information%20on%20the%20Data%20tab%2C%20but%20then%2C%20on%20the%20Pivot%20tab%2C%20you%20can%20see%20the%20pivot%20table.%26nbsp%3B%20The%20first%20level%20of%20the%20rows%20column%20is%20the%20Grad%20Year.%26nbsp%3B%20If%20you%20expand%20that%2C%20it%20lets%20you%20drill%20down%20on%20the%20year%20for%20the%20Enrollment%20Date%2C%20and%20then%20the%20Quarter%2C%20then%20the%20Month.%26nbsp%3B%20Once%20you%20get%20to%20the%20level%20you%20want%2C%20you%20can%20double%20click%20on%20the%20number%20in%20the%20Count%20column%2C%20and%20it%20will%20create%20a%20new%20sheet%20with%20only%20the%20data%20that%20fits%20your%20criteria.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-271793%22%20slang%3D%22en-US%22%3ERE%3A%20Sort%20If%20Function%20List%20Results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-271793%22%20slang%3D%22en-US%22%3E%3CP%3Ethe%20above%20will%20only%20sort%20column%20N%20--%20if%20there%20are%20adjoining%20relevant%20columns%2C%20you%20have%20to%20adjust%20the%20range%20in%20the%20code.%3C%2FP%3E%3CP%3Etry%20this%20in%20a%20test%20sheet...%20always%20make%20a%20backup%20for%20your%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-271792%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%20If%20Function%20List%20Results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-271792%22%20slang%3D%22en-US%22%3E%3CP%3Eyou%20have%20to%20sort%20it%20manually%20or%20with%20macro%20like%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20SortColumnN()%3CBR%20%2F%3E%20%20%20%20mlrc%20%3D%20Cells(Rows.Count%2C%20%22N%22).End(xlUp).Row%3CBR%20%2F%3E%20%20%20%20Range(%22N1%3AN%22%20%26amp%3B%20mlrc).Select%3CBR%20%2F%3E%20%20%20%20ActiveWorkbook.Worksheets(%22Sheet1%22).Sort.SortFields.Clear%3CBR%20%2F%3E%20%20%20%20ActiveWorkbook.Worksheets(%22Sheet1%22).Sort.SortFields.Add%20Key%3A%3DRange(%22N2%3AN%22%20%26amp%3B%20mlrc)%20_%3CBR%20%2F%3E%20%20%20%20%20%20%20%2C%20SortOn%3A%3DxlSortOnValues%2C%20Order%3A%3DxlAscending%2C%20DataOption%3A%3DxlSortNormal%3CBR%20%2F%3E%20%20%20%20With%20ActiveWorkbook.Worksheets(%22Sheet1%22).Sort%3CBR%20%2F%3E%20%20%20%20%20%20%20.SetRange%20Range(%22N2%3AN%22%20%26amp%3B%20mlrc)%3CBR%20%2F%3E%20%20%20%20%20%20%20.Header%20%3D%20xlGuess%3CBR%20%2F%3E%20%20%20%20%20%20%20.MatchCase%20%3D%20False%3CBR%20%2F%3E%20%20%20%20%20%20%20.Orientation%20%3D%20xlTopToBottom%3CBR%20%2F%3E%20%20%20%20%20%20%20.SortMethod%20%3D%20xlPinYin%3CBR%20%2F%3E%20%20%20%20%20%20%20.Apply%3CBR%20%2F%3E%20%20%20%20End%20With%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Joshua Cayetano
Occasional Visitor

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]

[Blank]

Email

[Blank]

[Blank}

Email

[Blank]

Email

 

But I want it to be:

Email

Email

Email

Email

[Blank]

[Blank] 

3 Replies

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

 

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.

To be honest, this is something that I, personally, would use a pivot table for.  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.  If you have some that have blank email addresses, you can set email as a filter, and not display the blanks.  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.  The first level of the rows column is the Grad Year.  If you expand that, it lets you drill down on the year for the Enrollment Date, and then the Quarter, then the Month.  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.  

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies