Comparing two lists in excel

Didn't find your answer?

A client has managed to muck up their cloud bookkeeping so there are transactions missing from the bank feed.

I have managed to get both the bank transactions and the transactions from the software into excel.

Any thoughts on how to compare the two schedules and so I can generate the missing items please? 


Replies (14)

Please login or register to join the discussion.

By SXGuy
16th Jun 2021 07:12

But there are other ways. Simplest way would be to look at the bank statement and start from the last time the balance matched and work your way back.

The other method would be to create a balance column in excel and again look for the last time it matched and work your way back.

Thanks (0)
Replying to SXGuy:
By Matrix
16th Jun 2021 07:23

Thanks it is not two columns, it is two sets of data.

The bank never matched, your suggestion is what I would normally do if a few transactions missing. There are 70,000 transactions and I think they deleted over 1,000 after import so I need to know if I can do this in excel or not. (Well I have hired someone to sort it by comparing printouts but wondering if excel would work.)

Thanks (1)
Replying to Matrix:
By Cheshire
16th Jun 2021 08:20

So you want to compare two sheets that effectively in different workbooks?

Try this (scroll right down the page so you ignore the massive ad for the tool how to do it after number '3').

Thanks (0)
Routemaster image
By tom123
16th Jun 2021 08:36

You may find Access a better tool.

Excel can only do 'internal' joins where list A items equal list B.

Access can do "in list A but not list B" type joins as well.

Thanks (0)
By rmillaree
16th Jun 2021 09:27

One slightly different simple trick is to sort the two columns of numbers into ascending number order then list side by side with column c having a-b sum. when i find entry in one column and not the other move the relevant row down one and carry on. This only works though if the numbers should be the same - doesnt take much in the way of batching or the like to throw this trick out the window..

For quickbooks and sage i also sometimes just compare differences (bs and tb) at different dates on the tb(sage) or bank register(qb) and list the difference - start with once a month - then half the time periods if the difference has changed that month till you find the period when the transactions are missing - it tends to be with bank feeds they are often in tight date range when things go wrong.

Thanks (0)
By frankfx
16th Jun 2021 09:48

Given the risk that there will be mispostings / misanalyis when you isolate the missing transactions.

Are you seeking supplier statements?

Reconciliation with payroll?

Reconciliation with lease payments?

And so on.

And other external proof to support final TB figures at one or more dates

Are. Bankings likely to cause reconciliation problems to debtors ledger.

Will you need to look at Directors own bank accounts.

In short get the requests for the foregoing in place, in parallel with the bank transaction task.

This may provide you with assurance that end result is a reliable set of Cloud accounts, and you will not be constantly ironing out wrinkles in future periods.

Thanks (0)
By LW64
16th Jun 2021 10:53

Assuming that you are taking a multiple analysis approach, rather than cross tick 70,000 entries. If you stack one set of data at the bottom of the other and conditionally format the column for duplicate amounts, anything not flagged/highlighted is missing.
You might want to concatenate something around the month and an amount to drive that a bit closer. Wouldn't use actual date as the posting dates are unlikely to agree with the bank posted dates.
Might get a bit closer to what is missing.

Thanks (1)
Danny Kent
By Viciuno
16th Jun 2021 11:03

Depending what software you use can you import the full bank CSV and the software will recognise and delete duplicate transactions?

With Xero you could give it a go and if it doesn't work/make things worse just delete the newest statement import completely and try something else.

Thanks (0)
By Duggimon
16th Jun 2021 11:14

You could put both lists in to one list and sort by value then date, that would let you use conditional formatting to quickly find all the unique values provided the dates between the two datasets don't vary.

You would still have a small chance that you had payments for the same amount to different people on the same day that this approach would miss. If the payee field is the same in both sets then you could factor this in to the conditional formatting, otherwise it'd be a manual scan but I don't think it would take all that long.

Thanks (0)
By turtleburgers
16th Jun 2021 12:08

I would add a column to concatenate the date, description and value of every transaction in each list to give a unique transaction reference.

Then run countif against these lists, any transaction with a 0 count is present on one list but not the other.

Thanks (2)
Replying to turtleburgers:
By JamesDS
17th Jun 2021 10:56

I have to do this all the time for all sorts of data!

Use TurtleBurgers' unique reference method on each dataset (remembering to use trim() to remove spaces from each cell concatenated) . Then sort on that column. The sorting is important as vlookup does odd thing when the data is not sorted.

Then on each dataset, add a column to the end that vlookups that unique reference with the same column in the other dataset. Anything that is not #N/A will show you a missing transaction from one or other data set and reconciling the two is then simple, albeit time-consuming.

Also when constructing your vlookup columns, make sure to set the [range-lookup] parameter to FALSE to avoid excel attempting to make a fuzzy match.

Thanks (0)
By User deleted
16th Jun 2021 12:52

I would run a lookup against the two sets of data. You can do Xlookups against multiple criteria.
If you are unsure take a look here.

Thanks (0)
By Matrix
16th Jun 2021 13:47

Thanks for all the suggestions. I will take a look at some of the functions as I do like a bit of excel.

In the end I got someone to compare the entries manually which was good as sometimes there a few customer receipts adding up to one bank payment so this would not have been spotted in excel.

Thanks (0)
By Nick.Ferriter
17th Jun 2021 10:56

So you add both our tables as data sources. Power Query will handle that fine, just got to Data > Get Data > From File. It should interpret CSV, TXT, whatever else as a table and treat it just as if it came from within the same file.

So let's say you want to match Date, AccountNo. & Amount.

Make a new query, Data > Get Data > Combine Queries > Merge. You want an Inner Join between our tables. Hold Ctrl and select the column headers, in identical order, for both tables. It will compare the columns together according to the number displayed above them (i.e., if you have 3 above Amount in one, and 3 above Date in the other, it's wrong).

Now you should be golden. You should have just a list of the entries that match on all three columns. At this point you're going to have to compare your query to your own manual work and diagnose why something did or didn't show up incorrectly. Here are the main culprits I might foresee:

Manual entry. That "Memo" field is human-entered. It's never gonna match. So leave it out of the column selection.

One system generates codes with a trailing space and the other doesn't. Use the "Trim" transformation.

Those dates are stored as Text instead of Date. The Account number is stored as a Number instead of Text. For best results, keep strict control not only of the data type in Power Query, but also in the source table in Excel.

If you want to see all the ones that don’t match you use an anti-join It will keep only rows that do not match the other table. The downside is that it will only do so for one side of the merge - so if you want non-matched rows from both sides, you will have to run a merge for each, and do an Append merge to combine their outputs.

Appending is the other choice vs. Merge. All it does is combine two tables - IF their column headers and data types are identical.

Thanks (0)