Could anyone assist with a problem I've got trying to 'tidy' up a data source
I'm working wth. I'm currently importing a file (dat file) into Excel.

I need to be able to loop through the file and perform the following:

1) Loop through for each entry that begins +BV and contains additional data
in the cell, not = ENSTRH.
e.g. Cell A5 = "+BV Header Test"
When this appears create a new sheet and put the data from this cell into
Sheet2:A2
(If possible it would be useful to not then include the +BV)

2) Continue looping through the entrys and do the same for any entries with
+BY, but put at the side of the one above (e.g. "+BY Value x" exists, so this
would then offset from Cell A5 and get put into B5.

3) The next +BV entry (as per Step 1) will then go onto the next row and so
on...

Thanks for your help on this. Al ( )

Sub ABC()
dim rng as Range, i as Long
Dim k as Long, cell as Range
Dim rng1 as Range
set rng = worksheets("sheet2").Range("A2")
i = 1
set rng1 = Range(cells(1,1),cells(rows.count,1).End(xlup))
for each cell in rng1
if instr(1,cell,"+BV",vbTextCompare) 0 then
rng(i).Value = application.substitute(cell,"+BV","")
i = i + 1
for k = cell.row + 1 to rng1.Rows(rng1.rows.count).row
if instr(1,cells(k,1).Value,"+BY",vbTextCompare) 0 then
rng(i-1,2).Value = application.Substitute(cells(k,1),"+BY","")
exit for
end if
Next
end if
Next
End Sub

However, depending on how your data is set up, this could put duplicate +BY
entries on the second sheet.

--
Regards,
Tom Ogilvy



"Mackay 1979" wrote in message
...
Could anyone assist with a problem I've got trying to 'tidy' up a data
source
I'm working wth. I'm currently importing a file (dat file) into Excel.

I need to be able to loop through the file and perform the following:

1) Loop through for each entry that begins +BV and contains additional
data
in the cell, not = ENSTRH.
e.g. Cell A5 = "+BV Header Test"
When this appears create a new sheet and put the data from this cell into
Sheet2:A2
(If possible it would be useful to not then include the +BV)

2) Continue looping through the entrys and do the same for any entries
with
+BY, but put at the side of the one above (e.g. "+BY Value x" exists, so
this
would then offset from Cell A5 and get put into B5.

3) The next +BV entry (as per Step 1) will then go onto the next row and
so
on...

Thanks for your help on this. Al ( )

Tom,

Can never thank you enough for all of the help that you provide.

Wondered if you could help me a little further on this one....

Is it possible to have the records span across on a row basis (e.g.
transpose the data across the columns, instead of down the rows?).

Also, there will be several headers within the sheet (this is the part which
the "+BV" string indicates, and therefore when the next 'header' appears
Sheet2's data will be returned on a new row).

e.g.
Column A Column B Column C
Row 2: Header 1 (+BV), Value 1 (+BY), Value 2 (+BY)...........
Row 3: Header 2 (+BV), Value 1 (+BY), Value 2 (+BY)...........

Also, how easy would it be to include an additional criteria to include
other possible values that may exist between each +BV entry? (e.g. currently
the only value is defined by +BV, it may be that there could be a +BA entry -
however, I'm still working through my datasource @ the moment so this is
something I'm still looking @).

Thanks again Tom, many thanks - Al.

"Tom Ogilvy" wrote:

Sub ABC()
dim rng as Range, i as Long
Dim k as Long, cell as Range
Dim rng1 as Range
set rng = worksheets("sheet2").Range("A2")
i = 1
set rng1 = Range(cells(1,1),cells(rows.count,1).End(xlup))
for each cell in rng1
if instr(1,cell,"+BV",vbTextCompare) 0 then
rng(i).Value = application.substitute(cell,"+BV","")
i = i + 1
for k = cell.row + 1 to rng1.Rows(rng1.rows.count).row
if instr(1,cells(k,1).Value,"+BY",vbTextCompare) 0 then
rng(i-1,2).Value = application.Substitute(cells(k,1),"+BY","")
exit for
end if
Next
end if
Next
End Sub

However, depending on how your data is set up, this could put duplicate +BY
entries on the second sheet.

--
Regards,
Tom Ogilvy



"Mackay 1979" wrote in message
...
Could anyone assist with a problem I've got trying to 'tidy' up a data
source
I'm working wth. I'm currently importing a file (dat file) into Excel.

I need to be able to loop through the file and perform the following:

1) Loop through for each entry that begins +BV and contains additional
data
in the cell, not = ENSTRH.
e.g. Cell A5 = "+BV Header Test"
When this appears create a new sheet and put the data from this cell into
Sheet2:A2
(If possible it would be useful to not then include the +BV)

2) Continue looping through the entrys and do the same for any entries
with
+BY, but put at the side of the one above (e.g. "+BY Value x" exists, so
this
would then offset from Cell A5 and get put into B5.

3) The next +BV entry (as per Step 1) will then go onto the next row and
so
on...

