PDA

View Full Version : [SOLVED] Mapping old to new with VBA



Sir Babydum GBE
06-22-2005, 07:15 AM
Hello again.

Another question regarding my current project.

The setting:
All our staff will be re-aligned to new job roles soon.

On my "Delegates" sheet I have the following:
Column F: Contains the delegate's current department.
Column G: Contains the delegate's current Job Role.
Column H: Contains an override option.
Column I: Contains the new role that the delegate will be moving to.

On my "Mapping Sheet" I have the following:
Column A: Contains the manually input department
Column B: Contains the manually input Old Job Role
Column C: Contains the manually input New Job Role

The Task
Somewhere around 3,000 staff will need to be allocated new roles depending on what their current activities are. The mapping will be manually decided by me and entered onto the "mapping" sheet in the columns indicated above (headers are in row 2 - data starts in row 3)
What I'd like to happen is for a macro to look on the "delegates" sheet to see what department and activity each delegate belongs to. Then see if it can find a match of that same combination on the "mapping" sheet. If there is a match, then it will look at the "new role" that is aligned to that combination, and return that role in the "new role" column of the "delegates" sheet (next to that delegate).

If, on the "delegate" sheet, either (or both) the "Department" or "Current Job Role" columns are blank for a given delegate, then it will return a "Please Choose a Role" in the "new role" Column

However, if there is an "Ovr" in the override column for that delegate, it will ignore that line all together.

Please help!

Thanks in advance for any help with this.

Sir Babydum GBE
06-22-2005, 10:03 AM
Ah! I've just realised, I could probably do this with a nested IF(AND formula including a LOOKUP. Can you use a lookup with an AND to look up two values, and if they both match then you return the looked up result?

Bob Phillips
06-22-2005, 11:12 AM
Ah! I've just realised, I could probably do this with a nested IF(AND formula including a LOOKUP. Can you use a lookup with an AND to look up two values, and if they both match then you return the looked up result?

Nope.

You have to use a Match, and then Index the data , like


=INDEX(Sheet2!C1:C100,MATCH(A1&B1,Sheet2!A1:A100&Sheet2!B1:B100,0))

Sir Babydum GBE
06-22-2005, 03:53 PM
As usual xld, you're a life saver.

Ok, so I've done a test of you formula on a worksheet at home (I forgot to send myself the proper worksheet from my place of work - it's the yorkshire in me!)

It's an if formula wher the match will retur a month.

{=IF(OR(ISBLANK(A1),ISBLANK(B1)),"Please Add This Yourself",INDEX(Sheet2!C1:C12,MATCH(A1&B1,Sheet2!A1:A12&Sheet2!B1:B12,0)))}

With CTRL+SHIFT+ENTER this works. But I have a little dilemma still. The purpose of the override column mentioned in the OP is that some people will not be re-aligned accordin to their old roles. Therefore, the few that this anomaly applies to can be put down as an override so that any macro or formula bypasses them.

...I'm thinking out loud here, interrupting myself... The above formula should work if I put it in an empty column, then add another if formula that says "if the override column has an "ovr" in it, then return whatever has already been put into this person's job role, but if it is empty, then do the calculation.

xld you're a genius! I'm gonna try this out now...

Bob Phillips
06-22-2005, 04:03 PM
<snip>The above formula should work if I put it in an empty column, then add another if formula that says "if the override column has an "ovr" in it, then return whatever has already been put into this person's job role, but if it is empty, then do the calculation.

Rather than a separate column, why not just make it an initial condition in the formula, or am I missing what you are suggesting?

Sir Babydum GBE
06-22-2005, 04:10 PM
EDIT: Sorry xld, yes, because in the real spreadsheet (which you've seen) the formula will return the result in the cell that it is created in, so I'll get a circular reference and the text that's meant to be picked up if the OVR appears will be overwritten by the formula itself. So I need to create the formula in a seperate column, then I can paste the values over the "new roles column"

OK, I have one last question:

where do I put an error trap in this formula:

=IF(D1="OVR",E1,IF(OR(ISBLANK(A1),ISBLANK(B1)),"Please Add This Yourself",INDEX(Sheet2!C$2:C$13,MATCH(A1&B1,Sheet2!A$2:A$13&Sheet2!B$2:B$13,0))))

In other words, I want it so that if a match is not found it returns the words "No Match Found" in the cell.

I've managed to do it with the formula:

{=IF(ISERROR(INDEX(Sheet2!C$2:C$13,MATCH(A1&B1,Sheet2!A$2:A$13&Sheet2!B$2:B$13,0))),"No Match Found",IF(D1="OVR",E1,IF(OR(ISBLANK(A1),ISBLANK(B1)),"Please Add This Yourself",INDEX(Sheet2!C$2:C$13,MATCH(A1&B1,Sheet2!A$2:A$13&Sheet2!B$2:B$13,0)))))}

But that is one UGLY formula. Surely there has to be an easier way of handling this... I mean, come on!! :)

