I added a column to your table that displays the row number when valid. Column G displays the list of valid row numbers. Column H and I are the associated dates and values.
@ad.dias @SirJB7 Damn. OK, round did a funny thing around Noon on each day. Was trying to get away from doing the Date statement. Shows what corner cutting gets you. Updated sheet attached.
@MediumLebowski It's identifying every person in the A28:E30 table that have start dates before and end dates after the year-quarter in h5:h16. If a person started in or before that quarter, and were discharged after or during that quarter, they're counted. Getting this function to work was...
Brute force, but here's a way to do it. You don't always have the same number of data points per day, so using the average if and adding a Date column seemed a simple way to do it.
@MediumLebowski I took a shot at this, but was getting some different counts than yours. To get it to work without being a pain, I needed to reformat your Corp Fiscal Qtr column to show the years first. Cells D28:E30 have the min/max Assess Dates converted to quarter format.
Here's an issue...
@Brooksy1
Sorry for the response delay, I don't check here every day. Without getting too messy, the attached file seems to do what you want. Hiding Col J will make it look cleaner.
Another look at a solution. I've added a name selection and some other things. You can change the age ranges by altering the yellow values. You also need to make sure the ages match for the start/stop of the different blocks. Otherwise, you lose years in those gaps.
There's the basic data form provided with Excel. It's not very elaborate, but it's easy to activate and works well with simple tables:
https://support.office.com/en-us/article/Add-edit-find-and-delete-rows-by-using-a-data-form-17bca0a4-3ba5-444a-983c-a8ce70609374
See Bosco's comment in #4. By changing the headers to text (ie using ">", "-", or "<" in the header) you stopped the Match function from operating. I'd recommend using 0, 5, 10, 15, 20, and 100 for the headers. The function works with that. If you must do the headers as you have them above, use...
@Dvd42 I've done a few things on this. There's a new Resolved Mentor tab that simply pulls the mentor numbers out from the Condensed tab. This is needed for the loading calculation. I also simplified how you were resolving the Mentor names in the Name tab. To try and reduce the file size, I...
@Dvd42 OK, another concept. I got tired of doing the weightings as the sensitivity shifted too far with the number of locations. This is more brute force, but the end result is a sorted listing of best matches per graduate.
The tab Matrix-2 encodes the level of match (4 all factors: 1...
@Dvd42 Take a look at the attached file. I was playing with different weightings for the three factors and I'm pretty close. I can now differentiate between Institutions, locations, and even departments by varying the acceptable range on the Matrix tab. I don't like the big matrix, but it's...
Just looking at your spreadsheet. There will obviously be situations where there are too many or too few from the two groups to match up everyone. What were you thinking about as this starts happening through the Institutions and locations? Match the first x and leave the rest or start...
It'd really help if you'd upload an dummy file of approximately what you're working with. Also, is there a priority for the matching criteria? You mentioned location first, but could you use a 2,3,4,....n rating for the other attributes?
@e_var Well, here's my copy of your file using the function. I'm not showing any errors on the Schedule tab after picking up data from all tabs. I added headers and a fake ID number to the Sept-Nov tabs to make sure I was hitting the marks, although I don't think that would make a difference...
@e_var The code can be adjusted using the same logic. Basically, you just keep nesting the IF statements. With your existing spreadsheet, it'd look something like this...
You need to correct for the number of lines in "June" and subtract the current row count (row()). By modifying your function slightly, this can be done. For example (from your cell Schedule!A72)...