Deleting Correlating Positive and Negative Numbers based on Criteria

**amaan.khowaja** · 07-02-2013, 05:09 AM

Dear All,

I have a large spreadsheet that i am trying to cleanup. it contains thousands of rows where it shows a positive transaction with a matching negative transaction.

what i want is to delete that matching transaction based on following condition;

if matching postive and negative transaction alongwith amount is reconcile with ACCOUNT NO then both entries should be deleted.

this is previously solved with VBA but i am unable to modify according to my need.

i have attached the sheet for ease of reference.

thanx and cheers.

**XOR LX** · 07-02-2013, 05:29 AM

Hi,

In your attachment, you indicate that the following pair should be deleted:

900138163 PROFESSIONAL FEES DR. SHAZIA SHAFIQUE -1.00 -770.00
900136073 PROFESSIONAL FEES DR. SHAZIA SHAFIQUE -1.00 -770.00

though this does not match your criterion that the Account Numbers should match.

Please clarify.

Regards

**amaan.khowaja** · 07-02-2013, 05:57 AM

yes thats true and you are right.......... i am sorry the pair matching is row no 7 & 8

**XOR LX** · 07-02-2013, 06:17 AM

Hi,

But now there are three matching that Account No. I assume that one of the positive lines remains and one is deleted, but does it matter which one?

900138163 PROFESSIONAL FEES DR. SHAZIA SHAFIQUE -1.00 -770.00
900138163 PROFESSIONAL FEES DR. SHAZIA SHAFIQUE -1.00 -770.00
900138163 PROFESSIONAL FEES DR. SHAZIA SHAFIQUE -1.00 -770.00

Regards

**amaan.khowaja** · 07-02-2013, 06:24 AM

yes your assumption is correct, it does not matter.

**XOR LX** · 07-02-2013, 06:31 AM

Hi,

One more question: is the corresponding negative transaction always on the row immediately above or below that of the positive transaction?

Regards

**amaan.khowaja** · 07-02-2013, 06:47 AM

no it can be anywhere in the rows of around 1000.

**XOR LX** · 07-02-2013, 07:41 AM

Hi,

Place this formula in cell E2 of you sheet and copy down as required:

=IF((SUMPRODUCT(($A$2:$A$1000=A2)*($C$2:$C$1000=-C2)*($D$2:$D$1000=-D2))>0)*(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2)*($D$2:$D2=D2))=1),"DELETE","")

Amend the range references to suit your needs. Then filter on this column for DELETE and delete those rows.

Suggest you do some spot tests on this formula before going ahead and performing the actual delete.

Regards

**amaan.khowaja** · 07-02-2013, 08:57 AM

great brother......it works.

thanx alot.

cheers.

**XOR LX** · 07-02-2013, 01:34 PM

Glad I could help. Best of luck with it.

**amaan.khowaja** · 09-02-2013, 04:35 AM

Dear XOR LX,
i need your help once again the formula you suggested was working great but unfortunately this month i have a change in condition of which result is misleading, so this this ok but can you please help to add another condition of PHYSICAIN ID coloum E.(File Attached)

thanx in advance.

and sorry guys i have update the solved thread i dont know weather it is correct or not.

Regards

**XOR LX** · 09-02-2013, 04:41 AM

Hi,

You have not clarified what this extra condition on Column E is.

Regards

**amaan.khowaja** · 09-02-2013, 04:49 AM

actually this is another criteria which it needs to be checked. just like account no. coloum A

**XOR LX** · 09-02-2013, 04:55 AM

You mean that the formula should only apply to cases where there is "SHME" in column E?

Regards

**amaan.khowaja** · 09-02-2013, 04:58 AM

yes where there is "SHME" ALSO

**XOR LX** · 09-02-2013, 05:02 AM

Sorry - what do you mean by "ALSO"? I asked you whether it was to apply ONLY to cases where there is "SHME" in column E?

Please clarify.

Regards

**amaan.khowaja** · 09-02-2013, 05:13 AM

Dear Xor,
YES i have written also because ACCOUNT NO (coloum A) alongwith PHYSICIAN ID (coloum E) these both should also match.

