# Help Requested - Deletion of Duplicate Lines Possible?

#### terry40

##### New Member
Hello All,

I have a huge file with many different part numbers (36,000 +). The part numbers are duplicated on multiple rows but each row includes a part number plus unique description information. I.E.:

.....A...........B.........C..........D...........E

1 part xx ..................purple........
2 part xx .................4 inches......
3 part xx ...............acme brand....

What I want to do is to delete the duplicate row number but still keep the unique info associated. i.e.:

.....A.........B.........C..............D.............................E

1 part xx.............purple, 4 inches, acme brand

Does anyone have any idea of how I can do this? I have already tried auto-filter and keeping unique records but I loose the "4 inches" and "acme brand" info rows that is vital for my report.

Any help would be appreciated,
Thank you!
-stressed and confused.

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
I did some thing like this for another member of the group. I am sending a sample sheet with data on columns A and B and the formulas in F,G,H
This is based on formula given By an expert FRANK GABLE.

use the formfula for your needs and if necesary carefully modify
vlookup all values transpose.xls
ABCDEFGH
1ron30ron302010
2ron20
3ron10
Sheet1

Assuming Part Number in Col.A and corresponding description in Col.B
results will be displayed in Sheet2
Code:
``````Sub test()
Dim a, i As Long, dic As Object, e, flg As Boolean, w()
Set dic = CreateObject("Scripting.Dictionary")
dic.comparemode = vbtextcompare
a = Range("a1").CurrentRegion.Resize(,2).Value
For i = 1 To UBound(a,1)
If Not IsEmpty(a(i,1)) Then
If Not dic.exists(a(i,1)) Then
Else
w = dic(a(i,1))
For Each e In w
If e = a(i,2) Then flg = True : Exit For
Next
If Not flg Then
w(1)= w(1) & ", " & a(i,2)
dic(a(i,1)) = w
End If
flg = False
End If
End If
Next
y = dic.Items : Erase a : Set dic = Nothing
With Sheets("Sheet2").Range("a1")
For i = 0 To UBound(y)
.Offset(i).Resize(,2).Value = y(i)
Next
End With
End Sub``````

thank you both sooooo much for your help!

It is appreciated!!!

Replies
14
Views
446
Replies
19
Views
753
Replies
6
Views
597
Replies
11
Views
695
Replies
1
Views
256

1,219,917
Messages
6,150,946
Members
450,996
Latest member
darko1515s

### 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