Solved

Excel Combining Text

Posted on 2014-02-21
6
301 Views
Last Modified: 2014-02-21
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
0
Comment
Question by:PhxDB
6 Comments
 
LVL 4

Expert Comment

by:mvalencia2003
ID: 39876956
0
 

Author Comment

by:PhxDB
ID: 39876991
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
 
LVL 40

Expert Comment

by:als315
ID: 39877055
Simplest way - to do it with VBA macro. Is it acceptable?
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:PhxDB
ID: 39877061
Not very good at VBA, but will give it a shot.  Thanks.
0
 
LVL 19

Accepted Solution

by:
regmigrant earned 500 total points
ID: 39877074
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
 

Author Closing Comment

by:PhxDB
ID: 39877095
That was exactly what I was looking for.  Thanks regmigrant!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question