**XOR LX** · 09-02-2013, 05:18 AM

Sorry, but it's still not clear whether you mean that records should only be considered for deletion if PHYSICIAN ID is "SHME" (you don't have any other type in your attached) OR whether the formula should be looking at cases in which the PHYSICIAN ID is the same (as currently applies to column A).

Regards

**amaan.khowaja** · 09-02-2013, 05:19 AM

Dear Xor,
I think i have to share a real example to help you understand;

Untitled.jpg

**amaan.khowaja** · 09-02-2013, 05:24 AM

as you will notice from the attachment that in coloum completeness the DELETE option is showing on the records where PHYSICIAN ID is not same. so finally the records of (PHYSICIAN ID) of AJMK should be deleted rather one record of AJMK & one of HLOH.

**XOR LX** · 09-02-2013, 05:26 AM

I'm afraid that doesn't help either: the two marked as "DELETE" in the COMPLETENESS column have different entries in the PHYSICIAN ID column, which would seem to contradict what you stated earlier (that the PHYSICIAN ID entries for those to be deleted should be the same).

Edit: just seen your last post. Now I understand. One moment, please.

Regards

**amaan.khowaja** · 09-02-2013, 05:30 AM

Dear XOR,
Actually this is derived from the formula you suggested earlier, this is the example of record which i said was misleading this is just an example so to correct this error i request for your help.

**XOR LX** · 09-02-2013, 05:36 AM

Hi,

Change your formula in row 2 to:

=IF((SUMPRODUCT(($A$2:$A$1000=A2)*($C$2:$C$1000=-C2)*($D$2:$D$1000=-D2)*($E$2:$E$1000=E2))>0)*(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2)*($D$2:$D2=D2)*($E$2:$E2=E2))=1),"DELETE","")

and copy down as required. Please let me know if that solves the issue.

Regards

**amaan.khowaja** · 09-02-2013, 06:34 AM

Dear XOR,
Finally we reach to a conclusion and i am really thankfull to you for all your support i really took your time and thanx alooooooot for your time and help..

appreciated your patient and responses.

thanx alooooooooot brother.

**XOR LX** · 09-02-2013, 06:49 AM

You're welcome.

Deleting Correlating Positive and Negative Numbers based on Criteria

LinkBack

Thread Tools

Rate This Thread

Display

Deleting Correlating Positive and Negative Numbers based on Criteria

Re: Deleting Correlating Positive and Negative Numbers based on Criteria

Re: Deleting Correlating Positive and Negative Numbers based on Criteria

Re: Deleting Correlating Positive and Negative Numbers based on Criteria

Re: Deleting Correlating Positive and Negative Numbers based on Criteria

Re: Deleting Correlating Positive and Negative Numbers based on Criteria

Re: Deleting Correlating Positive and Negative Numbers based on Criteria

Re: Deleting Correlating Positive and Negative Numbers based on Criteria

Re: Deleting Correlating Positive and Negative Numbers based on Criteria

Re: Deleting Correlating Positive and Negative Numbers based on Criteria

Re: Deleting Correlating Positive and Negative Numbers based on Criteria

Re: Deleting Correlating Positive and Negative Numbers based on Criteria

Re: Deleting Correlating Positive and Negative Numbers based on Criteria

Re: Deleting Correlating Positive and Negative Numbers based on Criteria

Re: Deleting Correlating Positive and Negative Numbers based on Criteria

Re: Deleting Correlating Positive and Negative Numbers based on Criteria

Re: Deleting Correlating Positive and Negative Numbers based on Criteria

Re: Deleting Correlating Positive and Negative Numbers based on Criteria

Re: Deleting Correlating Positive and Negative Numbers based on Criteria

Re: Deleting Correlating Positive and Negative Numbers based on Criteria

Re: Deleting Correlating Positive and Negative Numbers based on Criteria

Re: Deleting Correlating Positive and Negative Numbers based on Criteria

Re: Deleting Correlating Positive and Negative Numbers based on Criteria

Re: Deleting Correlating Positive and Negative Numbers based on Criteria

Re: Deleting Correlating Positive and Negative Numbers based on Criteria

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions