Formula to mark offsetting transactions

**jmiller7** · 01-14-2011, 10:41 AM

I nee help developing a formula. I have an extremely large database of sales and I need to figure out how to mark the refunds to the matching sale.Here is an example of what my database looks like

Cust # qty price
12345 15 5
12345 20 10
12345 20 -10
12345 20 10
12345 15 10
23666 20 -10

I want to mark down the negative # and ONLY 1 corresponding positive #. In this case I wouldwant the formula to mark down row 2 & 3 excluding the headings. The customer number and the qty must match as well. Thanks

**NBVC** · 01-14-2011, 11:13 AM

Does this work?

=IF(AND(COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2)=1,COUNTIFS(A:A,A2,B:B,B2,C:C,-C2)),"x","")

assuming data is in A:C

**jmiller7** · 01-14-2011, 02:23 PM

This formula was working for the first 1500 entries and then all of the sudden it started marking all the entries even though it shouldn't have been. Can you help explain why and how to fix that. Thanks

**NBVC** · 01-14-2011, 02:40 PM

Can you attach sample workbook... only include the relevant columns and show the formula "not working".

**jmiller7** · 01-14-2011, 07:23 PM

This actually worked. It was just talking a long time becuase the database has over 600,000 rows.While loading the x's were there but now they are gone. Thank you so much