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

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 01 Oct 2009

Replies - Displaying 1 to 3 of 3

Order Replies By: Most recent | Chronological | Highest Rated

	OK - I get it Rate this: (3/5 from 1 vote) Yes - I see now how record 3 contains everything above.  I still think that it is worth running a "clean up" type macro first and maybe putting the Result ID and Comments columns into a template which contains the macro from before that can then be run to get the desired Results column. 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 02 Oct 2009

	The Blue Sky, Red Shirt, Green Grass and Yellow Sun are part of Record ID 3. Rate this: (3/5 from 1 vote) The Record ID column, in the example, is the only one with merged cells but it is unknown if other columns will be added in the future that may have merge cells.  Is it possible to target the name of the column? 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 02 Oct 2009

	A tricky one - some questions Rate this: (3/5 from 1 vote) OK, I think I get it but have some questions. 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 02 Oct 2009

Displaying page 1 of 1