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

PierremontQuaker03

New Member
Joined
Nov 9, 2019
Messages
5
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

Well-known Member
Joined
Dec 30, 2008
Messages
6,910
Office Version
2019
Platform
Windows
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

New Member
Joined
Nov 9, 2019
Messages
5
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!
 

Forum statistics

Threads
1,078,312
Messages
5,339,422
Members
399,305
Latest member
msklut

Some videos you may like

This Week's Hot Topics

Top