• 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.

Combining multiple rows into one

B

BirdyBee

New Member
Hi I am seeking help to identify an easy way to combine the contents of a number of rows into one.

I have uploaded an example file. In the attached file I want to combine rows 2-4 into row 5 - the $ columns need to be summed and in cell G5 I want to list all data in the rows above separated by commas.

Then I will be deleting rows 2-4 so only row 5 remains, which will summarise all the data for Service C. I am wondering if there is a formula I can insert in cells A5:G5 that will sum/combine the data easily.

I am working in a spreadsheet where there is mixed data - some services have one row per service (which is what I want) and some have multiple rows so I am manually combining the services with multiple rows into one row so they are like the others.

Any ideas would be gratefully received.
 

Attachments

Hi ,

Rather than use formulae to combine data from multiple rows into one row , and then deleting the redundant rows , why not use a macro to do both ?

As it is , Excel is not very flexible and powerful when it comes to concatenating cells.

Narayan
 
Unfortunately I haven't written a macro before - would you have any tips about where to start or ideas about a tutorial focused on this particular problem?
 
Hi ,

Sorry ; unlike tutorials about Excel formulae , there are no tutorials about such specific aspects of VBA ; you have to start at the beginning and go through most of the basics before you can start coding.

You can check out these links :

http://excelvbatutor.com/vba_tutorial.html

http://www.excel-vba.com/excel-vba-contents.htm

http://www.homeandlearn.org/

http://www.tutorialspoint.com/vba/

http://www.excel-pratique.com/en/vba.php

However , for this specific case , there are a lot of members on this forum who can write the code and upload the file.

Wait for some time.

Narayan
 
Hi ,

Can you see the attached file ?

The macro has been given a short-cut of CTRL m

Pressing the 2 keys CTRL and m together will run the macro named CombineRows ; what this will do is insert the combined rows on Sheet2.

Confirm whether it does what you wanted , and then I'll explain what you need to do to transfer this to your actual working file.

Narayan
 

Attachments

Thank you Narayan! The macro worked but in the result I think a couple of rows haven't worked out exactly as I wanted. If you look in the original file I sent, I would need to macro to deliver the exact content that is in rows 5-7 and delete rows 2-4.

The result produced by your macro is close but there seems to be a bit of overlap between Orgs A and B. I think a slight tweak is all that is needed.

After the macro is run, Org B and Org C should look exactly as they are in the original file because they are only single row entries which I do not want to change. Org A should appear exactly as it does in row 5 (which combines rows 2 to 4) and rows 2-4 should be deleted.

I see that Chandoo is coming to Brisbane, Australia this year - I would like to get along to one of his classes if I can, I'm sure it would be a lot of fun after using his site!

Thanks again for your help I am very appreciative.
 
Hi ,

I think you should take a closer look at the input data ; I had changed it.

I have intentionally not taken the route of deleting rows for the present , so that verification is possible. Once you confirm that the macro is working the way you want it to , this can be incorporated.

Narayan
 
Hi of course you are right - sorry I didn't notice the input sheet. It all looks great - the macro is returning exactly what I want and works beautifully!

So yes if you could advise how I can put it into my real spreadsheet (which has heaps more columns than the example, including static and formula driven text and number data) that would be great. I will try whatever you suggest tomorrow at work where I have my spreadsheet.

Thanks heaps!
 
Hi ,

Can you describe the layout of your working spreadsheet ( worksheet ) ?

1. What is the range of columns which needs to be transferred ? In the sample file this was columns A through G.

2. What are the columns which need to be just transferred from input to output ? In the sample file , these were columns A and B.

3. What are the numeric columns which need to be totalled ? In the sample file , these were columns C through F.

4. What are the columns which need to be concatenated ? In the sample file , there was only one column , column G.

That is all.

Narayan
 
Hi Narayan

Here is the information required, as per your questions:

1. What is the range of columns which needs to be transferred ? In the sample file this was columns A through G.

A through to AW
2. What are the columns which need to be just transferred from input to output ? In the sample file , these were columns A and B.

All columns except those listed below.
3. What are the numeric columns which need to be totalled ? In the sample file , these were columns C through F.

AD, AE, AF, AG
4. What are the columns which need to be concatenated ? In the sample file , there was only one column , column G.

P, AO, AP, AQ

Thanks for your help.
 
You must log in or register to reply here.
Back
Top