Home

Merge two cells one below the other

%3CLINGO-SUB%20id%3D%22lingo-sub-694670%22%20slang%3D%22en-US%22%3EMerge%20two%20cells%20one%20below%20the%20other%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-694670%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20excel%20sheet%20with%20data%20like%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20merge%20cells%20under%20Data%20Column%20corresponding%20to%20the%20same%20servername%20in%20one%20cell.%3C%2FP%3E%3CP%3EFor%20example%20I%20want%2C%20Server1Data1%20and%20Server1Data2%20to%20be%20merged%20and%20appear%20in%20one%20cell.%3C%2FP%3E%3CP%3ESimilarly%20I%20want%20to%20do%20data%20related%20to%20Server2%20and%20so%20on.%3C%2FP%3E%3CP%3EPlease%20advise%2C%20how%20to%20achieve%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-694670%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Emerge%20cells%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-694730%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20two%20cells%20one%20below%20the%20other%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-694730%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F361076%22%20target%3D%22_blank%22%3E%40Sam789%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EData%20is%20like%20below%3A%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EServerName%3C%2FTD%3E%3CTD%3EData%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EServer1%3C%2FTD%3E%3CTD%3EServer1%20Data%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EServer1%20Data%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EServer2%3C%2FTD%3E%3CTD%3EServer2Data%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EServer2Data%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EServer2Data%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-694777%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20two%20cells%20one%20below%20the%20other%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-694777%22%20slang%3D%22en-US%22%3EBy%20all%20means%2C%20don%E2%80%99t%20merge%20unless%20it%20is%20the%20heading%20of%20a%20report.%20For%20data%2C%20never%20merge!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-694856%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20two%20cells%20one%20below%20the%20other%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-694856%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20do%20you%20think%20of%20conditional%20formatting%20(provided%20one%20keeps%20the%20range%20of%20application%20tidy)%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-694930%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20two%20cells%20one%20below%20the%20other%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-694930%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%20I%20want%20to%20merge%20the%20data%20pertaining%20to%20the%20same%20server.%20Like%20if%20I%20am%20manually%20entering%20the%20information%20I%20can%20hit%20Alt%2BEnter%20to%20enter%20text%20on%20a%20new%20line%20in%20the%20same%20cell.%3C%2FP%3E%3CP%3EHowever%20this%20is%20a%20output%20from%20a%20script%20and%20I%20just%20want%20to%20keep%20the%20data%20related%20to%20each%20server%20in%20a%20single%20cell%20as%20opposed%20to%20many%20cells.%20I%20hope%20I%20am%20able%20to%20clarify.%3C%2FP%3E%3CBLOCKQUOTE%3E%3CBR%20%2F%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-695637%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20two%20cells%20one%20below%20the%20other%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-695637%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F361076%22%20target%3D%22_blank%22%3E%40Sam789%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20there%20anything%20useful%20you%20can%20pick%20out%20of%20the%20attached%3F%3C%2FP%3E%3CP%3EI%20misread%20your%20post%20originally%20and%20thought%20you%20were%20trying%20to%20create%20merged%20cells%20as%20opposed%20to%20merging%20the%20content%20of%20multiple%20cells%20to%20a%20single%20cell.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-695805%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20two%20cells%20one%20below%20the%20other%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-695805%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%20I%20did%20not%20get%20it.%20Can%20you%20explain%20please%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-695812%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20two%20cells%20one%20below%20the%20other%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-695812%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F361076%22%20target%3D%22_blank%22%3E%40Sam789%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20file%20contains%20a%20macro%20that%20copies%20the%20content%20of%20cells%20from%20a%20named%20range%20on%20sheet%20'Source'%20and%20writes%20it%20back%20the%20sheet%20'Formatted'%20in%20roughly%20the%20format%20you%20requested.%20%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20button%20'Copy%20server%20data'%20runs%20the%20macro%20whilst%20'Clear%20data'%20clears%20the%20content%20to%20allow%20you%20to%20develop%20the%20macro%20and%20re-import%20the%20data%20to%20test%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-695834%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20two%20cells%20one%20below%20the%20other%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-695834%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%20That's%20great.%20How%20can%20I%20create%20this%20macro%20myself%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-696052%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20two%20cells%20one%20below%20the%20other%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-696052%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F361076%22%20target%3D%22_blank%22%3E%40Sam789%3C%2FA%3E%26nbsp%3B%20Will%20the%20macro%20work%20for%20any%20number%20of%20rows%20and%20columns%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-696461%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20two%20cells%20one%20below%20the%20other%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-696461%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F361076%22%20target%3D%22_blank%22%3E%40Sam789%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAlt%2BF11%3C%2FSPAN%3E%20to%20open%20the%20code%20window.%3C%2FP%3E%3CP%3EInsert%2Fmodule%20to%20define%20a%20module%20for%20the%20code.%3C%2FP%3E%3CP%3ECopy%2FPaste%20the%20module%20across.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAt%20the%20moment%20the%20module%20uses%20a%20named%20range%20'input'%20to%20locate%20your%20original%20'server'%20data.%26nbsp%3B%20If%20you%20do%20not%20wish%20to%20set%20the%20extent%20of%20the%20range%20manually%20before%20using%20the%20macro%20there%20is%20a%20range%20property%20.currentRegion%20that%20includes%20all%20contiguous%20cells%20which%20can%20be%20used%20within%20the%20code.%26nbsp%3B%20Multiple%20columns%20will%20require%20more%20effort%20because%20the%20loop%3C%2FP%3E%3CP%3EFor%20Each%20c%20in%20rngBody%3C%2FP%3E%3CP%3Eand%20the%20writing%20to%20rngOutput%3C%2FP%3E%3CP%3Ewill%20need%20to%20be%20nested%2C%20giving%3C%2FP%3E%3CP%3EFor%20Each%20col%20in%20rngBody%3C%2FP%3E%3CP%3EFor%20Each%20c%20in%20col%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20all%20quite%20possible%20but%20takes%20time%20unless%20you%20are%20experienced%20at%20coding.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Sam789
Occasional Contributor

