How to Merge Cells Specially

[luxor]

hi everyone, l am new in vba and need a favour.

l have data type like this:

1 a j
1 b k
1 c l
2 d m
2 e n
2 f o

in this sheet, the numbers in the first column are unique. the following columns may be one or more and are not unique. for example, the first column contains the student numbers and the following ones contain the lectures.

l need a macro to merge cells according to these rules:

1- the repeating values of the first column are merged in one cell as a single value like 1, 2, or 3...

2- then, the following columns' values are merged not losing any data within the range of the first columns unique values.

the result must be like this:

a j
1 b k
c l

d m
2 e n
f o

abc, jkl, def and mno must be in the same cell and written over and over. l hope l have explained well.

thanx...

[SamT]

Large effort should be used to avoid merging cells in data structures.

Is there any way to achive your needs without merging cells?

[luxor]

Large effort should be used to avoid merging cells in data structures.

Is there any way to achive your needs without merging cells?

my sheet is exactly as l explained in my post. let me give a more clear example

john physics
john maths
kate philosophy
kate biology
kate sociology

there are actually two individuals but five rows.l have to merge the unique values and the neigbour columns in the same range. unique values must be written once but the others must be protected.

once l do it, then l can use ms access to create a new excel sheet as unmerged cells. l have no other idea to achieve my goal but of course l am open for the new suggestions.

sincerely...

[SamT]

We don't know what your goal looks like. (an unmmerged Excel sheet)
We can't make suggestions about your goal.

[p45cal]

The following macro works on the two examples you've given. You must select the cells you want it to work on before running the macro. I really suspect it won't work on real data for a variety of reasons:
Sub blah()
Set zzz = Selection
zzz.TextToColumns DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Space:=True
Set StartCll = zzz.Cells(1)
StartCllVal = StartCll.Value
For Each cll In zzz.Cells
Set CurrentCell = cll
With cll
' .Select
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
If .Value <> StartCllVal Then
Application.DisplayAlerts = False: Range(StartCll, .Offset(-1)).MergeCells = True: Application.DisplayAlerts = True
Set StartCll = cll
StartCllVal = StartCll.Value
End If
End With
Next cll
Application.DisplayAlerts = False: Range(StartCll, CurrentCell).MergeCells = True: Application.DisplayAlerts = True
End Sub
Supply a file with realistic data for a more refined solution.



then l can use ms access to create a new excel sheet as unmerged cells.
?!!

Forget how you think you want to get to your goal; what is your goal?

[luxor]

We don't know what your goal looks like. (an unmmerged Excel sheet)
We can't make suggestions about your goal.

as u know, working with merged cells in ms excel has some difficulties. after the operation, the sheet can be imported to ms access and then exported as unmerged cells. but this is just a detail, not the main goal.

[luxor]

thanx for your help, l will try it.

[p45cal]

the sheet can be imported to ms access and then exported as unmerged cells.So what does the file look like after that?!

[SamT]

but this is just a detail, not the main goal.Again. We ask, "What is the main goal?"

[luxor]

Hi friends, l am sorry for being late. My aim is just like the operation in the image below. l need a macro to do this in the selected area automatically, otherwise l will have to do it for too much cells manually. l tried the code above but it merged only the numbers, not the cells containing expressions. Thanx again.


21608

[p45cal]

Here is some code to try:
21611

[SamT]

:devil2:

[luxor]

Here is some code to try:
21611


Thanx. l will try this code.