Matching 2 columns - quick easy way?

kryptek49

New Member
Joined
Mar 5, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi, I work at an accountants and have been tasked with reconciling a clients bank account. They use accounting software and have manually entered around 1400 bank transactions. The bank is now out by about £200k, and I need to find out where they have gone wrong. It's not 1 big transaction out, but 100's of transactions that don't add up.

I have the clients list of bank payments (in and out) in one column (say A), and a separate column (B) with the actual bank figures. What I want to do is find a way to match column A to column B without having to manually tick the 2. There are several payments in both columns that have the same value, for example £100 will be repeated multiple times in both columns, therefore looking at items that aren't duplicates doesn't really help.

Is there a way, either VBA or formula's that I can work through this data quickly? I'm dreading having to go through it manually and thought someone here must know!

Thank you.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,599
Office Version
  1. 365
Platform
  1. Windows
Welcome to Mr Excel :)

Here is a quick way to do it with formulas, you might find it easier to check if you separate the client list from the actual and have the formula to check column A in column B.
Additionally, changing "OK" to "" would leave the 'good' transactions with a blank cell, again, making it easier when you have to look down the list to check the remaining data.

Sample Data.xlsx
ABCD
1List 1List 2List 1 checkList 2 check
2109OKOK
354OKOK
451ErrorOK
5410OKOK
621OKOK
7910OKOK
829OKOK
992OKOK
1046ErrorError
1185OKOK
1229ErrorOK
1378OKOK
1438ErrorError
1518OKError
1659ErrorError
1717OKOK
1872OKOK
1997OKOK
20109OKError
Sheet5
Cell Formulas
RangeFormula
C2:C20C2=IF(COUNTIF(A$2:A2,A2)<=COUNTIF(B:B,A2),"OK","Error")
D2:D20D2=IF(COUNTIF(B$2:B2,B2)<=COUNTIF(A:A,B2),"OK","Error")
 

Watch MrExcel Video

Forum statistics

Threads
1,129,367
Messages
5,635,853
Members
416,886
Latest member
coreyalaurence37

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top