How to combine two columns with no data loss for further analysis

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

Fred

Posted 2014-10-29T16:05:00.573

Reputation: 1

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

Answers

0

Follow the steps as following:

  1. Collate the data (i.e. sum the amounts) for a data and description (merchant) in both sheets. You do not need to do this is there is only one row for a combination of date and description.
  2. Create the unique key for each row in new column by joining Date and description (you can use excel concatenate function), in both sheets.
  3. Use the Excel's VLOOKUP function to get the amount from first sheet into other in the new column. Use the unique key created in the step above as Lookup_value.

user354735

Posted 2014-10-29T16:05:00.573

Reputation:

Asked: 2014-10-29T16:05:00.573

Viewed: 200 times

Active: 2014-10-30T08:54:39.423