Formula to delete duplicate rows in "merged" worksheet

[Colin Bembridge]

Hi, Here is the problem.
We just did inventory, and had a bunch of users modifying
seperate versions of a "master" workbook with all our part
numbers on it (10,000 rows). When they were finished,
because the person that initiated the process didn't start
off with a shared document, we cannot use the merge
function. So we did a cut/paste/sort to get all the data
into the sheet, but now we have duplicate entries, some
with 0 or empty qty columns, others with #'s in the qty
column. The problem is that some non-duplicated rows
SHOULD be 0 or empty qty. Is there a formula we could use
to delete rows that are both duplicates and with 0 or
empty qty?
Obviously this following idea for a formula is fantasy,
but the real one would have the same intent:
IF(cell A)SAME AS(cell B)AND(0qty)OR(empty)THENELETE ROW
(containing cell A)
Does this make ANY sense?
Would really appreciate some help, we are all Excel
beginners around here. We have Excel 2000 and 97 available.
Thanks.

 

[Max]

Here's one possible way?

Try this on a *back-up* copy of your file, not the original.

Assume the following sample data-set / format is in A1:C7

Name1 Name2 Qty
ddddd sssss
aaaaa bbbbb 0
fffff ggggg 78
aaaaa bbbbb
fffff ggggg 78
ddddd sssss 0

Put in
D2:=IF(OR(TRIM(C2)="",ISBLANK(C2)),TRIM(A2&"_"&B2&"_"&0),TRIM(A2&
"_"&B2&"_"&C2))

Copy down col D

Press Alt+F11 to go to vba

In vba, click Insert>Module, and copy>paste MVP Chip Pearson's
Sub DeleteDuplicateRows()* - reproduced below - into the general
module window (copy>paste everything within the dotted lines of
begin/end vba)

Press Alt+Q to go back to excel

* Found at:
http://www.cpearson.com/excel/deleting.htm#DeleteDupl

Select col D

Run the Sub DeleteDuplicateRows()

To run:
a. Press Alt+F8
b. Select DeleteDuplicateRows
c. Click Run

The sample data-set after the macro run completes will be:

Name1 Name2 Qty
ddddd sssss
aaaaa bbbbb 0
fffff ggggg 78

which (hopefully) gives what you're after...

------------- begin vba -----------------
Public Sub DeleteDuplicateRows()

' This macro deletes duplicate rows in the selection. Duplicates
are
' counted in the COLUMN of the active cell.
' DeleteDuplicateRows
' This macro will delete duplicate rows in a range.
' To use, select a single-column range of cells, comprising the
range
' of rows from which duplicates are to be deleted, e.g., C2:C99.

' To determine whether a row has duplicates,
' the values in the selected column are compared.
' Entire rows are not compared against one another.
' Only the selected column is used for comparison.
' When duplicate values are found in the active column, the first
row remains,
' and all subsequent rows are deleted.

Dim Col As Integer
Dim r As Long
Dim C As Range
Dim n As Long
Dim V As Variant
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Col = ActiveCell.Column

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If

n = 0
For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) >
1 Then
Rng.Rows(r).EntireRow.Delete
n = n + 1
End If
Next r

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub
------ end vba ------------------

 

[Max]

Here's the full link to Chip's Sub DeleteDuplicateRows()
- earlier one was inadvertently truncated, sorry

http://www.cpearson.com/excel/deleting.htm#DeleteDuplicateRows