Excel 2010 formula to look up duplicates so I can contra them off

[PierremontQuaker03]

Hi, first of all thank you for looking at this post.

I have started a new job and am wanting to improve the speedof the month end process.
I have to download the information from Excel from thenominal ledger to review (approx. 3000 lines) some of the values are the same butare pluses and negatives – but they should have the same reference text in anothercell on that line, as when I post the journal to move stuff out I use the samereference.
So I am looking at a formula that looks for duplicates by doing two checks onone line:

• An absolute value in one cell and
  
• Match text in another cell

If a line meets those conditions I know it can be a contraand I can filter on the contra’s to remove them.
At the moment this is done manually.
I am not sure whether a Vlookup, Match or Duplicate formulacan be done to do this?
Any ideas? I am using Excel 2010 If anything is unclear let me know.

Actually while writing this post I have realised that the valueit looks up does not have to be the absolute value as I can create another columnwith the absolute values in and so I could potentially do a Concatenateformulae combining the two cells of the number and text I am looking up and then check for a duplicateof this concatenate row?? Does this sound feasible?

 

[jasonb75]

Assuming references in column A and amounts in column B, try this formula

=COUNTIFS(A:A,A2,B:B,-B2)

This should give you a count of 1 for each record that has a positive amount and a corresponding negative amount. Rows that don't match up will show a count of 0.

 

[PierremontQuaker03]

Assuming references in column A and amounts in column B, try this formula

=COUNTIFS(A:A,A2,B:B,-B2)

This should give you a count of 1 for each record that has a positive amount and a corresponding negative amount. Rows that don't match up will show a count of 0.

That's great thank you, that gives me something to work with and will save me a lot of time - much appreciated!