Solved

Excel Combining Text

Posted on 2014-02-21
6
303 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 

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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

728 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