Solved

Excel Combining Text

Posted on 2014-02-21
6
300 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 39

Expert Comment

by:als315
ID: 39877055
Simplest way - to do it with VBA macro. Is it acceptable?
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

809 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