I imported a very large flat file dump (187K line) and want to move ranges of cells to a new worksheet if a cell in the range contains a specific sub-string. These strings can appear multiple times in the file dump as well as multiple times within a range/block. So whenever the string appears in a block, move it to the next empty column in the new WS.
The range/blocks can vary from a dozen lines to over 1000. They all begin with two cells of 10 "s" each, followed by a blank cell. The first cell in each range begins with a specific sub-string (ACO: ) and has a sub-string I need to search: either a 7 digit number, or a username. Ideally, I'd like to spawn a new WS named with the sub-string I'm searching by, and moving the blocks to separate columns in that WS.
Even more ideally, if it can run through a named list of values and move the ranges/blocks to new WS's...with each block in a separate column (not asking for a home run, but if anyone is feeling the challenge).
Sample data set:
ColA
[TABLE="width: 425"]
<tbody>[TR]
[TD]ssssssssss[/TD]
[/TR]
[TR]
[TD]ssssssssss[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]ACO: web.site.com: 1234567 - user.name1[/TD]
[/TR]
[TR]
[TD]DataPoint1[/TD]
[/TR]
[TR]
[TD]DataPoint2[/TD]
[/TR]
[TR]
[TD]DataPoint3[/TD]
[/TR]
[TR]
[TD]DataPoint4[/TD]
[/TR]
[TR]
[TD]DataPoint5[/TD]
[/TR]
[TR]
[TD]DataPoint11[/TD]
[/TR]
[TR]
[TD]DataPoint12[/TD]
[/TR]
[TR]
[TD]DataPoint13[/TD]
[/TR]
[TR]
[TD]DataPoint16[/TD]
[/TR]
[TR]
[TD]DataPoint17[/TD]
[/TR]
[TR]
[TD]ssssssssss[/TD]
[/TR]
[TR]
[TD]ssssssssss[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]ACO: web2.site2.com: 1234567 - user.name1[/TD]
[/TR]
[TR]
[TD]DataPoint1[/TD]
[/TR]
[TR]
[TD]DataPoint2[/TD]
[/TR]
[TR]
[TD]DataPoint11[/TD]
[/TR]
[TR]
[TD]DataPoint12[/TD]
[/TR]
[TR]
[TD]DataPoint13[/TD]
[/TR]
[TR]
[TD]DataPoint14[/TD]
[/TR]
[TR]
[TD]ssssssssss[/TD]
[/TR]
[TR]
[TD]ssssssssss[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]ACO: web3.site3.coml: 1236549 - user.name1[/TD]
[/TR]
[TR]
[TD]DataPoint1[/TD]
[/TR]
[TR]
[TD]DataPoint2[/TD]
[/TR]
[TR]
[TD]DataPoint4[/TD]
[/TR]
[TR]
[TD]DataPoint5[/TD]
[/TR]
[TR]
[TD]DataPoint9[/TD]
[/TR]
[TR]
[TD]ssssssssss[/TD]
[/TR]
[TR]
[TD]ssssssssss[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]ACO: web4.site4.com: 9876542 - user.name1[/TD]
[/TR]
[TR]
[TD]DataPoint1[/TD]
[/TR]
[TR]
[TD]DataPoint9[/TD]
[/TR]
[TR]
[TD]DataPoint10[/TD]
[/TR]
[TR]
[TD]ssssssssss[/TD]
[/TR]
[TR]
[TD]ssssssssss[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]ACO: web5.site5.com: 9876542 - user.name1[/TD]
[/TR]
[TR]
[TD]DataPoint1[/TD]
[/TR]
[TR]
[TD]DataPoint4[/TD]
[/TR]
[TR]
[TD]DataPoint5[/TD]
[/TR]
[TR]
[TD]DataPoint9[/TD]
[/TR]
[TR]
[TD]DataPoint10[/TD]
[/TR]
[TR]
[TD]DataPoint11[/TD]
[/TR]
[TR]
[TD]DataPoint15[/TD]
[/TR]
[TR]
[TD]ssssssssss[/TD]
[/TR]
[TR]
[TD]ssssssssss[/TD]
[/TR]
</tbody>[/TABLE]
The range/blocks can vary from a dozen lines to over 1000. They all begin with two cells of 10 "s" each, followed by a blank cell. The first cell in each range begins with a specific sub-string (ACO: ) and has a sub-string I need to search: either a 7 digit number, or a username. Ideally, I'd like to spawn a new WS named with the sub-string I'm searching by, and moving the blocks to separate columns in that WS.
Even more ideally, if it can run through a named list of values and move the ranges/blocks to new WS's...with each block in a separate column (not asking for a home run, but if anyone is feeling the challenge).
Sample data set:
ColA
[TABLE="width: 425"]
<tbody>[TR]
[TD]ssssssssss[/TD]
[/TR]
[TR]
[TD]ssssssssss[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]ACO: web.site.com: 1234567 - user.name1[/TD]
[/TR]
[TR]
[TD]DataPoint1[/TD]
[/TR]
[TR]
[TD]DataPoint2[/TD]
[/TR]
[TR]
[TD]DataPoint3[/TD]
[/TR]
[TR]
[TD]DataPoint4[/TD]
[/TR]
[TR]
[TD]DataPoint5[/TD]
[/TR]
[TR]
[TD]DataPoint11[/TD]
[/TR]
[TR]
[TD]DataPoint12[/TD]
[/TR]
[TR]
[TD]DataPoint13[/TD]
[/TR]
[TR]
[TD]DataPoint16[/TD]
[/TR]
[TR]
[TD]DataPoint17[/TD]
[/TR]
[TR]
[TD]ssssssssss[/TD]
[/TR]
[TR]
[TD]ssssssssss[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]ACO: web2.site2.com: 1234567 - user.name1[/TD]
[/TR]
[TR]
[TD]DataPoint1[/TD]
[/TR]
[TR]
[TD]DataPoint2[/TD]
[/TR]
[TR]
[TD]DataPoint11[/TD]
[/TR]
[TR]
[TD]DataPoint12[/TD]
[/TR]
[TR]
[TD]DataPoint13[/TD]
[/TR]
[TR]
[TD]DataPoint14[/TD]
[/TR]
[TR]
[TD]ssssssssss[/TD]
[/TR]
[TR]
[TD]ssssssssss[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]ACO: web3.site3.coml: 1236549 - user.name1[/TD]
[/TR]
[TR]
[TD]DataPoint1[/TD]
[/TR]
[TR]
[TD]DataPoint2[/TD]
[/TR]
[TR]
[TD]DataPoint4[/TD]
[/TR]
[TR]
[TD]DataPoint5[/TD]
[/TR]
[TR]
[TD]DataPoint9[/TD]
[/TR]
[TR]
[TD]ssssssssss[/TD]
[/TR]
[TR]
[TD]ssssssssss[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]ACO: web4.site4.com: 9876542 - user.name1[/TD]
[/TR]
[TR]
[TD]DataPoint1[/TD]
[/TR]
[TR]
[TD]DataPoint9[/TD]
[/TR]
[TR]
[TD]DataPoint10[/TD]
[/TR]
[TR]
[TD]ssssssssss[/TD]
[/TR]
[TR]
[TD]ssssssssss[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]ACO: web5.site5.com: 9876542 - user.name1[/TD]
[/TR]
[TR]
[TD]DataPoint1[/TD]
[/TR]
[TR]
[TD]DataPoint4[/TD]
[/TR]
[TR]
[TD]DataPoint5[/TD]
[/TR]
[TR]
[TD]DataPoint9[/TD]
[/TR]
[TR]
[TD]DataPoint10[/TD]
[/TR]
[TR]
[TD]DataPoint11[/TD]
[/TR]
[TR]
[TD]DataPoint15[/TD]
[/TR]
[TR]
[TD]ssssssssss[/TD]
[/TR]
[TR]
[TD]ssssssssss[/TD]
[/TR]
</tbody>[/TABLE]