Hi,
I have to submit a statutory report to the UK Office of National Statistics which requires detail of all addresses under the company ownership with totals of the number of employees who work more or less than 30 hrs a week.
My spreadsheet has the following data:
1 line for each employee with their address which is split across 5 cells i.e. first line of address, second line, third line etc. I then have two cells at the end of the data with a marker in one of the cells ( more than 30 hrs, less than 30 hrs )
My question is how to subtotal the number of employees by address but retain the full address in the subtotal. I believe I can only subtotal on one cell of the address.
I have over 26,000 employees split across over 600 addresses to summarise.
I tried using concatenate to join the address data into one cell which does give me the result I need. However I then need to break out the addresses back into 5 cells as this is the format required for the report.
Is there a function I can use to split the combined address data if split by a comma ??
Any comments appreciated.
Bookmarks