Macro to enter person's name in one column if another column is equal to X

[kwilsdon]

Hi,

We have different sales reps who deal with different areas of the UK, companies are allocated to sales reps depending on the start of the company's postcode. E.g. postcode starting AB is allocated to Joe Bloggs, postcode starting NE is allocated to Fred Smith.

I have an Excel spreadsheet containing companies from all over the UK (postcode in column P) and I need the corresponding sales rep's name entered into column D.

The postcodes are in full, so it would read, for example, AB21 3GY rather than just AB, so the macro would need to look at the start of the cell, which could be either two letters or one, as some postcodes have just one letter at the start.

I did try an IF formula rather than a macro, but I think it will get too complicated (or be impossible) as there are many postcodes.

Is anyone able to help?

Many thanks

[stnkynts]

Try this, you will need to edit to match all your sales reps. You could also probably do better using lookup but it would be easier to create with a copy of your workbook;

Please Login or Register  to view this content.

[kwilsdon]

Thanks stnkynts, that's a great help! Just one question: how do I deal with postcodes that only start with one letter rather than two?

Do I need to change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

?

And would that need to be a new section of the macro? (Sorry, novice!)

*edit:

Also, am I able to allocate more than one postcode to the same sales rep in the same line of code or do I need to do a separate section for every postcode? E.g.

Please Login or Register  to view this content.

Or does it need to be

Please Login or Register  to view this content.

etc?

[stnkynts]

To answer your first question. Yes, change the 2 to a 1.

To answer your second question:

Please Login or Register  to view this content.

[kwilsdon]

Thanks again stnkynts, very helpful

[Cutter]

@ kwilsdon

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.