Deleting rows after copying data
 

Greetings!

Using Excel2002 - I am a beginner when it comes to programming.

Here is my challenge: I have a worksheet that contains data in which I need
to be able to perform the following.

I need to be able to check the ID numbers in column A, and determine if it
is the same ID number from the previous row. If the ID number matches, then
I need to copy data from the second row and add it to the end of the first
row and then delete the second row. This needs to continue to the end of the
data -- currently about 12000 rows. Some IDs will have two records and
others will have up to 15 entries.

Deleting rows after copying data
 

You could do that by using variables to track what the new
value is, as compared to the old value, etc. and then
adding the contents, as you say.

But, what you describe is exactly what Data-Consolidate
does.

Maybe take a look at that from the Data menu... see if it
does what you need. Data-Consolidate can be programmed as
well.


-----Original Message-----
Greetings!

Using Excel2002 - I am a beginner when it comes to
programming.

Here is my challenge: I have a worksheet that contains
data in which I need
to be able to perform the following.

I need to be able to check the ID numbers in column A,
and determine if it
is the same ID number from the previous row. If the ID
number matches, then
I need to copy data from the second row and add it to the
end of the first
row and then delete the second row. This needs to
continue to the end of the
data -- currently about 12000 rows. Some IDs will have
two records and
others will have up to 15 entries.
.

Deleting rows after copying data
 

Mark,

I'll check into that. Thanks for the quick response!

"mark" wrote:

You could do that by using variables to track what the new
value is, as compared to the old value, etc. and then
adding the contents, as you say.

But, what you describe is exactly what Data-Consolidate
does.

Maybe take a look at that from the Data menu... see if it
does what you need. Data-Consolidate can be programmed as
well.


-----Original Message-----
Greetings!

Using Excel2002 - I am a beginner when it comes to
programming.

Here is my challenge: I have a worksheet that contains
data in which I need
to be able to perform the following.

I need to be able to check the ID numbers in column A,
and determine if it
is the same ID number from the previous row. If the ID
number matches, then
I need to copy data from the second row and add it to the
end of the first
row and then delete the second row. This needs to
continue to the end of the
data -- currently about 12000 rows. Some IDs will have
two records and
others will have up to 15 entries.
.

Deleting rows after copying data
 

Well, I tried to get the Data-Cosolidate to do what I am wanting but have not
been successful. I have part of the problem figured out by using the VLookup
function to compare the ID numbers but am not sure how to make the system
copy more than one field at a time. I thought there might be a way to do so
using a macro.

"mark" wrote:

You could do that by using variables to track what the new
value is, as compared to the old value, etc. and then
adding the contents, as you say.

But, what you describe is exactly what Data-Consolidate
does.

Maybe take a look at that from the Data menu... see if it
does what you need. Data-Consolidate can be programmed as
well.


-----Original Message-----
Greetings!

Using Excel2002 - I am a beginner when it comes to
programming.

Here is my challenge: I have a worksheet that contains
data in which I need
to be able to perform the following.

I need to be able to check the ID numbers in column A,
and determine if it
is the same ID number from the previous row. If the ID
number matches, then
I need to copy data from the second row and add it to the
end of the first
row and then delete the second row. This needs to
continue to the end of the
data -- currently about 12000 rows. Some IDs will have
two records and
others will have up to 15 entries.
.

Deleting rows after copying data
 

I have used VLOOKUP extensively in many applications, but
don't understand why you would need it here.

If I understand right, your data is like this:

ID Data1 Data2
a 1 10
a 2 9
b 3 8
b 4 7
b 5 6
c 6 5
d 7 4
e 8 3
e 9 2
f 10 1

and you would want that to consolidate to:

ID Data1 Data2
a 3 19
b 12 21
c 6 5
d 7 4
e 17 5
f 10 1

Is that it? More columns of data don't matter... Data-
Consolidat can handle it. It does have a nasty habit of
not keeping your data column headers, but they can be put
in easily enough.

For an exampmle, if you had the data listed above in cells
A1 to C11, and had that range named rgData, the following
code would summarize your data in cell F1


Sub Macro1()