Bob Phillips
06-23-2005, 01:39 AM
OK, I have one last question:

where do I put an error trap in this formula:
=IF(D1="OVR",E1,IF(OR(ISBLANK(A1),ISBLANK(B1)),"Please Add This Yourself",INDEX(Sheet2!C$2:C$13,MATCH(A1&B1,Sheet2!A$2:A$13&Sheet2!B$2:B$13,0))))

In other words, I want it so that if a match is not found it returns the words "No Match Found" in the cell.

I've managed to do it with the formula:
{=IF(ISERROR(INDEX(Sheet2!C$2:C$13,MATCH(A1&B1,Sheet2!A$2:A$13&Sheet2!B$2:B$13,0))),"No Match Found",IF(D1="OVR",E1,IF(OR(ISBLANK(A1),ISBLANK(B1)),"Please Add This Yourself",INDEX(Sheet2!C$2:C$13,MATCH(A1&B1,Sheet2!A$2:A$13&Sheet2!B$2:B$13,0)))))}

But that is one UGLY formula. Surely there has to be an easier way of handling this... I mean, come on!!

Always one last question!

First, you don't need to test all of this


ISERROR(INDEX(Sheet2!C$2:C$13,MATCH(A1&B1,Sheet2!A$2:A$13&Sheet2!B$2:B$13,0)))

as the error will be in the Match so the first cut can be


=IF(ISERROR(MATCH(A1&B1,Sheet2!A$2:A$13&Sheet2!B$2:B$13,0)),"No Match Found",IF(D1="OVR",E1,IF(OR(ISBLANK(A1),ISBLANK(B1)),"Please Add This Yourself",INDEX(Sheet2!C$2:C$13,MATCH(A1&B1,Sheet2!A$2:A$13&Sheet2!B$2:B$13,0)))))

Then you can further improve it by using names. Put the cursor in the cell with the formula and define a name of say myMatch for
MATCH(A1&B1,Sheet2!A$2:A$13&Sheet2!B$2:B$13,0). To do this, goto menu
Insert>Name>Define..., put myMatych in the names box, and
=MATCH(A1&B1,Sheet2!A$2:A$13&Sheet2!B$2:B$13,0) in the Refersto box. The formula can then be reduced to


=IF(ISERROR(myMatch),"No Match Found",IF(D1="OVR",E1,IF(OR(ISBLANK(A1),ISBLANK(B1)),"Please Add This Yourself",INDEX(Sheet2!C$2:C$13,myMatch))))

Your company should employ me as a c onsultant :)

Sir Babydum GBE
06-23-2005, 02:43 AM
Always one last question! I have an inquisitive mind - I figure out to do something (usually with tons of help) and think "great that's done now", but then I look at it and think "hmm, there has to be a better way than that" hence the new questions. Feel free at any time to tell me to shut up, grow up, blow up - whatever you prefer really - if you think the questions are too stupid/cheeky/long...


First, you don't need to test all of this

ISERROR(INDEX(Sheet2!C$2:C$13,MATCH(A1&B1,Sheet2!A$2:A$13&Sheet2!B$2:B$13,0)))

as the error will be in the Match so the first cut can be

=IF(ISERROR(MATCH(A1&B1,Sheet2!A$2:A$13&Sheet2!B$2:B$13,0)),"No Match Found",IF(D1="OVR",E1,IF(OR(ISBLANK(A1),ISBLANK(B1)),"Please Add This Yourself",INDEX(Sheet2!C$2:C$13,MATCH(A1&B1,Sheet2!A$2:A$13&Sheet2!B$2:B$13,0)))))

