| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove duplicate rows based on 1 specific criterion
Question:
A B Apple 1/1/2004 Apple 2/1/2005 (to be deleted) Orange 1/1/2004 Orange 2/1/2005(to be deleted) Orange 3/1/2006(to be deleted) I want to delete the duplicate rows based on column A. The rows to keep will be the one with earliest date in column B. Appreciate any help. Thank you. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Remove duplicate rows based on 1 specific criterion
datafilteradvanced filter, you need headers for both columns, then use a
formula as criteria Assuming that the dates are in order first to last, assume the first fruit is in A4 and the criteria range is F1:F2, leave F1 blank and put =COUNTIF($A$4:A4,A4)=1 in F2 then apply the filter -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email) "Sweepea" wrote in message ... Question: A B Apple 1/1/2004 Apple 2/1/2005 (to be deleted) Orange 1/1/2004 Orange 2/1/2005(to be deleted) Orange 3/1/2006(to be deleted) I want to delete the duplicate rows based on column A. The rows to keep will be the one with earliest date in column B. Appreciate any help. Thank you. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Remove duplicate rows based on 1 specific criterion
Hi Peo,
I am sorry I can't quite get it. Please advise further. I set it up this way and apply the filter but nothing happened. Fruit Date apple 1/1/04 TRUE (=COUNTIF($A$4:A4,A4)=1) orange 1/1/04 apple 2/1/05 orange 2/1/05 orange 3/1/06 To reiterate, I want my final list to have apple 1/1/04 orange 1/1/04 Thank you again. Swee. "Peo Sjoblom" wrote: datafilteradvanced filter, you need headers for both columns, then use a formula as criteria Assuming that the dates are in order first to last, assume the first fruit is in A4 and the criteria range is F1:F2, leave F1 blank and put =COUNTIF($A$4:A4,A4)=1 in F2 then apply the filter -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email) "Sweepea" wrote in message ... Question: A B Apple 1/1/2004 Apple 2/1/2005 (to be deleted) Orange 1/1/2004 Orange 2/1/2005(to be deleted) Orange 3/1/2006(to be deleted) I want to delete the duplicate rows based on column A. The rows to keep will be the one with earliest date in column B. Appreciate any help. Thank you. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Remove duplicate rows based on 1 specific criterion
I couldn't eliminate one of the lines for apple. This is the result set I get:
Fruit Date apple 1/1/04 TRUE orange 1/1/04 apple 2/1/05 "Sweepea" wrote: Hi Peo, I am sorry I can't quite get it. Please advise further. I set it up this way and apply the filter but nothing happened. Fruit Date apple 1/1/04 TRUE (=COUNTIF($A$4:A4,A4)=1) orange 1/1/04 apple 2/1/05 orange 2/1/05 orange 3/1/06 To reiterate, I want my final list to have apple 1/1/04 orange 1/1/04 Thank you again. Swee. "Peo Sjoblom" wrote: datafilteradvanced filter, you need headers for both columns, then use a formula as criteria Assuming that the dates are in order first to last, assume the first fruit is in A4 and the criteria range is F1:F2, leave F1 blank and put =COUNTIF($A$4:A4,A4)=1 in F2 then apply the filter -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email) "Sweepea" wrote in message ... Question: A B Apple 1/1/2004 Apple 2/1/2005 (to be deleted) Orange 1/1/2004 Orange 2/1/2005(to be deleted) Orange 3/1/2006(to be deleted) I want to delete the duplicate rows based on column A. The rows to keep will be the one with earliest date in column B. Appreciate any help. Thank you. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Remove duplicate rows based on 1 specific criterion
How about just selecting column A and using data|filter|Advanced filter and
checking the box for unique records only. Then copy the visible data to a new sheet. This would depend on column B being in nice order so the top most "Apple" is kept. I'd sort the data by column A (ascending), then column B (ascending). Debra Dalgleish explains it in more detail: http://www.contextures.com/xladvfilter01.html#FilterUR Sweepea wrote: Question: A B Apple 1/1/2004 Apple 2/1/2005 (to be deleted) Orange 1/1/2004 Orange 2/1/2005(to be deleted) Orange 3/1/2006(to be deleted) I want to delete the duplicate rows based on column A. The rows to keep will be the one with earliest date in column B. Appreciate any help. Thank you. -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Remove duplicate rows based on 1 specific criterion
Thank you so much, Dave! This works!!
"Dave Peterson" wrote: How about just selecting column A and using data|filter|Advanced filter and checking the box for unique records only. Then copy the visible data to a new sheet. This would depend on column B being in nice order so the top most "Apple" is kept. I'd sort the data by column A (ascending), then column B (ascending). Debra Dalgleish explains it in more detail: http://www.contextures.com/xladvfilter01.html#FilterUR Sweepea wrote: Question: A B Apple 1/1/2004 Apple 2/1/2005 (to be deleted) Orange 1/1/2004 Orange 2/1/2005(to be deleted) Orange 3/1/2006(to be deleted) I want to delete the duplicate rows based on column A. The rows to keep will be the one with earliest date in column B. Appreciate any help. Thank you. -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Combining Totals on Successive Rows Based on Two Criterion | Excel Discussion (Misc queries) | |||
| Delete rows that don't meet specific criterion | New Users to Excel | |||
| Hide/Delete entire rows based in the content of one cell | Excel Discussion (Misc queries) | |||
| Insert duplicate rows based on numeric value in column | Excel Discussion (Misc queries) | |||
| Showing Duplicate Rows | Excel Discussion (Misc queries) | |||
Linear Mode