I have a excel sheet with data like below:

 

Sheet attached.

 

I want to merge cells under Data Column corresponding to the same servername in one cell.

For example I want, Server1Data1 and Server1Data2 to be merged and appear in one cell.

Similarly I want to do data related to Server2 and so on.

Please advise, how to achieve this?

10 Replies

@Sam789 

Data is like below:

ServerNameData
Server1Server1 Data
 Server1 Data
Server2Server2Data
 Server2Data
 Server2Data
By all means, don’t merge unless it is the heading of a report. For data, never merge!

@Twifoo 

What do you think of conditional formatting (provided one keeps the range of application tidy)?

@Twifoo  I want to merge the data pertaining to the same server. Like if I am manually entering the information I can hit Alt+Enter to enter text on a new line in the same cell.

However this is a output from a script and I just want to keep the data related to each server in a single cell as opposed to many cells. I hope I am able to clarify.



 

@Sam789 

I there anything useful you can pick out of the attached?

I misread your post originally and thought you were trying to create merged cells as opposed to merging the content of multiple cells to a single cell.

@Peter Bartholomew 

No I did not get it. Can you explain please?

@Sam789 

The file contains a macro that copies the content of cells from a named range on sheet 'Source' and writes it back the sheet 'Formatted' in roughly the format you requested.  

The button 'Copy server data' runs the macro whilst 'Clear data' clears the content to allow you to develop the macro and re-import the data to test it.

@Peter Bartholomew  That's great. How can I create this macro myself?

@Sam789  Will the macro work for any number of rows and columns?

@Sam789 

Alt+F11 to open the code window.

Insert/module to define a module for the code.

Copy/Paste the module across.

 

At the moment the module uses a named range 'input' to locate your original 'server' data.  If you do not wish to set the extent of the range manually before using the macro there is a range property .currentRegion that includes all contiguous cells which can be used within the code.  Multiple columns will require more effort because the loop

For Each c in rngBody

and the writing to rngOutput

will need to be nested, giving

For Each col in rngBody

For Each c in col

 

It is all quite possible but takes time unless you are experienced at coding.