PLEASE help with IF function...
 

I have a spreadsheet that i need to clean up. I have many duplicates that i
need to delete. Here is the problem; I don't want to delete ALL the
duplicates.
Here's an example:

A B C D
1 2/4/2005 F2 60A155-100 105588 Duplicate
2 2/1/2005 RE 60A155-100 105588 Duplicate
3 9/30/2004 F2 60A155-100 105445 Duplicate
4 9/30/2004 RE 60A155-100 105445 Duplicate
5 9/30/2004 RE 60A155-100 105445 Duplicate


If B1="F2" AND it is more recent (A) then the subsequent "RE" in B2, i want
to leave it and delete the "RE" line (Row 2).

But if :
A B C D
1 2/4/2005 F2 60A155-100 105588 Duplicate
2 2/1/2005 RE 60A155-100 105588 Duplicate
4 9/30/2004 RE 60A155-100 105445 Duplicate
5 9/30/2004 F2 60A155-100 105445 Duplicate


If B4="RE" AND is the more recent one, i want to delete both rows 4 and 5
both.

any ideas? i can go through each one but my report is huge!

TIA

James

Not very sure, but no harm trying this on a *spare* copy ..

Assuming source data below is in cols A to D, from row1 down:
A B C D
1 2/4/2005 F2 60A155-100 105588 Duplicate
2 2/1/2005 RE 60A155-100 105588 Duplicate
3 9/30/2004 F2 60A155-100 105445 Duplicate
4 9/30/2004 RE 60A155-100 105445 Duplicate
5 9/30/2004 RE 60A155-100 105445 Duplicate

Put in E1:
=IF(AND(B1="F2",A1=A2),"",IF(AND(B1="RE",A1=A2), "X",""))
Copy down

Col E will tag an "X" for the rows to be deleted

For the sample data above, E2, E4 and E5 will return "X"'s
Confirm this is correct before you proceed further

Assuming the "X''s are correctly returned:

Kill the formulas in col E with
an in-place copy paste special values ok

Insert a new row1, and put a label into E1

Select E1 and click Data Filter Autofilter
Filter out: X (select from droplist)

Select all the filtered rows (i.e. select the blue-colored row headers)
Right-click on selection Delete rows

Remove the autofilter
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"James" wrote in message
...
I have a spreadsheet that i need to clean up. I have many duplicates that
i
need to delete. Here is the problem; I don't want to delete ALL the
duplicates.
Here's an example:

A B C D
1 2/4/2005 F2 60A155-100 105588 Duplicate
2 2/1/2005 RE 60A155-100 105588 Duplicate
3 9/30/2004 F2 60A155-100 105445 Duplicate
4 9/30/2004 RE 60A155-100 105445 Duplicate
5 9/30/2004 RE 60A155-100 105445 Duplicate


If B1="F2" AND it is more recent (A) then the subsequent "RE" in B2, i
want
to leave it and delete the "RE" line (Row 2).

But if :
A B C D
1 2/4/2005 F2 60A155-100 105588 Duplicate
2 2/1/2005 RE 60A155-100 105588 Duplicate
4 9/30/2004 RE 60A155-100 105445 Duplicate
5 9/30/2004 F2 60A155-100 105445 Duplicate


If B4="RE" AND is the more recent one, i want to delete both rows 4 and 5
both.

any ideas? i can go through each one but my report is huge!

TIA

James

Max! you da'man!

thanks, i was creeping up on the very same idea, but you were one step ahead
of me!
"Max" wrote in message
...
Not very sure, but no harm trying this on a *spare* copy ..

Assuming source data below is in cols A to D, from row1 down:
A B C D
1 2/4/2005 F2 60A155-100 105588 Duplicate
2 2/1/2005 RE 60A155-100 105588 Duplicate
3 9/30/2004 F2 60A155-100 105445 Duplicate
4 9/30/2004 RE 60A155-100 105445 Duplicate
5 9/30/2004 RE 60A155-100 105445 Duplicate

Put in E1:
=IF(AND(B1="F2",A1=A2),"",IF(AND(B1="RE",A1=A2), "X",""))
Copy down

Col E will tag an "X" for the rows to be deleted

For the sample data above, E2, E4 and E5 will return "X"'s
Confirm this is correct before you proceed further

Assuming the "X''s are correctly returned:

Kill the formulas in col E with
an in-place copy paste special values ok

Insert a new row1, and put a label into E1

Select E1 and click Data Filter Autofilter
Filter out: X (select from droplist)

Select all the filtered rows (i.e. select the blue-colored row headers)
Right-click on selection Delete rows

Remove the autofilter
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"James" wrote in message
...
I have a spreadsheet that i need to clean up. I have many duplicates
that
i
need to delete. Here is the problem; I don't want to delete ALL the
duplicates.
Here's an example:

A B C D
1 2/4/2005 F2 60A155-100 105588 Duplicate
2 2/1/2005 RE 60A155-100 105588 Duplicate
3 9/30/2004 F2 60A155-100 105445 Duplicate
4 9/30/2004 RE 60A155-100 105445 Duplicate
5 9/30/2004 RE 60A155-100 105445 Duplicate


If B1="F2" AND it is more recent (A) then the subsequent "RE" in B2, i
want
to leave it and delete the "RE" line (Row 2).

But if :
A B C D
1 2/4/2005 F2 60A155-100 105588 Duplicate
2 2/1/2005 RE 60A155-100 105588 Duplicate
4 9/30/2004 RE 60A155-100 105445 Duplicate
5 9/30/2004 F2 60A155-100 105445 Duplicate


If B4="RE" AND is the more recent one, i want to delete both rows 4 and
5
both.

any ideas? i can go through each one but my report is huge!

TIA

James

Glad it worked for you !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"James" wrote in message
...
Max! you da'man!

thanks, i was creeping up on the very same idea, but you were one step
ahead
of me!