Finding a matching credit line to a debit line in Excel

Resolved · Low Priority · Version 2016

Mon 16 Jul 2018, 17:31 Delegate Wendy said...

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

Mon 16 Jul 2018, 19:35 Trainer Jens said...

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

Tue 17 Jul 2018, 10:03 Delegate Wendy said...

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.

Tue 17 Jul 2018, 18:59 Trainer Jens said...

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

Wed 18 Jul 2018, 09:52 Delegate Wendy said...

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

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Entering text in Multiple Worksheets

If you have a number of worksheets in a workbook that require the same information (data or tables) on each worksheet, this can be done as follows:

Hold down the Ctrl key and click one or more of the additional worksheet tabs (i.e. Sheet2, Sheet3, etc).

In your mainsheet (Sheet1) enter the required data or design a table.

When done 'Click' on the other sheet tabs and you will see that the information entered in Sheet1 is on all the other selected worksheets.

NB Do not forget to deselect the worksheets - otherwise you may add data to the main worksheet and all the selected worksheets will also have that data!

View all Excel hints and tips