one record split into 3 rows --> Need the 3 rows rolled up into one record

**bcn1988** · 04-25-2013, 05:17 PM

Hello,
I have data that I export into an excel spreadsheet. The number of records varies all the time. The issue I am having with the export is there are certain rows that are “merged” into one record. The reason there are multiple rows rolled up into one record is there’s memo fields that can have a few records that go with one record (please see example file attached)—I know this probably sounds confusing at first, but please see example workbook.

What I need is some VBA that will work in excel 2003, that will take the merged cells that is really one record but is using 3 rows, and take all three rows and roll them up into one single record. I need this to be done for the entire data set.

The workbook I am uploading is only an example and is not the target workbook. I can’t upload the exact data set I am talking about since it holds proprietary information. Please let me know if you have any questions! Thank you!

**scott.s.fower** · 04-25-2013, 08:08 PM

are there ever times when there will be blocks greater than 3 rows? right now in description there are three rows assigned to primary key 1 will there ever be 4 or 5 or more? that makes a big difference

if there is only three max lines per key then this code will work (didn't test on 2003 but it should be the same)

ps if you want there to be ", " between the description elements then change:
Range(pl).Value = one & two & three --------> Range(pl).Value = one & ", " & two & ", " & three just do a find replace to do it all in one shot

Please Login or Register  to view this content.

**bcn1988** · 04-26-2013, 10:29 AM

Hey Scott,

Thanks for the quick response! On the target workbook, it appears that the records with data below the first row are disappearing. Meaning Record 1 data is maintained, and then the rollup of the 2nd and 3rd row data is not maintained and disappears. Also, yes sometimes there are more than 3 rows...I saw one record that was merged which contained up to 12 rows. Any thoughts on the data not being maintained?

Regards,

bcn1988

**scott.s.fower** · 04-26-2013, 01:37 PM

see if this works better, it will take into account more than three lines for any given primary key, hope this is the solution you're looking for

Please Login or Register  to view this content.

**scott.s.fower** · 04-26-2013, 01:39 PM

Meaning Record 1 data is maintained, and then the rollup of the 2nd and 3rd row data is not maintained and disappears.

not quite sure what you mean by this part... can you clarify

**bcn1988** · 04-26-2013, 03:44 PM

Hey Scott,

Thanks again for the quick response! I jumped the gun on this and didn't set up the example workbook correctly...I apologize!!! HUGE MISTAKE on my part.

Column A actually contains text in the target workbook and I really didn't think this was going to be an issue. But it appears it is. Is there some code that you can put before the one you have already created, to insert a column before column A, so now the column you just entered is now column A, and the old column A is now column B. In column A, the format needs to be the same as the cell in column B. There is never an instance where column A or B will be split with rows, like the descriptions. These cell will ALWAYS either be a single cell, or merged cells.

IF:
1) Single Cell --> Then there is only one record with descriptions and this doesn't need to be rolled up
2) Merged Cell --> Then this cell could have 3 rows with descriptions, or 12 rows.

So what I was thinking is in column A (the new column inserted), can be labeled with Primary Key just as the example workbook. Each cell, regardless of single cell or merged cells, will be a count of the number of records in ascending order. So each record goes 1, 2, 3, 4, 5, 6, etc.....

Can we incorporate this into the existing code? Sorry again...my mistake. Please let me know if you have any questions.

****In Regards to your question****
It appears that the rows that needed to be rolled up into one record was just disappearing. So lets say we have 3 rows with descriptions. It appeared to me that the 1st row was maintained. The other two rows were deleted. The other two rows of data I was expecting to see in row 1. However, none of the data that was contained in rows 2 or 3 was rolled up into the 1st row.