I have a worksheet that has about 18,000 rows of data where there are
duplicates. For instance the required delivery date changed from February 2
to February 9 but all of the information regarding the sales order remained
the same. I would like to delete the record with the oldest date. Is there
a function I can use to compare the date and highlight the record that should
be deleted?

Assuming you have your order numbers in Col A and dates in Col B
Sort your data in Ascending order of Col B (oldest date first)
Insert Col C
Enter this in C1
=Countif(A1:$A$1800,A1)
change 1800 to your last row number
Copy this down to the end of your data

Now any cells in Col B with a number greater than 1 will indicate a order
number which has a later date row, somewhere down... You can filter on Col C
choosing Greater than 1 as the condition and delete those rows. Once done you
can delete col C also.


"Lizz45ie" wrote:

I have a worksheet that has about 18,000 rows of data where there are
duplicates. For instance the required delivery date changed from February 2
to February 9 but all of the information regarding the sales order remained
the same. I would like to delete the record with the oldest date. Is there
a function I can use to compare the date and highlight the record that should
be deleted?

Here's an example of what my data looks like and what I'm trying to accomplish:

61110000 656.00 12/15/08
62220000 600.00 12/1/08
61110000 656.00 11/15/08
61110000 656.00 10/01/08
61140000 1000.00 7/02/08
61140000 1000.00 12/16/08

I would like to delete the rows where the date is oldest.

"Sheeloo" wrote:

Assuming you have your order numbers in Col A and dates in Col B
Sort your data in Ascending order of Col B (oldest date first)
Insert Col C
Enter this in C1
=Countif(A1:$A$1800,A1)
change 1800 to your last row number
Copy this down to the end of your data

Now any cells in Col B with a number greater than 1 will indicate a order
number which has a later date row, somewhere down... You can filter on Col C
choosing Greater than 1 as the condition and delete those rows. Once done you
can delete col C also.


"Lizz45ie" wrote:

I have a worksheet that has about 18,000 rows of data where there are
duplicates. For instance the required delivery date changed from February 2
to February 9 but all of the information regarding the sales order remained
the same. I would like to delete the record with the oldest date. Is there
a function I can use to compare the date and highlight the record that should
be deleted?

You just need to enter the formula in D1
=Countif(A1:$A$1800,A1)

and follow the rest of my instructions

"Lizz45ie" wrote:

Here's an example of what my data looks like and what I'm trying to accomplish:

61110000 656.00 12/15/08
62220000 600.00 12/1/08
61110000 656.00 11/15/08
61110000 656.00 10/01/08
61140000 1000.00 7/02/08
61140000 1000.00 12/16/08

I would like to delete the rows where the date is oldest.

"Sheeloo" wrote:

Assuming you have your order numbers in Col A and dates in Col B
Sort your data in Ascending order of Col B (oldest date first)
Insert Col C
Enter this in C1
=Countif(A1:$A$1800,A1)
change 1800 to your last row number
Copy this down to the end of your data

Now any cells in Col B with a number greater than 1 will indicate a order
number which has a later date row, somewhere down... You can filter on Col C
choosing Greater than 1 as the condition and delete those rows. Once done you
can delete col C also.


"Lizz45ie" wrote:

I have a worksheet that has about 18,000 rows of data where there are
duplicates. For instance the required delivery date changed from February 2
to February 9 but all of the information regarding the sales order remained
the same. I would like to delete the record with the oldest date. Is there
a function I can use to compare the date and highlight the record that should
be deleted?

Hi,

Assume that your data is in range E9:G14. use the following array formula
(Ctrl+Shift+Enter) in cell H9 and copy down -
=IF(G9=MIN(IF(($E$9:$E$14=E9),$G$9:$G$14)),1,"a").

Now you will see 1 against all the records which are the least. Now
highlight H9:H14 and the Ctrl+GSpecialFormulasNumbers and OK. This will
highlight all the 1's. Now do Alt + E+D+R.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Lizz45ie" wrote in message
...
I have a worksheet that has about 18,000 rows of data where there are
duplicates. For instance the required delivery date changed from February
2
to February 9 but all of the information regarding the sales order
remained
the same. I would like to delete the record with the oldest date. Is
there
a function I can use to compare the date and highlight the record that
should
be deleted?

I tried the formula listed below and got all "a", I must be doing something
wrong.
Here's my adjusted formula:

=IF(E3=MIN(IF(($B$3:$B$17787=B3),$E$3:$E$17787)),1 ,"a")


"Ashish Mathur" wrote:

Hi,

Assume that your data is in range E9:G14. use the following array formula
(Ctrl+Shift+Enter) in cell H9 and copy down -
=IF(G9=MIN(IF(($E$9:$E$14=E9),$G$9:$G$14)),1,"a").

Now you will see 1 against all the records which are the least. Now
highlight H9:H14 and the Ctrl+GSpecialFormulasNumbers and OK. This will
highlight all the 1's. Now do Alt + E+D+R.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Lizz45ie" wrote in message
...
I have a worksheet that has about 18,000 rows of data where there are
duplicates. For instance the required delivery date changed from February
2
to February 9 but all of the information regarding the sales order
remained
the same. I would like to delete the record with the oldest date. Is
there
a function I can use to compare the date and highlight the record that
should
be deleted?

Hi,

Confirm the formula with Ctrl+Shift+Enter instead of the conventional Enter.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Lizz45ie" wrote in message
...
I tried the formula listed below and got all "a", I must be doing
something
wrong.
Here's my adjusted formula:

=IF(E3=MIN(IF(($B$3:$B$17787=B3),$E$3:$E$17787)),1 ,"a")


"Ashish Mathur" wrote:

Hi,

Assume that your data is in range E9:G14. use the following array formula
(Ctrl+Shift+Enter) in cell H9 and copy down -
=IF(G9=MIN(IF(($E$9:$E$14=E9),$G$9:$G$14)),1,"a").

Now you will see 1 against all the records which are the least. Now
highlight H9:H14 and the Ctrl+GSpecialFormulasNumbers and OK. This
will
highlight all the 1's. Now do Alt + E+D+R.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Lizz45ie" wrote in message
...
I have a worksheet that has about 18,000 rows of data where there are
duplicates. For instance the required delivery date changed from
February
2
to February 9 but all of the information regarding the sales order
remained
the same. I would like to delete the record with the oldest date. Is
there
a function I can use to compare the date and highlight the record that
should
be deleted?