Hi All,
Part 1
I have a delimited file which is carrying 16,000 lines of data, only 7000 are relevant. I recorded a macro to clean it up but have since realised its cell referenced and I need it to delete rows which contain certain text.
In column B I have several different rows that repeat starting with for example 'year', 'date','days'. Is there a macro which could search through column b detect cells with certain text and delete the rows.
Part 2
After deleting the rows I then have to insert a new row column A with different departments in reference to sub headings in colum B. The code on column B for example is "05021 Banking" which repeats every x amount of lines and I will manually type Banking into column A and drag down until a see a new code for example'00222 Corporate'.
<tbody>
</tbody><colgroup><col><col></colgroup>
Is there a macro or formula which could run down column A check numerical code first 5 digits, insert text description and fill down until finds the new code and keep repeating task until hits row 16,500. The codes do repeat and other info is also contained column B which can be ignored. At the moment I just manually input the descriptions and drag down which only takes 5-10mins but I assume there must be a better way.
Thanks
John
Part 1
I have a delimited file which is carrying 16,000 lines of data, only 7000 are relevant. I recorded a macro to clean it up but have since realised its cell referenced and I need it to delete rows which contain certain text.
In column B I have several different rows that repeat starting with for example 'year', 'date','days'. Is there a macro which could search through column b detect cells with certain text and delete the rows.
Part 2
After deleting the rows I then have to insert a new row column A with different departments in reference to sub headings in colum B. The code on column B for example is "05021 Banking" which repeats every x amount of lines and I will manually type Banking into column A and drag down until a see a new code for example'00222 Corporate'.
Department | |
Banking | 01133 Banking |
Banking | 780115151 |
Banking | |
Banking | |
Banking | **Tota |
Banking | |
Banking | |
Corporate | 05021 Corporate |
Corporate | 78075512 |
Corporate | |
Corporate | |
Corporate | 78115219 |
Corporate | |
Corporate | |
Corporate | 2222222 |
Corporate |
<tbody>
</tbody><colgroup><col><col></colgroup>
Is there a macro or formula which could run down column A check numerical code first 5 digits, insert text description and fill down until finds the new code and keep repeating task until hits row 16,500. The codes do repeat and other info is also contained column B which can be ignored. At the moment I just manually input the descriptions and drag down which only takes 5-10mins but I assume there must be a better way.
Thanks
John