Formula or Macro

Hello ,

I am trying to create a formula or a macro that could do this task, would help me tremendously as i have 20 thousand rows to look at and at the moment are manually updated , I know sounds very sad so I hope I can find help in here!

Basically the attached spreadsheet Serial and Job are given by the system column Type in orange is the one that i would like to formulate.

When serial number starts with 41xxx ; 54xxx; has only job no as numerical all should show Tech
When serial number is 43100 and and job number is numerical , also the job number has the letters SO at the end should be AH - SO
When serial number is 43100 or any other serial numbers and job number has at the end POL should be AH - POL
When serial number is 43100 or any other serial numbers and job number has at the end TM should be AH - TM
When serial number starts with 7xxxx and job number is numerical and letters all should show Green
When serial number is 43401 and job number has at the end UP should be UP
When serial number is 43403 and job number has at the end MES and this specific serial number should be MS MES
When serial number is 52xxx and job number numerical to show OLP

Not sure if is possible, but I thought i would ask for help

Serial Job NO Type
41100 50294 Tech
41100 50840 Tech
41100 50969 Tech
41100 50969 Tech
41000 48314 Tech
41000 48314 Tech
41000 48314 Tech
41000 50423 Tech
41200 46766 Tech
41200 NEW19 Tech
41200 NEW19 Tech
41200 50406 Tech
43100 MEL TM AH - TM
43100 50000 AH - SO
43100 LUPTY POL AH - POL
43100 LUPTY POL AH - POL
43100 BOL AHSO AH - SO
43100 BEO AH-SO AH - SO
43401 FLOR UP UP
43401 FLOR UP UP
43403 BAT MES MS MES
43403 BAT MES MS MES
43403 BAT MES MS MES
51520 48987 Tech
51520 50710 Tech
51530 48987 Tech
51530 50710 Tech
52530 50023 OLP
52530 49305 OLP
54800 48314 Tech
766899 ADJ Green
766899 50998 Green
766899 50961 Green
766899 50960 Green


Thank you so much
Angi

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)