Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting 63,886 Blank Rows Under My Data | Excel Discussion (Misc queries) | |||
Deleting Rows of Data | Excel Discussion (Misc queries) | |||
Copying data to excel and deleting duplicates | Excel Discussion (Misc queries) | |||
Deleting rows containing common data | Excel Discussion (Misc queries) | |||
Deleting rows using key data | Excel Programming |