Populating Cells based on Values from others cells

Hi, I have to import web data on a daily basis that gets sorted before its converted to a .csv file for final use.

After sorting is completed a status column is added to the end in the 'AV' column.

If any 'New row' are added to the bottom, then i need add more data as outlined below. If not then all is well.

In the example provided i need to be able to populate the 'AD' column based on data from 'B' column under the following criteria;

B	AD
Accessory	Accessories/Stands & Brackets
Monitor	Peripherals/Monitors
Displays	Peripherals/Monitors
Thin client Mon	Peripherals/Monitors

I have piece of code that was kindly created by 'AB33' on this forum, that was created for another similar workbook, but i can't seem to get it to function.
The code is ;

Please Login or Register  to view this content.

I also need to populate the cells in the 'AB' column based on the same criteria from the 'B' column as follows;

B	AB
Displays	Monitor
Monitor	Monitor
Thin client Mon	Monitor
Accessory	Accessories

once that's completed i need to to fill down the values and formulas from the last cells in ranges 'AA-AC' 'AE-AJ' and 'AL-AO'.

I realise this maybe asking a lot but these daily tasks are so laborious and any automation would be immensely appreciated.

Regards
John

Thanks for your time and effort anyway AB. Much appreciated.