Match data from 2 financial statements by amount and date

[dreamed2fly]

how to match data from 2 financial statements by amount and date ?


So i have a transaction at Date 1/1/18 with the amount of $4000 and in the other statement i have the same transaction amount and date, how to use a formula to run through the data and pick out not matching ones.

 

[Aladin Akyurek]

Re: how to match data from 2 financial statements by amount and date

What are the relevant ranges?

 

[dreamed2fly]

Re: how to match data from 2 financial statements by amount and date

What are the relevant ranges?

So theres a column of dates which are aligned to the transaciton amount


and on the other statement there is a column of dates that are aligned to the transaction amount,


i would like to use a formula that can pick out the non matchings ones.

 

[Aladin Akyurek]

Re: how to match data from 2 financial statements by amount and date

A2:A10 for example is something which is called a range in Excel.

 

[dreamed2fly]

Re: how to match data from 2 financial statements by amount and date

A2:A10 for example is something which is called a range in Excel.

The range is A2:H137 for statement 1

and for statement 2 the same but in another worksheet

 

[Aladin Akyurek]

Re: how to match data from 2 financial statements by amount and date

The range is A2:H137 for statement 1

and for statement 2 the same but in another worksheet

A2:H137 is all dates? Where are the dates exactly, where are the amounts exactly, and what is the name of the another worksheet?

 

[dreamed2fly]

Re: how to match data from 2 financial statements by amount and date

A2:H137 is all dates? Where are the dates exactly, where are the amounts exactly, and what is the name of the another worksheet?

Hi sorry the dates for statement 1 range A9:A156

amount range = E9:E156


Statement 2 dates = J9:J156
statement 2 amounts = n9:n156

 

[Aladin Akyurek]

Re: how to match data from 2 financial statements by amount and date

Hi sorry the dates for statement 1 range A9:A156

amount range = E9:E156


Statement 2 dates = J9:J156
statement 2 amounts = n9:n156

In F9 enter and copy down:

=IF(ISNA(MATCH($A9,$J$9:$J$156,0)),"does not exist in statement 2",IF(VLOOKUP($A9,$J$9:$N$156,5,0)=$E9,"","amounts differ"))

In O9 enter and copy down:

=IF(ISNA(MATCH($J9,$A$9:$A$156,0)),"does not exist in statement 1",IF(VLOOKUP($J9,$A$9:$E$156,5,0)=$N9,"","amounts differ"))

Does this give you what you had in mind?

 

[Spotycus]

Re: how to match data from 2 financial statements by amount and date

Hi! A couple years ago I created a bank reconciliation tool to help me close out our clients files each month. It is an excel based widget that requires Macros to be enabled. You would paste the statement information into one of the tabs and the other statement info into the other. Click the button to run the reconciliation and all that will be left will be the unmatched items on each side.
The reconciliation was accomplished by concatenating the transaction type, the amount, and a sequential number (in case you had multiple payments of the same amount on the same day). Then it basically does a filter and find to match up the like items.

I just added it to my company website to provide you a link to download if you wish.

https://parkway.business/wp-content/uploads/2018/03/Bank-Reconciliation-4.0-1000-lines.xlsm

Matthew Fulton
Parkway Business Solutions
https://parkway.business