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

Selecting and moving merged cells content into one cell in correct row

C

clarky

New Member
Hi, I would appreciate any help with the following please.

We download a spreadsheet from our intranet in the format shown in the attachment. The problem is column M "outcomes" because there can be more than one outcome per ID in column A. This leads to that ID entry having merged cells across all its columns eg rows 5&6 and 31&32&33. There are a possible 11 outcomes but 11 should never be selected, probably a maximum of 4 outcomes for any ID.

Outcomes is the only column that can have multiple entries for an ID and cause cells to merge.

Is there some macro I can run that will take all the outcomes for an ID if there is more than 1 and put them in one cell so that when I unmerge the sheet all the information for that ID is in one row?

Just in case it matters, column F "Title" comes out as hyperlinked.

Thank you for looking. Clarky
 

Attachments

  • Sample.xlsx
    12.1 KB · Views: 5
Good day Clarky


Avoid merging cells
Merged cells can help you arrange values in a meaningful way, but they come with problems -- numerous problems, big problems.
For instance, Excel won't apply column formats to a merged cell unless you select all the columns that comprise the merge.
In addition, not all cell formats stick once you unmerge a cell.
You can't sort a column with merged cells.
You can't even select a single-column range if there's a merged cell in it -- go ahead, try!, the whole column will become merged, not good.
You cannot put a filter on it. The problem is the filter is completely useless because the filter will groan with the "merged cells need to be identically sized." Warning, which in English means you have to make each group of merged cells the same size as the largest group. And you have to find them all!
Merging cells in columns and rows could lead to data lose, bad thing.
Formulas and Functions that refer to merged cells will not work, bad thing.
Don't hesitate to use merged cells if you really need them (you don’t), but they will limit what you can do to the cells and even the columns involved.
Center Across Selection is a far better alternative to merging.
To apply this format, select the cells you want to appear merged and then launch the Alignment group dialog, Ctrl + 1, and click the Alignment tab. Center Across Selection is in the Horizontal drop-down.
You will get the desired look you want but without the merged cells problems.
 
bobhc said:
Good day Clarky


Avoid merging cells
Merged cells can help you arrange values in a meaningful way, but they come with problems -- numerous problems, big problems.
For instance, Excel won't apply column formats to a merged cell unless you select all the columns that comprise the merge.
In addition, not all cell formats stick once you unmerge a cell.
You can't sort a column with merged cells.
You can't even select a single-column range if there's a merged cell in it -- go ahead, try!, the whole column will become merged, not good.
You cannot put a filter on it. The problem is the filter is completely useless because the filter will groan with the "merged cells need to be identically sized." Warning, which in English means you have to make each group of merged cells the same size as the largest group. And you have to find them all!
Merging cells in columns and rows could lead to data lose, bad thing.
Formulas and Functions that refer to merged cells will not work, bad thing.
Don't hesitate to use merged cells if you really need them (you don’t), but they will limit what you can do to the cells and even the columns involved.
Center Across Selection is a far better alternative to merging.
To apply this format, select the cells you want to appear merged and then launch the Alignment group dialog, Ctrl + 1, and click the Alignment tab. Center Across Selection is in the Horizontal drop-down.
You will get the desired look you want but without the merged cells problems.
Click to expand...
 
HI bobhc, yes we've found some of those problems you mentioned and that's why we want to remove them. We haven't set up this formatting, it is how the download comes from our intranet and there is little to no hope of getting that changed.

So are you suggesting that if I highlight the rows that have merged cells as they appear in my sample doc and apply the Centre Across Selection that will allow the sorting and filtering, cutting and pasting without the merged problems? Or are you proposing it a better option before we get to the merged cells stage?
 
If I where you I would sort out the source file so that merged cells are not used, someone has for unknown reasons merged an empty cell with a data cell, strange, bit of work to unmerge (half an hour) but worth the effort, if it is a large source file get someone else to do it.

You have pasted by post but no comment????
 
If you unmerge the merged cells the data moves up one cell and you need to note this, source file needs sorting, someone needs the push out the door....;)

If you unmerge and apply the Centre Across Selection that will allow the sorting and filtering, cutting and pasting.
 
Sorry bobhc, I'm clicking the wrong buttons:( . I certainly agree with your suggestion about sorting out the source document but it is not something I have access to and our IT department is small and it is not on their radar at the moment as they have some big projects on so tweaking this is not going to happen.

I appreciate your thoughts. Clarky
 
You could do it yourself, if the file is not to large. But you would end up doing it again and again until you got that naffed off you would go down to IT with a piece of two by four and make it their number one on the radar (as you can tell my man management skills are of the highest order, training really does pay off:p ).

Bearing in mind that the unmerge will shift the data up one cell and would not be of any use in any formulas or functions you would as I said have to note this, and then move them down one cell, some what tedious to say the least, I am no VBAer but the site does have some of the highest order and they may, I say may, be able to come up with something that would run in all workbooks you download from your server.
 
Bobhc, man management skills are best left to others I think, it's minefield. :)

Thanks for your comments but I will see if someone can come up with some code. If not, then we'll have to carry on as we have done up to now. cheers, clarky
 
and apply the Centre Across Selection that will allow the sorting and filtering, cutting and pasting
Click to expand...
I stand at six foot seven inches and weigh in at 291lbs, and I really was in charge of man management at my last company (early retirement), never had a problem....:)
 
Sounds like you didn't need anything so pretentious as a 'management style', just stand in front of them and suddenly there's no problem. Am envious about your retirement. cheers, Clarky
 
You must log in or register to reply here.
Back
Top