Thanks for your help on this. Al ( )

Sub ABC()
dim rng as Range, i as Long
Dim cell as Range, rw as Long
Dim rng1 as Range
set rng = worksheets("sheet2").Range("A1")
rw = 0
set rng1 = Range(cells(1,1),cells(rows.count,1).End(xlup))
for each cell in rng1
if instr(1,cell,"+BV",vbTextCompare) 0 then
rw = rw + 1
i = 1
rng.offset(rw,0).Value = application.substitute(cell,"+BV","")
else if instr(1,cell.Value,"+BY",vbTextCompare) 0 then
rng.offset(rw,i).Value = application.Substitute(cell,"+BY","")
i = i + 1
End if
Next
End Sub

Might do it.

--
Regards,
Tom Ogilvy


"Mackay 1979" wrote in message
...
Tom,

Can never thank you enough for all of the help that you provide.

Wondered if you could help me a little further on this one....

Is it possible to have the records span across on a row basis (e.g.
transpose the data across the columns, instead of down the rows?).

Also, there will be several headers within the sheet (this is the part
which
the "+BV" string indicates, and therefore when the next 'header' appears
Sheet2's data will be returned on a new row).

e.g.
Column A Column B Column C
Row 2: Header 1 (+BV), Value 1 (+BY), Value 2 (+BY)...........
Row 3: Header 2 (+BV), Value 1 (+BY), Value 2 (+BY)...........

Also, how easy would it be to include an additional criteria to include
other possible values that may exist between each +BV entry? (e.g.
currently
the only value is defined by +BV, it may be that there could be a +BA
entry -
however, I'm still working through my datasource @ the moment so this is
something I'm still looking @).

Thanks again Tom, many thanks - Al.

"Tom Ogilvy" wrote:

Sub ABC()
dim rng as Range, i as Long
Dim k as Long, cell as Range
Dim rng1 as Range
set rng = worksheets("sheet2").Range("A2")
i = 1
set rng1 = Range(cells(1,1),cells(rows.count,1).End(xlup))
for each cell in rng1
if instr(1,cell,"+BV",vbTextCompare) 0 then
rng(i).Value = application.substitute(cell,"+BV","")
i = i + 1
for k = cell.row + 1 to rng1.Rows(rng1.rows.count).row
if instr(1,cells(k,1).Value,"+BY",vbTextCompare) 0 then
rng(i-1,2).Value =
application.Substitute(cells(k,1),"+BY","")
exit for
end if
Next
end if
Next
End Sub

However, depending on how your data is set up, this could put duplicate
+BY
entries on the second sheet.

--
Regards,
Tom Ogilvy



"Mackay 1979" wrote in message
...
Could anyone assist with a problem I've got trying to 'tidy' up a data
source
I'm working wth. I'm currently importing a file (dat file) into
Excel.

I need to be able to loop through the file and perform the following:

1) Loop through for each entry that begins +BV and contains
additional
data
in the cell, not = ENSTRH.
e.g. Cell A5 = "+BV Header Test"
When this appears create a new sheet and put the data from this cell
into
Sheet2:A2
(If possible it would be useful to not then include the +BV)

2) Continue looping through the entrys and do the same for any
entries
with
+BY, but put at the side of the one above (e.g. "+BY Value x" exists,
so
this
would then offset from Cell A5 and get put into B5.

3) The next +BV entry (as per Step 1) will then go onto the next row
and
so
on...

Thanks for your help on this. Al ( )