I work in the building industry, designing housing estates. I have a list of all the housetype design sheets which is based on house design, "housetype" and contains a "natural language" list of all the building plots this design is used on (it might take the form [housetype in column A]H1089 appears on plots [plots in column F]1, 5, 8-12, & 17; H1225 appears on plots 2 & 4). What I want to automatically produce is a list on a new sheet that says what housetype is on what plot (i.e. plot 1 [column A] H1089[column B], plot 2 H1225, plot 3 D1227, plot 4 H1225, plot 5 H1089 and so on).

I've started trying to put a formula together to do this, but ended up using about six functions to just deal with the "&" cases. Can anybody suggest a better way?

Thanks in advance
dJE