Our document contains around 40K entries of various geographical locations. Each of these locations belong to one of 10 major areas. Currently we have a tab for each of these 10 major areas. We have to (each month) look through the first column and match up with of the 64 locations to decide which major area it falls under. We cut those rows and have to paste them into the correct tab which is named after the major area (10 major areas, 64+ sub areas).
In my head it looks simple to automate, if cell A1 contains Cumbria, select the entire row and move it into the major area which looks after Cumbria.
Below is an example, since I am at work and unable to upload any examples or post any screenshots.
So, 10 tabs named Jan to Dec, if any cell in the first column contains Alpha or Bravo, move the entire row into tab Jan.
If any cell in the first column contains Charlie or Delta, move the entire row into the Feb tab.
Repeat for the entire list.
There may be some issues as some entries are all uppercase or lower, so I need to capture both. If it fails to find any match, then leave the data as is.
I have created my spreadsheet with 3 tabs so far.
Data - Containing over 40K rows of information. The vital information is in the first column, sub areas)
Locatoons - This tab contains a list in the first column of the 10 major areas (alpha, Bravo etc). To the right (columns B onwards) are the corresponding sub areas for.
Example:
Ideally I would create 10 tabs (Alpha, Bravo, etc) and each row containing any sub area would to into that tab. So Essex into Charlie tab, cornwall into Alpha and so forth
Can anyone help me with this mission? We currently have to do this manually and the staff are ready to strike!
In my head it looks simple to automate, if cell A1 contains Cumbria, select the entire row and move it into the major area which looks after Cumbria.
Below is an example, since I am at work and unable to upload any examples or post any screenshots.
So, 10 tabs named Jan to Dec, if any cell in the first column contains Alpha or Bravo, move the entire row into tab Jan.
If any cell in the first column contains Charlie or Delta, move the entire row into the Feb tab.
Repeat for the entire list.
There may be some issues as some entries are all uppercase or lower, so I need to capture both. If it fails to find any match, then leave the data as is.
I have created my spreadsheet with 3 tabs so far.
Data - Containing over 40K rows of information. The vital information is in the first column, sub areas)
Locatoons - This tab contains a list in the first column of the 10 major areas (alpha, Bravo etc). To the right (columns B onwards) are the corresponding sub areas for.
Example:
Code:
[TABLE="width: 1141"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Alpha[/TD]
[TD]AVON[/TD]
[TD]BRISTOL[/TD]
[TD]CORNWALL[/TD]
[TD]DEVON[/TD]
[TD]DORSET[/TD]
[TD]GLOUCESTERSHIRE[/TD]
[TD]SOMERSET[/TD]
[TD]WILTSHIRE. [/TD]
[/TR]
[TR]
[TD]Bravo[/TD]
[TD]COUNTY DURHAM[/TD]
[TD]DURHAM[/TD]
[TD]NORTHUMBERLAND[/TD]
[TD]YORKSHIRE[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD]BEDFORDSHIRE[/TD]
[TD]CAMBRIDGESHIRE[/TD]
[TD]DERBYSHIRE[/TD]
[TD]ESSEX[/TD]
[TD]HERTFORDSHIRE[/TD]
[TD]LEICESTERSHIRE[/TD]
[TD]LINCOLNSHIRE[/TD]
[TD]NORTHAMPTONSHIRE[/TD]
[/TR]
[TR]
[TD]Delta[/TD]
[TD]BERKSHIRE[/TD]
[TD]BUCKINGHAMSHIRE[/TD]
[TD]HAMPSHIRE[/TD]
[TD]KENT[/TD]
[TD]OXFORDSHIRE[/TD]
[TD]SUSSEX[/TD]
[TD]SURREY[/TD]
[TD]ISLE OF WIGHT[/TD]
[/TR]
</tbody>[/TABLE]
Ideally I would create 10 tabs (Alpha, Bravo, etc) and each row containing any sub area would to into that tab. So Essex into Charlie tab, cornwall into Alpha and so forth
Can anyone help me with this mission? We currently have to do this manually and the staff are ready to strike!