Range("F1").Select
Selection.Consolidate Sources:=ActiveWorkbook.FullName
& "!rgData", Function:=xlSum, toprow:=False,
LeftColumn:=True, CreateLinks:=False

End Sub

It doesn't quite show up right here, due to the narrowness
of the page, but that's it.

Does that do what you want? Or did I misunderstand what
it is that you are trying to do?







-----Original Message-----
Well, I tried to get the Data-Cosolidate to do what I am
wanting but have not
been successful. I have part of the problem figured out
by using the VLookup
function to compare the ID numbers but am not sure how to
make the system
copy more than one field at a time. I thought there
might be a way to do so
using a macro.

"mark" wrote:

You could do that by using variables to track what the
new
value is, as compared to the old value, etc. and then
adding the contents, as you say.

But, what you describe is exactly what Data-Consolidate
does.

Maybe take a look at that from the Data menu... see if
it
does what you need. Data-Consolidate can be programmed
as
well.


-----Original Message-----
Greetings!

Using Excel2002 - I am a beginner when it comes to
programming.

Here is my challenge: I have a worksheet that
contains
data in which I need
to be able to perform the following.

I need to be able to check the ID numbers in column A,
and determine if it
is the same ID number from the previous row. If the
ID
number matches, then
I need to copy data from the second row and add it to
the
end of the first
row and then delete the second row. This needs to
continue to the end of the
data -- currently about 12000 rows. Some IDs will
have
two records and
others will have up to 15 entries.
.


.

Deleting rows after copying data
 

Mark,

Let's say this is my data:
ID Data1 Data2
a 1 10
a 2 9
b 3 8
b 4 7
b 5 6
c 6 5
d 7 4
e 8 3
e 9 2
f 10 1

What I need to have is this:
ID Data1 Data2
a 1 10 2 9
b 3 8 4 7 5 6
c 6 5
d 7 4
e 8 3 9 2
f 10 1

I don't see how the consolidate function will work for this scenario.


"mark" wrote:

I have used VLOOKUP extensively in many applications, but
don't understand why you would need it here.

If I understand right, your data is like this:

ID Data1 Data2
a 1 10
a 2 9
b 3 8
b 4 7
b 5 6
c 6 5
d 7 4
e 8 3
e 9 2
f 10 1

and you would want that to consolidate to:

ID Data1 Data2
a 3 19
b 12 21
c 6 5
d 7 4
e 17 5
f 10 1

Is that it? More columns of data don't matter... Data-
Consolidat can handle it. It does have a nasty habit of
not keeping your data column headers, but they can be put
in easily enough.

For an exampmle, if you had the data listed above in cells
A1 to C11, and had that range named rgData, the following
code would summarize your data in cell F1


Sub Macro1()

Range("F1").Select
Selection.Consolidate Sources:=ActiveWorkbook.FullName
& "!rgData", Function:=xlSum, toprow:=False,
LeftColumn:=True, CreateLinks:=False

End Sub

It doesn't quite show up right here, due to the narrowness
of the page, but that's it.

Does that do what you want? Or did I misunderstand what
it is that you are trying to do?







-----Original Message-----
Well, I tried to get the Data-Cosolidate to do what I am
wanting but have not
been successful. I have part of the problem figured out
by using the VLookup
function to compare the ID numbers but am not sure how to
make the system
copy more than one field at a time. I thought there
might be a way to do so
using a macro.

"mark" wrote:

You could do that by using variables to track what the
new
value is, as compared to the old value, etc. and then
adding the contents, as you say.

But, what you describe is exactly what Data-Consolidate
does.

Maybe take a look at that from the Data menu... see if
it
does what you need. Data-Consolidate can be programmed
as
well.


-----Original Message-----
Greetings!

Using Excel2002 - I am a beginner when it comes to
programming.

Here is my challenge: I have a worksheet that
contains
data in which I need
to be able to perform the following.

I need to be able to check the ID numbers in column A,
and determine if it
is the same ID number from the previous row. If the
ID
number matches, then
I need to copy data from the second row and add it to
the
end of the first
row and then delete the second row. This needs to
continue to the end of the
data -- currently about 12000 rows. Some IDs will
have
two records and
others will have up to 15 entries.
.


.