Jun 16 2019 05:00 AM
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?
Jun 16 2019 07:22 AM
Data is like below:
ServerName | Data |
Server1 | Server1 Data |
Server1 Data | |
Server2 | Server2Data |
Server2Data | |
Server2Data |
Jun 16 2019 10:49 AM
Jun 16 2019 02:27 PM
What do you think of conditional formatting (provided one keeps the range of application tidy)?
Jun 16 2019 04:52 PM
@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.
Jun 17 2019 04:03 AM
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.
Jun 17 2019 04:32 AM
No I did not get it. Can you explain please?
Jun 17 2019 04:42 AM
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.
Jun 17 2019 04:50 AM
@Peter Bartholomew That's great. How can I create this macro myself?
Jun 17 2019 06:21 AM
@Sam789 Will the macro work for any number of rows and columns?
Jun 17 2019 08:44 AM
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.