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?

 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
8,076
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,090,071
Messages
5,412,171
Members
403,421
Latest member
tammyanthos

This Week's Hot Topics

Top