I love Excel... when I know what I'm doing
February 15, 2011 7:46 AM Subscribe
ExcelFilter: I have Worksheet1 and Worksheet2. Both worksheets contain PersonName on the X-axis, ClassName on the Y-axis. I need to run some comparison between the two. Thinking of doing this on a 3rd worksheet. Here's where it gets tricky:
1. There are several thousand PersonNames.
2. There are about 100 CourseNames.
3. There is volatility in the PersonNames and CourseNames; they will be constantly changing, especially, the PersonNames.
Basically, what I need to do is check if a person does not have the same registration status in both Worksheet1 or Worksheet2. (Registration would be marked by an X in the intersecting cell between PersonName and CourseName.) So it could be 4 possibilities; registration in Worksheet1 only, Worksheet 2 only, registration in both, or registration in neither.
To do this, I am thinking of creating a 3rd worksheet, where we have the PersonNames and CourseNames, and then putting a formula in each intersecting cell to check for differences.
Now, because there is volatility - i.e. the PersonNames will be constantly changing, and to a lesser extent the CourseNames, it's not as simple as just punching in a formula checking Worksheet1's XnYn against Worksheet2 XnYn. I am thinking I need some sort of lookup -- i.e., tell Excel to look at the CourseName and then find the column with the same CourseName in the other worksheet, then do the same with PersonName, and then finally compare the cells.
Note that this comparison process would need to be reusable. That is, think of it this way: Worksheet1 is generated March 1st. Worksheet2 is generated March 15th. I run the comparison. On March 30th, I generate a Worksheet3, which is then compared against my Worksheet2. And so on...
Things I am wondering:
1. Is this the right approach?
2. What do I do about people being added, removed, and the same with courses?
3. Should I be using a database instead? I'm hoping this can be done in Excel easily, because my Access skills are very rusty.
I will be here for the next couple of hours, so please fire off any questions you need answered. :-) Thanks!
Basically, what I need to do is check if a person does not have the same registration status in both Worksheet1 or Worksheet2. (Registration would be marked by an X in the intersecting cell between PersonName and CourseName.) So it could be 4 possibilities; registration in Worksheet1 only, Worksheet 2 only, registration in both, or registration in neither.
To do this, I am thinking of creating a 3rd worksheet, where we have the PersonNames and CourseNames, and then putting a formula in each intersecting cell to check for differences.
Now, because there is volatility - i.e. the PersonNames will be constantly changing, and to a lesser extent the CourseNames, it's not as simple as just punching in a formula checking Worksheet1's XnYn against Worksheet2 XnYn. I am thinking I need some sort of lookup -- i.e., tell Excel to look at the CourseName and then find the column with the same CourseName in the other worksheet, then do the same with PersonName, and then finally compare the cells.
Note that this comparison process would need to be reusable. That is, think of it this way: Worksheet1 is generated March 1st. Worksheet2 is generated March 15th. I run the comparison. On March 30th, I generate a Worksheet3, which is then compared against my Worksheet2. And so on...
Things I am wondering:
1. Is this the right approach?
2. What do I do about people being added, removed, and the same with courses?
3. Should I be using a database instead? I'm hoping this can be done in Excel easily, because my Access skills are very rusty.
I will be here for the next couple of hours, so please fire off any questions you need answered. :-) Thanks!
Do it in Access. Your Access skills are rusty, but you'll be able to polish them and do the project more quickly than you can manage to coax Excel into doing it.
posted by sonic meat machine at 8:48 AM on February 15, 2011
posted by sonic meat machine at 8:48 AM on February 15, 2011
Response by poster: Ach. I was afraid that was the answer. Does anyone have any good Access (or Excel) resources they could point me towards? Thanks.
posted by demagogue at 9:17 AM on February 15, 2011
posted by demagogue at 9:17 AM on February 15, 2011
Best answer: My Access friend at work swears by accessmonster.com.
posted by look busy at 10:23 AM on February 15, 2011
posted by look busy at 10:23 AM on February 15, 2011
Response by poster: Thanks. I will look at accessmonster. If anyone has an inkling of the direction I should be heading in, e.g., what my queries would be looking like, please post away.
posted by demagogue at 10:54 AM on February 15, 2011
posted by demagogue at 10:54 AM on February 15, 2011
This thread is closed to new comments.
posted by milestogo at 8:01 AM on February 15, 2011