• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to merge multiple rows to a single cell ?

N

nirmal74

New Member
Hi all,


I am novice in Excel2007. I have been trying to figure out how to reorganize my original data which runs to 2k rows in to more visually friendly format. I need to merge duplicated data in column A, values in column B to be merged in a single cell and values of column C to transposed to 2 columns.


My original data is in the format below.


Texts Alphanumeric_code Locations (Alphanumeric)

aaaaaaa AA10/2 MY01

aaaaaaa AA10/3 MY01

aaaaaaa AB11/2 MY02

bbbbbbb CC25/25 MY02


I would like to have the data in the following way;

Texts MY01 MY02

aaaaaa AA10/2 AB11/2

AA10/3

bbbbbb CC25/25


Can this be done by using formulas? Thanks in advance for all the help given.

Best regards,

Nirmal
 
Hi Nirmal ,


Can you clarify a few details ?


1. You mention merging duplicated data ; in your sample data , the text "aaaaaaa" is repeated thrice ; you want that there should be only one entry with this text.


2. You mention merging values in column B into one cell ; is this merging to be done for the duplicate values in column A ? In your sample , all the three values corresponding to the text "aaaaaaa" ( AA10/2 , AA10/3 and AB11/2 ) are to be merged into one cell.


3. You mention transposing the values in column C ( MY01 and MY02 ) to be transposed into columns C and D. What if there are many more values in this column ? Will all of them be transposed into columns C ,D , E , F ,... ?


Can you give sample data with many more rows / items , so that it will be clearer ?


Narayan
 
Hi NARAYANK991,


To clarify,


1. Yes. It should only have one entry.


2. No the merging should not be done for column A. Column A will have one data to correspond to various values in column B (within a cell).


3. Yes, if more values are in within column C , I need all the values to be transposed to column D, E , F etc.. My data will only have 4 of such values.

My sample data is in the following url.


http://dl.dropbox.com/u/13469587/PS%20Tasks%20V1.xlsx


Many thanks NARAYANK991,for your kind help.


best regards,

Nirmal
 
Hi Nirmal,


I am not able to view your file, firewal. Anyways, here is my solution.


I assume you have your data from row 2 and col A.


Step1: Labeling..

In Row 1, D col, put label, "My Ref Fld"

In Row 1, E col, put label, "Texts"

In Row 1, F col, put label, "My01"

In Row 1, G col, put label, "My02"

You may add more locations in subsequent columns.


Step 2: Formulaes...

D2 shall have this formula =IF(A2=A1,"",A2)

E2 shall have this formula =IF(A2=A3,"",A2)


F2 shall have this formula =IF(D2="",IF(C2=$F$1,CONCATENATE(B2," ",F1),F1),IF(C2=$F$1,B2,""))


G2 shall have this formula =IF(D2="",IF(C2=$G$1,CONCATENATE(B2," ",G1),G1),IF(C2=$G$1,B2,""))


If you see F and G column formula are same and changes only at few places (F1 becomes G1 and $F$1 becomes $G$1) as per location. so If you have more location codes like in col H and I, you need to replace the formula G1's with H1s.. I1s..


Step 3: drag to all rows...

copy formulas from D2 to your last column and paste till the rows of your data.


Step 4: Filter

Filter Col E (Text) for non-blanks..


Step5: Result

You can copy the filtered data to new location with paste as values..


Thats it!


Regards,

Prasad.
 
Hi Prasaddn,


I tried your method, but it didn't seems to get the way I want it. Is there anyway, I can send you my sample file since you are not able to download it through dropbox?


Thanks again.


Best regards,


Nirmal
 
Hi Prasaddn,


I have uploaded the sample file in google docs. I hope you can access it.


https://docs.google.com/open?id=0B8gGf7qwRVbKODU1NjYyNWMtNDY4ZS00Y2ZkLTliOGUtOTIyOWI1ZGExMTQ4


Thanks and best regards,

Nirmal
 
Hi,


It worked exactly the way you wanted it. Are you sure you are following the same steps as detailed above Step 4 and Step 5?


couple of changes I made from your uploaded file is that you have data starting from Row 3, pls remove empty row 1 and "original data" lable from row 2. Let your data label be starting from row 1.


2nd thing, the desired output in your file is starting from column F and row 2 has label. Pls put your formulas and labels as suggested by me.


Once you know how it works you can move the rows and columns to your wish.


If you still have challenges do let me know.
 
In case you are concerned about formatting in TLG/Ctr in desired output coming without newline entry in same cell, then while using concate function CONCATENATE(B2," ",F1) replace space " " with char(10) like CONCATENATE(B2,char(10),F1)
 
Thanks you very much prasaddn.


Your method work just perfectly. I made 2 mistakes, one I did not filter duplicates from my original data and two, I did not wrap text for column F,G,H &I.


Thanks again for your kind help.


Best regards

Nirmal
 
You must log in or register to reply here.
Back
Top