How to delete rows if column A1 and B1 are equal to A2 and B2

[GenericPat]

Hey guys,

How do you automatically delete all rows EXCEPT ONE when for example A1,B1,C1,D1 are equal to A2,B2,C2,D2 AND copy the remaining one to another sheet if column E is greater then 4 999 999.99$?

For example:
A B C D E
1| 13232 | 250 | 1800 | 1 | 5 025 000$
2| 13232 | 250 | 1800 | 1 | 5 025 000$

In this case I would want:

First: Delete one of the rows (because A,B,C,D in row 1 are ALL equal to A,B,C,D in row 2)
Second: Copy the remaining row to a second worksheet (Because E is *> 4 999 999.99$)

Is this possible?

Thanks in advance,

Pat.

[benishiryo]

hi Pat. you could put a formula in column F as such, assuming your data starts from A2:
=AND(SUMPRODUCT(($A$2:A2=A2)*($B$2:B2=B2)*($C$2:C2=C2)*($D$2:D2=D2))>1,E2>=5000000)

drag the formula down & later do an Autofilter. anything that is FALSE should be deleted. if you need it automated, consider recording these actions into macro

[GenericPat]

I dont think it works...

13392 | 11935 |18000 |1 |0 |15000000 |113208915
13392 | 11935 |18000 |1 |0 |15000000 |113208915
13392 | 11935 |18000 |1 |0 |15000000 |113208915
13392 | 11935 |18000 |2 |6078706,04 | 7840000 |113208915
13392 | 11935 |18000 |2 |6078706,04 | 7840000 |113208915
13392 | 11935 |18000 |2 |6078706,04 | 7840000 |113208915
13392 | 11935 |18000 |3 |6464756,01 | 8340000 |113208915
13392 | 11935 |18000 |3 |6464756,01 | 8340000 |113208915
13392 | 11935 |18000 |3 |6464756,01 | 8340000 |113208915
13392 | 11935 |18000 |4 |4045367,74 | 5200000 |113208915
13392 | 11935 |18000 |4 |4045367,74 | 5200000 |113208915
13392 | 11935 |18000 |4 |4045367,74 | 5200000 |113208915
13392 | 11935 |18000 |5 |1064797,81 | 1356305 |113208915
13392 | 11935 |18000 |5 |1064797,81 | 1356305 |113208915
13392 | 11935 |18000 |5 |1064797,81 | 1356305 |113208915

Heres an example of data... Lines 2,3,5,6,8,9,11,12,14,15 should be deleted because A,B,C,D are equal to eachothers (Line 1 A,B,C,D are the same as line 2 and 3 therefore delete 2 and 3 and keep Line 1)

Any ideas?

Thanks,

Pat.

[benishiryo]

/have you shifted your amount to column F now? try:
=AND(SUMPRODUCT(($A$1:A1=A1)*($B$1:B1=B1)*($C$1:C1=C1)*($D$1:D1=D1))=1,F1>=5000000)

and line 13 should be deleted too since it's less than $5 million right?

ps: it would be better if you could upload sample Excel files next time. and a desired results shown would be fantastic. to upload a file, press "Go to Advanced" beside the "Post Quick Reply" button & click on the paperclip icon.