Copy multiple rows to once cell

[javifais]

Hello,

I have ports and ip addresses associated with ports and mac addresses. In some cases there may be more than 2 ip addresses associated with a port. I have data that looks like this:

Port	IP address;Mac Address
FA01	192.000.000.12;000.ae.ee
	127.000.000.00;123.aa.bb
FA02	122.000.000.22;lkl.aw.ee
	192.111.111.12;op.we.mn

<tbody>
</tbody>

And I want to combine data in one cell:

FA01	192.000.000.12;000.ae.ee 127.000.000.00;123.aa.bb
FA02	122.000.000.22;lkl.aw.ee 192.111.111.12;op.we.mn

<tbody>
</tbody>

Thanks,
Javifais

 

[JLGWhiz]

See if this does what you want. May require some formatting for column A.

Sub t()
Dim i As Long
With ActiveSheet
    For i = .Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
        If .Cells(i, 1) = "" Then
            .Cells(i - 1, 2) = .Cells(i - 1, 2).Value & Chr(10) & .Cells(i, 2).Value
            Rows(i).Delete
        End If
    Next
End With
End Sub

 

[hiker95]

javifais,

Here is another macro solution for you to consider.

Sample raw data in the active worksheet:

Excel 2007
	A	B
1	Port	IP address;Mac Address
2	FA01	192.000.000.12;000.ae.ee
3		127.000.000.00;123.aa.bb
4
5	FA02	122.000.000.22;lkl.aw.ee
6
7	FA03	192.000.000.12;000.ae.ee
8		127.000.000.00;123.aa.bb
9		122.000.000.22;lkl.aw.ee
10
11	FA04	192.000.000.12;000.ae.ee
12		127.000.000.00;123.aa.bb
13		122.000.000.22;lkl.aw.ee
14		127.000.000.00;123.aa.bb
15
Sheet1

And, after the macro:

Excel 2007
	A	B
1	Port	IP address;Mac Address
2	FA01	192.000.000.12;000.ae.ee 127.000.000.00;123.aa.bb
3
4	FA02	122.000.000.22;lkl.aw.ee
5
6	FA03	192.000.000.12;000.ae.ee 127.000.000.00;123.aa.bb 122.000.000.22;lkl.aw.ee
7
8	FA04	192.000.000.12;000.ae.ee 127.000.000.00;123.aa.bb 122.000.000.22;lkl.aw.ee 127.000.000.00;123.aa.bb
9
10
11
12
13
14
15
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 ConsolidateData()
' hiker95, 10/20/2016, ME971622
Dim Area As Range, sr As Long
Application.ScreenUpdating = False
With ActiveSheet
  For Each Area In Range("B2", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
    With Area
      sr = .Row
      If .Rows.Count > 1 Then
        Range("B" & sr).Value = Join(Application.Transpose(Area), vbLf)
        Rows(sr + 1).Resize(.Rows.Count - 1).Delete
      End If
    End With
  Next Area
End With
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 ConsolidateData macro.