Excel Problem

[Vince, Pt. II]

Looking for ideas here; leading off, I think my best bet is to actually use a database instead, but I do not have Access, and while I may be able to get it, I am completely unfamiliar with the program.

Anyhow, I'm tracking statistics for employees at various headquarters. These employees are going to accrue certain counting stats, but they may transfer to different HQs over time. I am currently using a master list and vlookup to grab HQ information on a per-employee basis...unfortunately when an employee transfers and I update the master list, all of their old counting stats are re-associated with the new headquarters.

I feel like I'm missing something simple in terms of a way to figure this out...my latest idea is a fairly convoluted transfer list where I countif based on employee name and then offset my way to the new HQ based on the date.

[spleen1015]

Can you have a vlookup to every HQ for every employee and just add them together?

Edit - Have a tab for each HQ.

[Vince, Pt. II]

I don't know if I follow. Here's an example of the stats we're keeping, if that helps:

In July, Ron Weasley got detention five times. We track these detentions by listing the date, the employee, and the class. We use our master list and a vlookup to associate these detentions to house Gryffindor, Ron's HQ. On August 1st, he transferred from house Gryffindor to house Hufflepuff. When he transfers, it now looks like Hufflepuff had five additional detentions in July that should stay with Gryffindor.

[GoldenCrest Games]

It looks to me like the detentions are the record you want to track, and the student/house are fields in the record.

Detention #12534: House=Griffendorf, Student=Ron, Date=dd/mm/yyyy
Detention #12535: House=Hufflepuff, Student=Ron, Date=dd/mm/yyyy

I think if you reorganize your data around detention records, the rest would be easier to sort/pull

Thoughts?

[Vince, Pt. II]

Yeah, it may be that we simply have to log the HQ independently with each record, rather than using the employee to look up the HQ.

Tracking these is unfortunately a manual process, so I've tried to keep the data entry to a minimum (date, employee, record).

[Bobble]

First, the cool kids are using INDEX(MATCH()) instead of VLOOKUP. I personally can't seem to break the habit but it's supposed to be faster in a large sheet.

It might be a little more complicated but if you have info on when Ron was in Grryffindor and when in Hufflepuff, can't you use that data along with the date of the detention to pin it on the right House? Basically, use the name and date to find the House responsible for the infraction.

Lastly, classic Ron...

[Vince, Pt. II]

INDEX(MATCH()), really? Huh.

As for the dates...that's the plan, I'm just not sure how I'm going to do it. I don't have a reference for date of transfer unless I create it myself. So my thought is to simply append my master list with new entries for each employee as they transfer. Count the number of times they appear in the list, then start comparing the date of the infraction to the date of the most recent transfer. If the transfer is after the recent infraction, keep looking backward.

[cartman]

This does sound like a better suited task for Access. However, I am a DB guy, so my bias leans towards using DBs for things like this.

[Vince, Pt. II]

Yeah, I've been meaning to start teaching myself more DB stuff, but I never seem to get around to it.

[Bobble]

Originally Posted by Vince, Pt. II

INDEX(MATCH()), really? Huh.

As for the dates...that's the plan, I'm just not sure how I'm going to do it. I don't have a reference for date of transfer unless I create it myself. So my thought is to simply append my master list with new entries for each employee as they transfer. Count the number of times they appear in the list, then start comparing the date of the infraction to the date of the most recent transfer. If the transfer is after the recent infraction, keep looking backward.

INDEX(MATCH()) is also supposed to be better able to handle multiple criteria. This example: https://www.deskbright.com/excel/ind...iple-criteria/ is closer to what you're looking to do.

[Bobble]

Interesting problem. I have an INDEX(MATCH()) solution that I put in my dropbox: Dropbox - DammitRon.xlsx

The only thing is that the dates have to be most recent listed first. I imagine there are creative ways to get around that but I haven't attempted them. I also have an unreasonable aversion to using array formulae but I have used one here.

To explain, the INDEX(MATCH()) is used in the C:C column to determine the House of the offending party on that date.

Let me know if you have questions or if this isn't what you meant.

[Bobble]

Trola,

You could probably get a very elegant solution with SUMPRODUCT as well, but I'm not too good with SUMPRODUCT.

[Vince, Pt. II]

I got buried in the second half of this week, sent to Manteca for a meeting then shipped off to a leadership conference in Sacramento. Wanted to say thanks for the index(match()) solution - worked like a charm. I love the simplicity of multiplying both logical expressions by one another and matching it against a true outcome.