10-15-2018 01:51 PM
10-15-2018 01:51 PM
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.
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:
But I want it to be:
10-15-2018 04:02 PM
you have to sort it manually or with macro like below:
mlrc = Cells(Rows.Count, "N").End(xlUp).Row
Range("N1:N" & mlrc).Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("N2:N" & mlrc) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("N2:N" & mlrc)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
10-15-2018 04:03 PM - edited 10-15-2018 04:05 PM
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.
10-16-2018 01:14 PM
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.
by Maverick494 on June 19, 2020
by David Kimball on April 06, 2018
by Jee Soo Han on November 04, 2019