URGENT!!!! I neeSpecial sorting!!!

moonlight22

New Member
Joined
Aug 15, 2014
Messages
24
Hi,

I am in desperate need.
I have a file which looks similar to the data below:

ABCDE
201409303344XYZ141AAB7
201404303344XYZ141AAB7
201409303344XYZ2196AAB7
201404303344XYZ2196AAB7
201409303344XYZ21345T2FG
201404303344XYZ21345N8J5
201409303344FFM58461B7GT
201404303344FFM58461N8J5

<tbody>
</tbody>


I have the file sorted by column D and then E, but what I need to identify next is whats difficult. I need to validate if the same code (column E) and same account (column D) appear in both April and September (column A). If so I need that highlighted so i can delete the data.

Basically, I need to keep any rows where column B, C & D are the same but column E are different based on the date in column A.

I know how to use conditional formatting to delete duplicate rows, but that's not what i need to do because of my requirement of column A.

Please let me know if you have any ideas, the file is very large and i need to finish this before tomorrow a.m...eeek!!

cheers!!
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

General Ledger

Active Member
Joined
Dec 31, 2007
Messages
460
Maybe try this....
I am assuming your sample data starts on line 1.
Sort your records by column B, then C, then D, then E, then A, all in ascending order.
In F1 enter =A1=A2
In G1 enter =B1=B2
In H1 enter =C1=C2
In I1 enter =D1=D2
In J1 enter =E1=E2
Copy these formulas down the entire list of records.
These formulas compare the value of each column for the current record to the value of the same column for the next record. A TRUE means the two values are the same and FALSE means they are not.
You want to delete records with the correct combinations of TRUE and FALSE in columns F through H.

Best of luck,

G/L
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Please don't create duplicate threads for the same topic. I have closed your other threads.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,204
Messages
5,527,396
Members
409,759
Latest member
KCH

This Week's Hot Topics

Top