I have two columns of information with a label for the information in column
1 and the data in column 2.
However the data is offset from the labels eg

blank cell Data 1
Label for Data 1 blank cell
blank cell Data 2
blank cell More Data 2
Label for Data 2 blank cell
Label for Data 2 blank cell
blank cell Data 3
Label for Data 3


Now what I want to do is write a macro in Excel 97 to align the data with
the labels.
This apparently simple task (because the number of data cells should equal
the number of label cells for that data and the number of blank cells below
each line of data should equal the number of lines of data) has a spanner in
the works because every now and then the data is missing and here I want to
add a comment to that effect in the data field (before or after realigning.
eg

Data 998
Label for Data 998 blank cell <-----Comment to add here
Label for Data 999 blank cell <-----Comment to add here
Label for Data 999 blank cell
blank cell data 1000
Label for Data 1000 blank cell

To do it manually would take forever as there are thousands of lines of
data.

Is there anyone who can point me in the right direction please.

Hi,

As an alternative to coding you could try a couple of functions;

If your data is in columns 1 & 2 (& you are using R1C1 references) put the
following formulae in

Column 4 : =IF(RC[-3]="",R[1]C,RC[-3])
Column 5 : =IF(RC[-3]="","EXCLUDE",RC[-3])

Add a filter & filter where Column 5 < "EXCLUDE"

Columns 4 and 5 now contain your data, without the blanks.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Zippy" wrote:

I have two columns of information with a label for the information in column
1 and the data in column 2.
However the data is offset from the labels eg

blank cell Data 1
Label for Data 1 blank cell
blank cell Data 2
blank cell More Data 2
Label for Data 2 blank cell
Label for Data 2 blank cell
blank cell Data 3
Label for Data 3


Now what I want to do is write a macro in Excel 97 to align the data with
the labels.
This apparently simple task (because the number of data cells should equal
the number of label cells for that data and the number of blank cells below
each line of data should equal the number of lines of data) has a spanner in
the works because every now and then the data is missing and here I want to
add a comment to that effect in the data field (before or after realigning.
eg

Data 998
Label for Data 998 blank cell <-----Comment to add here
Label for Data 999 blank cell <-----Comment to add here
Label for Data 999 blank cell
blank cell data 1000
Label for Data 1000 blank cell

To do it manually would take forever as there are thousands of lines of
data.

Is there anyone who can point me in the right direction please.

Thanks for that Chris,

That certainly appears to work in the main although the labels with the
missing data in which I would like to put a comment in the data field is
also excluded. It would also be preferable if I could use macro code to do
it as the situation I'm trying to cope with is repeatable in as much as
sometimes the data cells will be missing the data but I don't want to lose
the labels and I need to add a "Missing data" comment to the data cells.

Zippy.


"Chris Marlow" wrote in message
...
Hi,

As an alternative to coding you could try a couple of functions;

If your data is in columns 1 & 2 (& you are using R1C1 references) put the
following formulae in

Column 4 : =IF(RC[-3]="",R[1]C,RC[-3])
Column 5 : =IF(RC[-3]="","EXCLUDE",RC[-3])

Add a filter & filter where Column 5 < "EXCLUDE"

Columns 4 and 5 now contain your data, without the blanks.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Zippy" wrote:

I have two columns of information with a label for the information in
column
1 and the data in column 2.
However the data is offset from the labels eg

blank cell Data 1
Label for Data 1 blank cell
blank cell Data 2
blank cell More Data 2
Label for Data 2 blank cell
Label for Data 2 blank cell
blank cell Data 3
Label for Data 3


Now what I want to do is write a macro in Excel 97 to align the data
with
the labels.
This apparently simple task (because the number of data cells should
equal
the number of label cells for that data and the number of blank cells
below
each line of data should equal the number of lines of data) has a
spanner in
the works because every now and then the data is missing and here I want
to
add a comment to that effect in the data field (before or after
realigning.
eg

Data 998
Label for Data 998 blank cell <-----Comment to add here
Label for Data 999 blank cell <-----Comment to add here
Label for Data 999 blank cell
blank cell data 1000
Label for Data 1000 blank cell

To do it manually would take forever as there are thousands of lines of
data.

Is there anyone who can point me in the right direction please.

Zippy,

Ok. Coding wise, somewhat abridged;

Dim lCopyRow as Long
Dim lLabelRow as Long
Dim lDataRow as Long

lCopyRow=1
lLabelRow=1
lDataRow=1

Do Until IsEmpty(Sheets("Sheet1").Cells(lCopyRow,1)) And
IsEmpty(Sheets("Sheet1").Cells(lCopyRow,2))

If IsEmpty(Sheets("Sheet1").Cells(lCopyRow,1)) Then

Sheets("Sheet2").Cells(lDataRow,2)=Sheets("Sheet1" ).Cells(lCopyRow,2)
lDataRow=lDataRow+1

Else

Sheets("Sheet2").Cells(lLabelRow,1)=Sheets("Sheet1 ").Cells(lCopyRow,1)
lLabelRow=lLabelRow+1

End If

lCopyRow=lCopyRow+1

Loop

A lot of assumptions in here & I've not tested ... but should give you an
idea of where to go.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Zippy" wrote:

Thanks for that Chris,

That certainly appears to work in the main although the labels with the
missing data in which I would like to put a comment in the data field is
also excluded. It would also be preferable if I could use macro code to do
it as the situation I'm trying to cope with is repeatable in as much as
sometimes the data cells will be missing the data but I don't want to lose
the labels and I need to add a "Missing data" comment to the data cells.

Zippy.


"Chris Marlow" wrote in message
...
Hi,

As an alternative to coding you could try a couple of functions;

If your data is in columns 1 & 2 (& you are using R1C1 references) put the
following formulae in

Column 4 : =IF(RC[-3]="",R[1]C,RC[-3])
Column 5 : =IF(RC[-3]="","EXCLUDE",RC[-3])

Add a filter & filter where Column 5 < "EXCLUDE"

Columns 4 and 5 now contain your data, without the blanks.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Zippy" wrote:

I have two columns of information with a label for the information in
column
1 and the data in column 2.
However the data is offset from the labels eg

blank cell Data 1
Label for Data 1 blank cell
blank cell Data 2
blank cell More Data 2
Label for Data 2 blank cell
Label for Data 2 blank cell
blank cell Data 3
Label for Data 3


Now what I want to do is write a macro in Excel 97 to align the data
with
the labels.
This apparently simple task (because the number of data cells should
equal
the number of label cells for that data and the number of blank cells
below
each line of data should equal the number of lines of data) has a
spanner in
the works because every now and then the data is missing and here I want
to
add a comment to that effect in the data field (before or after
realigning.
eg

Data 998
Label for Data 998 blank cell <-----Comment to add here
Label for Data 999 blank cell <-----Comment to add here
Label for Data 999 blank cell
blank cell data 1000
Label for Data 1000 blank cell

To do it manually would take forever as there are thousands of lines of
data.

Is there anyone who can point me in the right direction please.

Thanks very much for that Chris. I'll see what I can make of it.

Zippy

"Chris Marlow" wrote in message
...
Zippy,

Ok. Coding wise, somewhat abridged;

Dim lCopyRow as Long
Dim lLabelRow as Long
Dim lDataRow as Long

lCopyRow=1
lLabelRow=1
lDataRow=1

Do Until IsEmpty(Sheets("Sheet1").Cells(lCopyRow,1)) And
IsEmpty(Sheets("Sheet1").Cells(lCopyRow,2))

If IsEmpty(Sheets("Sheet1").Cells(lCopyRow,1)) Then


Sheets("Sheet2").Cells(lDataRow,2)=Sheets("Sheet1" ).Cells(lCopyRow,2)
lDataRow=lDataRow+1

Else


Sheets("Sheet2").Cells(lLabelRow,1)=Sheets("Sheet1 ").Cells(lCopyRow,1)
lLabelRow=lLabelRow+1

End If

lCopyRow=lCopyRow+1

Loop

A lot of assumptions in here & I've not tested ... but should give you an
idea of where to go.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Zippy" wrote:

Thanks for that Chris,

That certainly appears to work in the main although the labels with the
missing data in which I would like to put a comment in the data field is
also excluded. It would also be preferable if I could use macro code to
do
it as the situation I'm trying to cope with is repeatable in as much as
sometimes the data cells will be missing the data but I don't want to
lose
the labels and I need to add a "Missing data" comment to the data cells.

Zippy.


"Chris Marlow" wrote in message
...
Hi,

As an alternative to coding you could try a couple of functions;

If your data is in columns 1 & 2 (& you are using R1C1 references) put
the
following formulae in

Column 4 : =IF(RC[-3]="",R[1]C,RC[-3])
Column 5 : =IF(RC[-3]="","EXCLUDE",RC[-3])

Add a filter & filter where Column 5 < "EXCLUDE"

Columns 4 and 5 now contain your data, without the blanks.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Zippy" wrote:

I have two columns of information with a label for the information
in
column
1 and the data in column 2.
However the data is offset from the labels eg

blank cell Data 1
Label for Data 1 blank cell
blank cell Data 2
blank cell More Data 2
Label for Data 2 blank cell
Label for Data 2 blank cell
blank cell Data 3
Label for Data 3


Now what I want to do is write a macro in Excel 97 to align the data
with
the labels.
This apparently simple task (because the number of data cells should
equal
the number of label cells for that data and the number of blank
cells
below
each line of data should equal the number of lines of data) has a
spanner in
the works because every now and then the data is missing and here I
want
to
add a comment to that effect in the data field (before or after
realigning.
eg

Data 998
Label for Data 998 blank cell <-----Comment to add here
Label for Data 999 blank cell <-----Comment to add here
Label for Data 999 blank cell
blank cell data 1000
Label for Data 1000 blank cell

To do it manually would take forever as there are thousands of lines
of
data.

Is there anyone who can point me in the right direction please.