Excel Combining Text

Believe this is an easy question, but just cant figure it out.  Simplified, by spreadsheet has two basic fields; an identifier (A, B, C etc) and a text remarks field.  What I want to do is combine all of the A's remarks, all of the B's remarks etc.  

Understand I could manually do this using transpose for each group, but since it is a very long list, I was hoping for an easier way.  Thanks!

A      Remark1                                    
A      Remark2            A      Remark1      Remark2      Remark3      
A      Remark3      to      B      Remark4      Remark5            
B      Remark4            C      Remark6      Remark7      Remark8      Remark9
B      Remark5                                    
C      Remark6                                    
C      Remark7                                    
C      Remark8                                    
C      Remark9
PhxDBAsked:
Who is Participating?
 
regmigrantConnect With a Mentor Commented:
try the attached - it uses index and match to swap columns for rows as follows
=IF($D2=INDIRECT("A"&COLUMN()-5+MATCH($D2,$A$1:$A$10,0)),INDIRECT("B"&COLUMN()-5+MATCH($D2,$A$1:$A$10,0)),"")

Obviously you will need to adjust the arrays (A1:A10) to match your data and have a unique list of identifiers in column D (use autfilter, copy, unique values only)

let me know if the adjustment is problematic
transpose.xlsx
0
 
PhxDBAuthor Commented:
Actually, that is what I plan to do once I transpose the Remarks into a single row:

A  B2&":"&C2&";"&D2   to make
A  Remarks1; Remarks2; Remarks3

But I cant figure out how to first get them lined up like that.  Tried to use If statements, but that got way to unwieldy  to go down the length of the list.  Thanks
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
als315Commented:
Simplest way - to do it with VBA macro. Is it acceptable?
0
 
PhxDBAuthor Commented:
Not very good at VBA, but will give it a shot.  Thanks.
0
 
PhxDBAuthor Commented:
That was exactly what I was looking for.  Thanks regmigrant!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.