VBA to remove records

[Shon]

I am just starting to learn VBA and really struggling with the following
problem. Any help would be appreciated!.

I have a list of Excel records which this time round is 4 columns and 45000
records. the columns will always be the same with the same headers but the
number of records will change each time. My data looks like the following;

Item No Trans Qty Doc Number Location
4001 100 50001 EL4
4001 100 50001 EL7
4001 100 50001 EL9
4001 100 50001 EL4
4002 50 30001 3BJ
4002 50 30001 2DC
4002 50 30001 3BJ
4003 40 20001 EL4
4003 40 20001 EL7

I want to use a macro to look down the data and where the Item No, Trans Qty
and Doc Number are the same then keep the 1st record for the 1st location and
remove the other records. There may however be some instances where the Item
No, Trans Qty, Doc Number AND Location are the same e.g. Item No 4001,
Location EL4, and in these cases the records would need to be kept. I would
therefore expect to see the following result;

Item No Trans Qty Doc Number Location
4001 100 50001 EL4
4001 100 50001 EL4
4002 50 30001 3BJ
4002 50 30001 3BJ
4003 40 20001 EL4
4003 40 20001 EL7

Any ideas? Many thanks!

 

[joel]

Whre you have 45000 row the best way of removing items is to put an X i
an auxilary columns for the rows to delete. Then using auto filter ge
the visible rows from the autofilter and delete these rows. Remvoin
one row at a time take forever. This will run in a few seconds instea
of minutes.



Code
-------------------

Sub Removeduplicated()

With ActiveSheet
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'sort data on 4 columns
'do the last column
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
key1:=.Range("D1"), _
order1:=xlAscending
'do 3 other coluns
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
key1:=.Range("A1"), _
order1:=xlAscending, _
key2:=.Range("B1"), _
order2:=xlAscending, _
key3:=.Range("C1"), _
order3:=xlAscending

'Put in column IV an X for columns to delete
For RowCount = 2 To (LastRow - 1)
If .Range("A" & RowCount) = .Range("A" & RowCount + 1) And _
.Range("B" & RowCount) = .Range("B" & RowCount + 1) And _
.Range("C" & RowCount) = .Range("C" & RowCount + 1) And _
.Range("D" & RowCount) <> .Range("D" & RowCount + 1) Then

.Range("IV" & (RowCount + 1)) = "X"
End If

Next RowCount

'Make sure the is at least one X is column IV before applying autofilter
Set c = .Columns("IV").Find(what:="X", _
LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then
.Columns("IV").AutoFilter
.Columns("IV").AutoFilter Field:=1, Criteria1:="X"
.Rows("1:" & LastRow).SpecialCells (xlCellTypeVisible)
.Columns("IV").Delete
End If


End With

End Sub

 

[Barb Reinhardt]

WIthout providing the code, this is how I'd approach it.

1) Add a helper column with the following concatenated

Item #, TransQty, Doc Number.

You can do this like this

=A2&B2&C2

2) Add another helper column to count the values. Let's say your first
helper is column #, In that column put something like this

=COUNTIF (E:E,E2) Where the concatenated data is in column E and the
current row is 2.

HTH,

Barb Reinhardt

 

[keiji kounoike]

I think you didn't tell us the clear criteria that keep the records even
if Item No, Trans Qty, Doc Number AND Location are the same. I think it
is hard to answer your question without that criteria.

Keiji