Merging Multiple Cells

[rdmit141]

Hi,

I am working on huge data in which I have Device Names in Column B and its Connecting interfaces in Column C.
Column B has single entry against multiple entries in Column C. I want to merge Cells in Column B w.r.t column C cells.automatically. Number Cells/rows are unequal in column C for each entry against Column B.

I know how to do it manually, but data is huge it will take ages to do that way. I am looking for a way in which it can be merged. It should automatically detect next cell in which data is present minus 1 and merge above all cells.

e.g. in below case Cells from Router 1 (Cell B2) to Router 2{(cell B5)-1}i.e B4 should get merge automatically and merging FROM B5 TO B7, B9 to B14 should happen and so on.

Query 2. how give serial number to merged cells, as the number rows are different, Sr.No are not getting assigned automatically.

Query 3. If I Filter Device "Router 1" I am Getting Output for only one row (1) in column B. I should get output with all entries in Column B

Please share a solution how can I get things done with less headache.


[TABLE="width: 50"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Sr No.[/TD]
[TD]Device[/TD]
[TD]Interface[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]Router1[/TD]
[TD]Gig0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]Gig1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Gig2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]Gig3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5[/TD]
[TD]Router2[/TD]
[TD]Gig0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]6[/TD]
[TD][/TD]
[TD]Gig1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]Gig2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]8[/TD]
[TD]Router 3[/TD]
[TD]Gig0[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]Gig1[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD]Gig2[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]Gig3[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD]Gig4[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD]Gig5[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]Router 4[/TD]
[TD]Gig0[/TD]
[/TR]
</tbody>[/TABLE]


Regards,
Rohit

 

[Andrew Poulsom]

It's best to avoid merged cells as they cause more problems than they solve. It would be better to fill in the blank cells. Here's how:

http://www.contextures.com/xlDataEntry02.html

 

[hiker95]

rdmit141,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


You have not displayed what the results should look like.

If I understand you correctly, here is a macro solution for you to consider.

Sample raw data:

Excel 2007
	A	B	C
1	Sr No.	Device	Interface
2	1	Router1	Gig0
3	2		Gig1
4	3		Gig2
5	4		Gig3
6	5	Router2	Gig0
7	6		Gig1
8	7		Gig2
9	8	Router 3	Gig0
10			Gig1
11			Gig2
12			Gig3
13			Gig4
14			Gig5
15		Router 4	Gig0
16
Sheet1

And, after the macro:

Excel 2007
	A	B	C
1	Sr No.	Device	Interface
2	1	Router1	Gig0
3	2	Router1	Gig1
4	3	Router1	Gig2
5	4	Router1	Gig3
6	5	Router2	Gig0
7	6	Router2	Gig1
8	7	Router2	Gig2
9	8	Router 3	Gig0
10	9	Router 3	Gig1
11	10	Router 3	Gig2
12	11	Router 3	Gig3
13	12	Router 3	Gig4
14	13	Router 3	Gig5
15	14	Router 4	Gig0
16
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub rdmit141()
' hiker95, 01/24/2016, ME916480
Dim lr As Long, lra As Long
Application.ScreenUpdating = False
lr = Columns("A:C").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
lra = Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
With Range("B1:B" & lr)
  .SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C"
  .Value = .Value
End With
Err.Clear
On Error Resume Next
With Range("A" & lra + 1 & ":A" & lr)
  .SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C+1"
  .Value = .Value
End With
Err.Clear
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the rdmit141 macro.