Wendy has attended:
Excel Introduction course
Finding a matching credit line to a debit line in Excel
Hello
Is there a dynamic way to locate a matching credit line to a debit line in an excel document? I have no unique line identifiers in my report to help evaluate or pivot this.
For example there may be 20 lines in a report of 400,000 lines where the debit amount is shown as -50.00, but if the report contains 80 lines with a credit amount of 50.00, I can't work out which 50 of the 80 relate exactly to the debit amounts I'm trying to match. I have thousands of lines of varying amounts I need to isolate and evaluate.
Any ideas are much appreciated.
Thanks
Wendy
RE: Finding a matching credit line to a debit line in Excel
Hi Wendy,
Thank you for the forum question.
How would you manually find the matching debit/credit line?
You will not need one unique identifier, but there much be a pattern or a combination of information, which can do the match.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Finding a matching credit line to a debit line in Excel
The problem is that this is a case of trying to match Gross reversal amounts like -£50.00 to the corresponding correction shown as £50.00. Some lines in my report have unit amounts which can help the match but this does not appear across the whole data set I need to evaluate, so using this won't give me the full results I need to evaluate.
RE: Finding a matching credit line to a debit line in Excel
Hi Wendy,
I am sorry but if you cannot define a pattern or if there is no rules Excel can follow, then Excel will not be able to help doing this.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Finding a matching credit line to a debit line in Excel
Thanks Jens. I've had to resort to a different approach, a more time cunsuming one to get the analysis I need. Good to know that it's not a function in Excel that would have helped that I was unaware of.
Thanks again,
Wendy
