Bank Reconciliation- Extracting Agreements

[Bars03]

Hi

I am working on a macro to speed up my bank reconciliation process. I am almost there but need a little help on extracting agreement numbers that should be contained within the narrative of the bank statement thus allowing me to clear against my general ledger transactions. The below data is a fake example of a bank statement I have pulled together.

The headers will populate columns A to M on my worksheet.

Within the narrative there will be seven digit Agreement numbers i.e. 8831773 and 9317014

Most of the time they tend to appear in the narrative of the cell with spaces before and after the agreement number. But this will not always be the case and sometimes they may be preceded/followed by text without any gaps.

I have a separate spreadsheet called Agreements which lists all the agreements on the system.

Is is possible to have a macro that will satisfactorily check the data in Column M on the bank statement spreadsheet and check if it contains an agreement that appears on the Agreements spreadsheet? If so it will need to populate column N on the bank statement spreadsheet with the agreement number or leave blank.

GROUP

ACC ID

ACCOUNT NO

TYPE

BANK CODE

CURR

ENTRY DATE

AS AT

AMOUNT

TLA CODE

CHEQUE NO

STATUS

DESCRIPTION

90295762

20/07/1919

GBP

08/05/2015

Close

1

STC

C

A CASTO 8831773 STOFrom: 77-66-44 19956666

90295762

20/07/1982

GBP

08/05/2015

Close

6.9

STC

C

CARLTON C 8836717 STOFrom: 60-00-50 71366714

90295762

20/07/1982

GBP

08/05/2015

Close

8.28

STC

C

J BRAMLIN 9317014 STOFrom: 30-94-48 45026368

90295762

20/07/1982

GBP

08/05/2015

Close

13.59

STC

C

MR SAMUEL J COPPER 8817174 STOFrom: 55-51-28 61192205

90295762

20/07/1982

GBP

08/05/2015

Close

20

STC

C

MRS C ODUNLAMI 8868527 STOFrom: 60-05-02 48127798

90295762

20/07/1982

GBP

08/05/2015

Close

21.76

STC

C

RAQUEL BARKES 9325124STOFrom: 09-01-27 74256221

90295762

20/07/1982

GBP

08/05/2015

Close

26.07

STC

C

M MILES8860100 STOFrom: 30-91-79 16634560

90295762

20/07/1982

GBP

08/05/2015

Close

40.99

STC

C

GORDON KA 9717752 STOFrom: 55-10-31 17503013

90295762

20/07/1982

GBP

08/05/2015

Close

46.32

STC

C

M MILES 9321564 STOFrom: 30-91-79 16636960

90295762

20/07/1982

GBP

08/05/2015

Close

55.96

STC

C

MR SINGHER A8971947 STOFrom: 08-92-49 05124600

90295762

20/07/1982

GBP

08/05/2015

Close

62.3

STC

C

MR SINGHA8881333 STOFrom: 08-91-49 05523600

90295762

20/07/1982

GBP

08/05/2015

Close

110

STC

C

A DODD MR J TODD8870956 STOFrom: 11-16-70 00458341

90295762

20/07/1982

GBP

08/05/2015

Close

98

FPC

C

0OUNG RICHARDO L L 8876981 BGCFrom: 60-04-14 75024487

<tbody>
</tbody>

Thanks in Advance

 

[JoeMo]

Here's a UDF that will pull the 7-digit agreement number from col M entries. After you install the UDF it can be used like any worksheet function (see example below). Once you have extracted the 7-digit number it should be easy to see if there's a MATCH on the Agreements sheet.

Excel Workbook
	M	N
1	DESCRIPTION	Agreement#
2	A CASTO 8831773 STOFrom: 77-66-44 19956666	8831773
3	CARLTON C 8836717 STOFrom: 60-00-50 71366714	8836717
4	J BRAMLIN 9317014 STOFrom: 30-94-48 45026368	9317014
5	MR SAMUEL J COPPER 8817174 STOFrom: 55-51-28 61192205	8817174
6	MRS C ODUNLAMI 8868527 STOFrom: 60-05-02 48127798	8868527
7	RAQUEL BARKES 9325124STOFrom: 09-01-27 74256221	9325124
8	M MILES8860100 STOFrom: 30-91-79 16634560	8860100
9	GORDON KA 9717752 STOFrom: 55-10-31 17503013	9717752
10	M MILES 9321564 STOFrom: 30-91-79 16636960	9321564
11	MR SINGHER A8971947 STOFrom: 08-92-49 05124600	8971947
12	MR SINGHA8881333 STOFrom: 08-91-49 05523600	8881333
13	A DODD MR J TODD8870956 STOFrom: 11-16-70 00458341	8870956
14	0OUNG RICHARDO L L 8876981 BGCFrom: 60-04-14 75024487	8876981
Sheet12

To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the code from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Press Alt+F8 keys to run the code
7. Make sure you have enabled macros whenever you open the file or the code will not run.

Function AgreementNum(S As String)
'Agreement numbers are 7 digits and may or may not be separated from rest of string S by spaces
'Assume, at most only one 7-digit number per string.
With CreateObject("VBScript.Regexp")
    .Global = True
    .Pattern = "(\d{7})"
    If .test(S) Then
        AgreementNum = .Execute(S)(0)
    Else
        AgreementNum = CVErr(xlErrNA)
    End If
End With
End Function

 

[sheetspread]

This worked on your sample, see how it does on the rest:

Excel 2010
	M	N
1	DESCRIPTION
2	A CASTO 8831773 STOFrom: 77-66-44 19956666	8831773
3	CARLTON C 8836717 STOFrom: 60-00-50 71366714	8836717
4	J BRAMLIN 9317014 STOFrom: 30-94-48 45026368	9317014
5	MR SAMUEL J COPPER 8817174 STOFrom: 55-51-28 61192205	8817174
6	MRS C ODUNLAMI 8868527 STOFrom: 60-05-02 48127798	8868527
7	RAQUEL BARKES 9325124STOFrom: 09-01-27 74256221	9325124
8	M MILES8860100 STOFrom: 30-91-79 16634560	8860100
9	GORDON KA 9717752 STOFrom: 55-10-31 17503013	9717752
10	M MILES 9321564 STOFrom: 30-91-79 16636960	9321564
11	MR SINGHER A8971947 STOFrom: 08-92-49 05124600	8971947
12	MR SINGHA8881333 STOFrom: 08-91-49 05523600	8881333
13	A DODD MR J TODD8870956 STOFrom: 11-16-70 00458341	8870956
14	0OUNG RICHARDO L L 8876981 BGCFrom: 60-04-14 75024487	8876981
Sheet8

Cell Formulas
Range	Formula
N2	=TRIM(RIGHT(MID(M2,FIND("From",M2,1)-12,9),MAX(7,LEN(MID(M2,FIND("From",M2,1)-12,9))-1)))

 

[Bars03]

Thanks, seems to work in most cases