Date Comparison in the same column
 

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?

Date Comparison in the same column
 

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?

Date Comparison in the same column
 

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?

Date Comparison in the same column
 

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?

Date Comparison in the same column
 

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?

Date Comparison in the same column
 

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?

Date Comparison in the same column
 

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?