# merging rows

#### its_subu

##### New Member
Hello

I have a huge list. My goal is to merge identical values in the list and move up the non identical in the merged list. In the example below at Id 10001 id name location etc are same. I want to merge all the same elements and move to fax number up so that so that the new row has all the unique elements including Fax and phone in the same row. Note that Fax and phone are in different rows.

This is the list I want to merge.
Book1.xls
ABCDEFGH
210001JohnBuildingA1WestAveNYSITE00001(000)999-2222
310001JohnBuildingA1WestAveNYSITE00001(111)222-4444
410002peterBuildingB13KarelAvenueNYSITE00720(111)222-3333
510002peterBuildingB13KarelAvenueNYSITE00720(111)384-2000
Sheet3

THE RESULT I NEED IS LIKE THIS. Identical rows are merged and non identical ones fax and phone are next to each other.
Book1.xls
ABCDEFGH
1Result-Needed
310001JohnBuildingA1WestAveNYSITE00001(000)999-2222(111)222-4444
410002peterBuildingB13KarelAvenueNYSITE00720(111)222-3333(111)384-2000
Sheet2

Any amount of help is appreciated!!!

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Hi
try
Code:
``````Sub test()
Dim dic As Object, x, z
Dim a, i As Long, ii As Long
Set dic = CreateObject("Scripting.Dictionary")
With Sheets("sheet3")
a = .Range("a1").CurrentRegion.Value
For i = 2 To UBound(a, 1)
For ii = 1 To 6: z = z & a(i, ii) & ":": Next
If Not dic.exists(z) Then
ReDim w(1 To 8)
For iii = 1 To 8
w(iii) = a(i, iii)
Next
Else
w = dic(z)
For ii = 7 To 8
If Not IsEmpty(a(i, ii)) Then _
w(ii) = a(i, ii)
Next
dic(z) = w
End If
z = Empty
Next
End With
x = dic.items: Erase a: Set dic = Nothing
With Sheets("sheet2")
With .Range("a1")
.CurrentRegion.ClearContents
.Resize(, 8).Value = _
Sheets("sheet3").Range("a1").Resize(, 8).Value
For i = 0 To UBound(x)
.Offset(i + 1).Resize(, UBound(x(i))) = x(i)
Next
End With
End With
Erase x
End Sub``````

Replies
7
Views
242
Replies
3
Views
290
Replies
17
Views
619
Replies
0
Views
1K
Replies
0
Views
308

1,202,967
Messages
6,052,850
Members
444,603
Latest member
dustinjmangum

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back