Then you can further improve it by using names. Put the cursor in the cell with the formula and define a name of say myMatch for MATCH(A1&B1,Sheet2!A$2:A$13&Sheet2!B$2:B$13,0). To do this, goto menu
Insert>Name>Define..., put myMatych in the names box, and =MATCH(A1&B1,Sheet2!A$2:A$13&Sheet2!B$2:B$13,0) in the Refersto box. The formula can then be reduced to

=IF(ISERROR(myMatch),"No Match Found",IF(D1="OVR",E1,IF(OR(ISBLANK(A1),ISBLANK(B1)),"Please Add This Yourself",INDEX(Sheet2!C$2:C$13,myMatch))))I knew this, i was just checking to see if you did. You did. Well done! :devil: Only kidding, that's a huge help - and there's a lot there I can use in future - thanks alot.


Your company should employ me as a consultant :)Funnily enough, I work for a massive company - at least by British standards anyway. Across the country we must employ somewhere in the region of 10,000 staff. 3,000ish of which are at the site I work at. I worked as (don't laugh now) the resident Excel and MS Office trainer (the skill level needed for the general management information teams is well within my own understanding). Now i still work in training - but in the design team and no longer on MS Office. However, lil'ol' me is coping with the task of coming up with a contingency tracking device for the training that will be required by practically all staff in the country because of a "re-alignment" of staff duties.

So I said to my management: "Can I suggest please that I use my contacts in the VBA world? Will you give me a modest budget to commission some people to do some work for me - I'll describe what's needed - I guarantee it will work, the work will be done quickly by world class coders...?". "No", came the reply "You do it". "Oh", I said, "Well can we get our own in-house coding department come up with something then?" "No, there is a 'change freeze', you do it".

So here I am drowning in Excel VBA and very grateful that I can come to VBAX with some hard to answer questions. I'm mindful that you're all volunteers, and I have no delusions that my questions deserve your time and consideration. I'm just ecstatic that they get it. :friends:

Bob Phillips
06-23-2005, 02:58 AM
Feel free at any time to tell me to shut up, grow up, blow up - whatever you prefer really - if you think the questions are too stupid/cheeky/long...

Not at all, if I felt like that I don't have to answer. It's my choice.


Funnily enough, I work for a massive company - at least by British standards anyway. Across the country we must employ somewhere in the region of 10,000 staff. 3,000ish of which are at the site I work at.

I uesd to work for one of those, but I got out.


So I said to my management: "Can I suggest please that I use my contacts in the VBA world? Will you give me a modest budget to commission some people to do some work for me - I'll describe what's needed - I guarantee it will work, the work will be done quickly by world class coders...?". "No", came the reply "You do it".

I know that management style well, the bean counters rule all. Shame, as often it is very short-sighted and costs more in the longer run. Judicious use of consultants, where you have a very clear idea of what you want from them, can save a packet. The trick is just not to employ them and let them set the agenda, then it costs lots.

Sir Babydum GBE
06-24-2005, 04:52 AM
Well, i've just heard that our Information Systems department are in the process of ruling out the use of Excel for anything other than ad-hoc minor management info reports (which will need to be destroyed once the info is calculated). no business critical spreadsheets, no scripting...

So it looks very likely that all that work that I (and you) have done is going to come to nothing.

That's just flippin great that is. This is a massive company. Surely this is business-suicide to say that the business is not allowed to rely on electronic systems?

Sorry guys. Just having a moan.

Bob Phillips
06-24-2005, 05:12 AM
Well, i've just heard that our Information Systems department are in the process of ruling out the use of Excel for anything other than ad-hoc minor management info reports (which will need to be destroyed once the info is calculated). no business critical spreadsheets, no scripting...

A typical reaction I am afraid.

Use of Excel can be a problem we all know. It is far too easy to use the product, and far too easy to use it in ways that the user is not c ompetent in, and this can cause problems. But to ban completely, that is poor management. Instead of dealing with the problem, they hide behind diktat. It's like saying drunk drivers kill, so you can only use cars for weddings and funerals.

Reminds me of my last company. The IT Services area was having prob lems convincing people to fill in timesheets. There were some consistent and flagrant abusers. So what did they do, brought in a rule that be late with submitting timesheets on 3 occasions, and you would be subject to disciplinary action. So everyone was threatened because of the actions of a few. And what did people do, they expended their energy on finding ways of not getting caught, in collusion with their immediate bosses I would add, wasted energies by the many. It's the target culture.:p