How can I remove multiple data by multiple conditions?

**FlorinS** · 08-21-2009, 12:43 PM

Hello guys!

This is my first post, so I am glad to meet you all!

Here is what I want to do:

1. I have 4 columns (let's say D,E,I,J);

D E I J
Account1 Sum1 Account2 Sum2
151 93,79 BF2479 30,00
1476757 93,79 BF7 22,00
BF2479 30,00 151 93,79
86041 84,03 23235 30,00
534 6608,45

2. I need to clean the multiple identical data in the list by both Account no. and Sum (of course the list is much longer!) so that the list should look like this:

D E I J
Account1 Sum1 Account2 Sum2

1476757 93,79 BF7 22,00

86041 84,03 23235 30,00
534 6608,45

I must say that I am a beginner in Excel, so I tried to use this:

=IF(OR(E5=$J$5:$J$430);IF(INDEX($I$5:$I$430;MATCH(E5;$J$5:$J$430;FALSE);1)=D5;"";E5);E5)

for column E, and

=IF(OR(J5=$E$5:$E$452);IF(INDEX($D$5:$D$452;MATCH(J5;$E$5:$E$452;FALSE);1)=I5;"";J5);J5)

(which is reverse of the first one) for column J; (The ranges are the ones used in the real sheet), not for the ones in the example above!);
The formula does seem to eliminate a dew unwanted data, but does not give me all the wanted results!

Please, give me a little eadvice if you can!

Thank you in advance!

**JBeaucaire** · 08-21-2009, 04:21 PM

You can't use worksheet formulas to delete cells, but you can use them to flag things for you to delete.

For instance, based on your example data, you could put this formula in F2 and copy down to get a list of items to delete in the first set:
=IF(ISNUMBER(MATCH(D2&E2, INDEX($I$2:$I$500&$J$2:$J$500, 0), 0)), "Delete", "")

And you could put this in K2 and copy down to get a list of deletions for the second set:
=IF(ISNUMBER(MATCH(I2&J2, INDEX($D$2:$D$500&$E$2:$E$500, 0), 0)), "Delete", "")

You would have to "copy/edit/paste special/values" over one of those columns of formulas before you started deleting values in the other set, but it would work well.

Sample sheet attached.

**FlorinS** · 08-23-2009, 10:01 AM

Hello,

Thank u very much for reply and sorry that i could not be here sooner;
I did not try to directly delete cells by using function (this is not possible, as you said above), but only to mark them in a third column as you did in your example -> so your formula works beautifully and solved all my problems!

Thank you again for all your work!

Greets,
Florin

**JBeaucaire** · 08-23-2009, 10:50 AM

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

**FlorinS** · 08-23-2009, 02:01 PM

Originally Posted by JBeaucaire

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

Done!

Have a nice day!

Greets,
Florin