Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
deleting repeated rows
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
deleting repeated rows
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "dummy" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
deleting repeated rows
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. --- "dummy" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
deleting repeated rows
Thanks for the help Bob. That works, but since this sheet is constantly being
added to, can I have this done through a macro? "Bob Bridges" wrote: 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. --- "dummy" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
deleting repeated rows
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
deleting repeated rows
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
deleting repeated rows
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,R OWS($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 -- Max Singapore --- "dummy" wrote: 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
deleting repeated rows
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 "dummy" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
repeated rows to single rows with many columns | Excel Discussion (Misc queries) | |||
Repeated rows when printing | Excel Discussion (Misc queries) | |||
deleting repeated text from a drop down box | Excel Discussion (Misc queries) | |||
Deleting Repeated Rows | Excel Worksheet Functions | |||
Deleting cells with repeated data | Excel Worksheet Functions |