0
Hopefully you good people can help me as I'm pulling my hair out doing this manually and can see no way to automate
At present I have two sets of data consisting of three columns each - Date, Amount £, Description. One set of data is the actual expenses and the other is a bank statement and both have been sorted on the 'Amount £' column ascending.
I want to produce a new spreadsheet combining the two sources with all six columns and where there is a match have both sets of input and where there is a difference have either the expenses line only or the bank statement line only. As you can see from my example below dates may mismatch by a few days due to Paypal. No data should be lost when merging
Expenses Bank
10-Aug-14 Ebay £0.95 12/08/2014 0.95 Paypal
14-Sep-14 Ebay £0.99 -
28-Sep-14 Ebay £0.99 -
30-Sep-14 Ebay £1.20 -
15-Sep-14 Ebay £1.29 22/09/2014 1.29 Paypal
18-Sep-14 Ebay £1.29 -
25-Sep-14 Ebay £1.45 -
Sorry for above layout, but hopefully it gives an idea of the data and the output 6 columns
Any idea on how I can automate this merging process.
Many thanks for your help
1Welcome to SuperUser! What have you tried already? – Wutnaut – 2014-10-29T16:10:28.183
It sounds like a "fuzzy logic" problem (match Paypal date even though it might be off by a few days, and one says Ebay while the other says Paypal). Excel doesn't have a fuzzy match function. You need to operationally define the rules. Also, how do you know that the 22/09/2014 Paypal goes with the 15-Sep-14 Ebay and not the 18-Sep-14 Ebay? – fixer1234 – 2014-10-29T16:48:16.713
Unfortunately all manual at the moment. From the date mismatch side - I completely agree with you on the mismatching and what I have done manually is to completely ignore the dates and simply match the amounts £. Then any mismatches will fall out and I can drill down on the dates if required. Maybe this would remove the fuzzy logic and make it more black and white. If there are five items of 99p in both the expenses and the bank then block booking them as matches works well. Any mismatches require a further manual action so will require investigating. Many thanks for your help – Fred – 2014-10-29T16:59:56.097