| Home > Forum Home > Developing and Auditing Analytical Models > Concatenate Comments for Rows For Each Record ID Where Record Id is Merged | Share |
Concatenate Comments for Rows For Each Record ID Where Record Id is Merged
Excel Help for Concatenate Comments For Rows For Each Record Id Where Record Id Is Merged in Developing and Auditing Analytical Models
| Forum Topic | Post Reply Login |
Concatenate Comments For Rows For Each Record Id Where Record Id Is Merged | Rate this: (3/5 from 1 vote) |
 | This is a twist on my last post as I have just been thrown a curve ball with a dataset which needs a macro because this spreadsheet scenario happens differently. I have three columns (Record ID, Comment, Results). The Record ID column has multiple associated rows of Comments and the Record ID exists as Merged Data representing a one-to-many relationship—that is—between one merged Record ID there are one or many associated rows of comments. The Results column is added to capture concatenation of the Comments column. Using VBA, I need to: 1. Concatenate Comments for each Record ID in the Results column but it needs to fall on the first (top) row for that merged Record Id. 2. Between each comment should be a carriage return (aka line feed). 3. The code should not target column A, B, or C because the next time I get the spreadsheet, the Comment column could show up as column Z. So the target needs to be the column name. This should happen for each row and continue for the length of the spreadsheet until it finds the last row with a Record ID and should stop after concatenating the results for each Record ID. Can anyone help? Record ID Comment Results I am a fish. I am a fish. I am a dolphin. 1 I am a dolphin. 2 I like gravy. I like gravy Blue sky. Blue sky. Red shirt. Green grass. Yellow sun. Red shirt. Green grass. 3 Yellow sun. 4 Little pink houses. Little pink houses. PS. I made the Record IDs appears as such because the spreadsheet arrives at my desk with the merged cells justified left and bottom. | |
| Michaniker | ||
| Posted by Michaniker on | ||
| Replies - Displaying 1 to 3 of 3 | Order Replies By: Most Recent | Chronological | Highest Rated |
 | Rate this: (3/5 from 1 vote) Is the Record ID column the only one with merged cells? If so, then there are options like either unmerging the cells or have the macro just reference back from the Comment column to the correct Record ID. I don't get "Yellow sun". It appears to belong to 3 but you have put it with 2. This is probably just a mistype. The working out of the columns is not a problem as the macro can just loop through the first row cells until it finds the correct column. I'm wondering, though, whether it wouldn't be easier to run a formatting macro over the spreadsheet to get it into the same format as your last post and then just use that macro. This way, all of the spreadsheets that you recieve would end up in the same format and may be easier for processing later on. |
| Excel Business Forums Administrator | |
| Posted by Excel Helper on |
 | Rate this: (3/5 from 1 vote) For the merged cell containing Record ID 3, "Yellow sun" does belong to it. The Blue Sky, Red Shirt, Green Grass and Yellow Sun are part of Record ID 3. I put a Postscript (PS) on the question to explain that when I view it in Excel, the Record ID column is justified left and bottom. That is why I typed 3 like it was at the bottom of a merged cell. Blue sky would be at the top of the Record ID 3 recordset. Sorry, this is so confusing. I am not sure it I can put an html table in to show it better or attach a spreadsheet. Does this clear things up? |
| Michaniker | |
| Posted by Michaniker on |
 | Rate this: (3/5 from 1 vote) The issues are whether there are other columns of data that you need in the final results, whether they vary, and finally what you want to do with the data at the end, etc. |
| Excel Business Forums Administrator | |
| Posted by Excel Helper on |
| Displaying page 1 of 1 |
| Find relevant Excel templates and add-ins for Concatenate Comments for Rows For Each Record ID Where Record Id is Merged in the Excel Business Solutions Directory |