deleting repeated rows

  • Thread starter Thread starter dummy
  • Start date Start date
D

dummy

I have a sheet with a list of items in column A and a quantity in Column B.
In the case where I have identical items in column A, I only want to see the
row with the lesser column B quantity and delete the others.
Example: Starting with
A B
BK152 500
CV136 600
BK152 400

I would like to end up with
A B
BK152 400
CV136 600


Thus throwing out the row with the original quantity of 500 for item BK152

Thanks for any help
 
try this

Sub keepsmallestvalue()
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 2 Step -1

Set mf = Cells(1, 1).Resize(lr - 1) _
.Find(What:=Cells(i, 1), after:=Cells(1, 1), _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not mf Is Nothing Then
If Cells(i, 2) < Cells(mf.Row, 2) _
Then Rows(mf.Row).Delete
End If

Next i
End Sub
 
This isn't hard, dummy. There are probably several ways to do it, but here's
how I'd go about it:

a) Sort your data by col A (either ascending or descending, doesn't matter)
and col B ascending (this one does matter).

b) Create a helper column, let's say N, and put in N2 the formula =A2=A1.
The assertion that A2 is equal to A1 is either True or False, and since I
presume you have a header column in A1 it will be False.

c) Copy N2 all the way down the column. Because the data is sorted by col
A, now every new item number will have False in col N, and all the duplicates
will be True. Furthermore because within item number it's sorted by
quantity, all the rows marked False are the lowest quantities in their item.

d) Change the helping column N from a formula to a value. That is:
d1) Highlight column N and Copy it.
d2) Go to Edit, Paste Special
d3) Select Values from the Paste-Special menu, and hit OK.

e) Now sort by col N. All the rows with True in row N are now grouped
together, and you can delete them. The remainder are the rows you want,
right?

f) Delete row N, just to clean up afterward.
 
Thanks for the help Bob. That works, but since this sheet is constantly being
added to, can I have this done through a macro?
 
Here's a formulas drive to monitor/deliver results auto as source data
changes ..
Assume source data as posted runs in A2:B2 down,
items in col A, amts in col B
In D2:
=IF(A2="","",IF(COUNTIF(A$2:A2,A2)>1,"",ROW()))
Copy down to cover the max expected extent of source data, eg down to D1000?
Then in E2:
=IF(ROWS($1:1)>COUNT(D:D),"",INDEX(A:A,SMALL(D:D,ROWS($1:1))))
In F2, confirm this via pressing CTRL+SHIFT+ENTER (CSE):
=IF(E2="","",MIN(IF((A$2:A$1000=E2)*(B$2:B$1000<>""),B$2:B$1000)))
Visually check that Excel has wrapped the curlies: { } in F2's formula bar,
otherwise, re-do the CSE. Copy E2:F2 down by the smallest range large enough
to cover the max expected number of unique items in col A, eg down to F50?
Hide/minimize col D. Col E returns the list of unique items, col F returns
the corresponding minimum amounts, dynamically. voila? celebrato, hit YES
below
 
Hi,

You may try the following:

1. Select the range (including the header row - say the headers are Code and
Amount) and assign it a name, say dummy
2. Select the range (including the header row - say the headers are Code and
Amount) and convert it to a Table
3. Save the file and click on any blank cell
4. Navigate to:
a. Excel 2007 - Data > Get External Data > From Other Sources > From
Microsoft Query > Excel files > OK
b. Excel 2003 - Data > Import Data > New Database Query > Excel files >
OK
5. Navigate the file saved in 3 above and click on Next
6. Select dummy and click on the greater then symbol. Click on Next
7. Click on Next 2 times and on the third screen, select the second radio
button
8. Click on SQL, delete all the contents and type the following

SELECT dummy.Code, Min(dummy.Amount) AS 'Min of Amount'
FROM `C:\Users\Ashish\Desktop\trying.xlsx`.dummy
dummy................................................(this path will be the
path where your file is saved)
GROUP BY dummy.Code

9. Go to File > Return Data to MS Office Excel
10. Select Table and the cell where you want the output and click on Finish
11. Now if you edit any entry or new data by rows, just right click on any
output cell and click on Refresh

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Back
Top