Lookup values and add further worksheets to compare and contrast data

sleek12

Board Regular
Joined
May 3, 2014
Messages
71
Office Version
  1. 365
Platform
  1. Windows
Cross-posted from another excel forum as i could not obtain answers from therein, Lookup values and add further worksheets to compare and contrast data.

My macro1 yields the following data, please place special emphasis on circled items:-
maro1result.jpg




What I want is macro 2 that creates worksheets from circled area :-
example1.jpg



Bank worksheet details

workbook1.xlsx
ABCDEFGHI
1Trn DtReference NoTransaction DetailAddl TextValue DtInstrument Code Debit Amt Credit Amt Balance
207-Jan-20190001606190070205INWARD CLEARING63-006-ELVIS DSOUZA07-Jan-2019178310000500,000.00
308-Jan-20190001606190080026INWARD CLEARING63-006-ACME INSURANCE COMPANY LTD08-Jan-2019180410000490,000.00
409-Jan-20190006300190090212INWARD CLEARING TRANSFER78_021_KING'S BANK_KING'S BANK09-Jan-2019050,000.00540,000.00
510-Jan-2019000INTT190100490INCOMING SWIFT TRANSFERFT19010C6THK BY_ORDER: -KING'S INSURANCE AGENCY P.O BOX 4179.00 -10-Jan-20196,500.00546,500.00
611-Jan-2019041LOCH190110022IN-HOUSE CHEQUE2020190000090964 - ACME INSURANCE COMPANY LIMITED-11-Jan-2019180550,000.00496,500.00
711-Jan-2019041LOCH190110023IN-HOUSE CHEQUE2020190000090914 - ACME INSURANCE COMPANY LIMITED-11-Jan-2019180650,000.00446,500.00
816-Jan-20190006300190160860INWARD CLEARING TRANSFER78_021_KING'S INSURANCE AGENCY_KING'S INSURANCE AGENCY16-Jan-201905,000.00451,500.00
923-Jan-20190006300190230008INWARD CLEARING TRANSFER78_021_KING'S INSURANCE AGENCY_KING'S INSURANCE AGENCY23-Jan-201905,000.00456,500.00
1028-Jan-2019000INTT190280013INCOMING SWIFT TRANSFER046250119RTGS002 BY_ORDER: -ACME INSURANCE CO LTD P O BOX 44444-00623 NAIROBI-28-Jan-201920,000.00476,500.00
1128-Jan-20190418314190280001ELECTRONIC FUNDS TRANSFERSALARY28-Jan-2019180860,000.00416,500.00
1228-Jan-20190418314190280001ELECTRONIC FUNDS TRANSFER CHARGESSALARY28-Jan-201912,000.00404,500.00
1328-Jan-20190418314190280001EXCISE DUTYSALARY28-Jan-20192,400.00402,100.00
1428-Jan-20190418315190280001ELECTRONIC FUNDS TRANSFERSALARY28-Jan-2019180760,000.00342,100.00
1528-Jan-20190418315190280001ELECTRONIC FUNDS TRANSFER CHARGESSALARY28-Jan-201917,000.00325,100.00
1628-Jan-20190418315190280001EXCISE DUTYSALARY28-Jan-201910,000.00315,100.00
1728-Jan-20190418316190280001ELECTRONIC FUNDS TRANSFERSALARY28-Jan-2019180960,000.00255,100.00
1828-Jan-20190418316190280001ELECTRONIC FUNDS TRANSFER CHARGESSALARY28-Jan-201915,000.00240,100.00
1928-Jan-20190418316190280001EXCISE DUTYSALARY28-Jan-20193,000.00237,100.00
2028-Jan-20190418317190280001ELECTRONIC FUNDS TRANSFERSALARY28-Jan-2019181090,000.00147,100.00
2128-Jan-20190418317190280001ELECTRONIC FUNDS TRANSFER CHARGESSALARY28-Jan-201910,000.00137,100.00
Bank
Cell Formulas
RangeFormula
I3:I21I3=I2-G3+H3



ERP worksheet details

workbook1.xlsx
ABCDEFG
1DateReceipt NoDescriptionCheque NoDebitCreditBalance
2MARKETING to ACME INSURANCE COMPANY193820000480,000.00
301/01/19R/HQ/19/008197Bank Deposit Code DEP19/1125116,000.000496,000.00
402/01/19R/HQ/19/000002Bank Deposit Code DEP19/0018114512,000.000508,000.00
502/01/19R/HQ/19/000003Bank Deposit Code DEP19/0018114522,000.000530,000.00
602/01/19R/HQ/19/000013Bank Deposit Code DEP19/00181180410,000.000540,000.00
702/01/19R/HQ/19/000016Bank Deposit Code DEP19/00181158546,500.000546,500.00
802/01/19R/HQ/19/000017Bank Deposit Code DEP19/00181158546,500.000553,000.00
907/01/19SALARY ARREARS to ACME INSURANCE COMPANY LIMITED178310000543,000.00
1008/01/19Being internal account transfer( Rachel Imo: recovery unpaid premium)180410000533,000.00
1110/01/19R/VET/19/000443Bank Deposit Code DEP19/09848FT190080RQ0V5,000.000538,000.00
1210/01/19R/VET/19/000442Bank Deposit Code DEP19/09849FT190080RQ0V5,000.000543,000.00
1310/01/19R/VET/19/000450Bank Deposit Code DEP19/09847FT9010C6THK6,500.000549,500.00
1411/01/19Payment to Kenya Revenue Authority180550000499,500.00
1511/01/19Payment to Kenya Revenue Authority180650000449,500.00
1628/01/19R/VET/19/000739Bank paymentCode DEP19/10788FT19015MXWQ060000389,500.00
1728/01/19R/VET/19/001115Bank payment Code DEP19/00667FT190ZZTS732400387,100.00
1828/01/19BEING INTERNAL TRANSFER TO DTB15000372,100.00
1928/01/19BEING INTERNAL TRANSFER TO DTB90000282,100.00
2028/01/19BEING INTERNAL TRANSFER TO DTB10000272,100.00
2128/01/19BEING INTERNAL TRANSFER TO DTB15000257,100.00
2228/01/19BEING INTERNAL TRANSFER TO DTB0257,100.00
2328/01/19BEING INTERNAL TRANSFER TO DTB0257,100.00
2428/01/19BEING INTERNAL TRANSFER TO DTB0257,100.00
2528/01/19BEING INTERNAL TRANSFER TO DTB0257,100.00
2628/01/19BEING INTERNAL TRANSFER TO DTB0257,100.00
2728/01/19BEING INTERNAL TRANSFER TO DTB0257,100.00
2828/01/19BEING INTERNAL TRANSFER TO DTB0257,100.00
ERP
Cell Formulas
RangeFormula
G3:G28G3=G2+E3-F3



Recon (Macro1 , I already have as Add-in)

workbook1.xlsx
ABCDEFGHI
1EXAMPLEUSE.XLSX!ERPEXAMPLEUSE.XLSX!BANKRESULTS USING Macro 1
2CreditCount Of CreditSum Of CreditCount Of Debit AmtSum Of Debit AmtComparison of Credit Based on Non-Zero value in Numerical field
310000330000440000-1Of course, every different scenario will give its own
450000210000021000000results with no. of rows varying
5600001600003180000-2
6240012400124000
7150002300001150001
8900001900001900000
92000012000000MARKETING to ACME INSURANCE COMPANY1938
10120000011200028/01/20190418314190280001ELECTRONIC FUNDS TRANSFER CHARGESSALARY
11170000011700028/01/20190418315190280001ELECTRONIC FUNDS TRANSFER CHARGESSALARY
123000001300028/01/20190418316190280001EXCISE DUTYSALARY
Resultsof Recon
Cell Formulas
RangeFormula
F3:F8F3=B3-D3
F9:G9F9=FILTER(ERP!C1:D28,ERP!F1:F28='Resultsof Recon'!A9)
F10:I12F10=FILTER(Bank!$A$1:$D$21,Bank!$G$1:$G$21='Resultsof Recon'!A10)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
Bank!_FilterDatabase=Bank!$A$1:$I$21F10:F12


Results i want after i'm helped with 2nd Macro, lookup the circled ones in Recon and create worksheets compare and contrast Bank and ERP , i used conditional formatting to highlight duplicates that how the light red colour arises.
10000
workbook2.xlsx
ABCDEFGHIJKL
110000BankERP
207/01/20190001606190070205INWARD CLEARING63-006-ELVIS DSOUZA07/01/2019178307/01/20190SALARY ARREARS to ACME INSURANCE COMPANY LIMITED17830
308/01/20190001606190080026INWARD CLEARING63-006-ACME INSURANCE COMPANY LTD08/01/2019180408/01/20190Being internal account transfer( Rachel Imo: recovery unpaid premium)18040
428/01/20190418315190280001EXCISE DUTYSALARY28/01/2019028/01/20190BEING INTERNAL TRANSFER TO DTB00
528/01/20190418317190280001ELECTRONIC FUNDS TRANSFER CHARGESSALARY28/01/20190
10000
Cell Formulas
RangeFormula
A2:F5A2=FILTER(Bank!A1:F21,Bank!G1:G21=A1)
H2:L4H2=FILTER(ERP!A1:E28,ERP!F1:F28=A1)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
Bank!_FilterDatabase=Bank!$A$1:$I$21A2
ERP!_FilterDatabase=ERP!$A$1:$G$19H2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F:F,K:KCell ValueduplicatestextNO



60000
workbook2.xlsx
ABCDEFGHIJKL
160000BankERP
228/01/20190418314190280001ELECTRONIC FUNDS TRANSFERSALARY28/01/2019180828/01/2019R/VET/19/000739Bank paymentCode DEP19/10788FT19015MXWQ00
328/01/20190418315190280001ELECTRONIC FUNDS TRANSFERSALARY28/01/20191807
428/01/20190418316190280001ELECTRONIC FUNDS TRANSFERSALARY28/01/20191809
5
60000
Cell Formulas
RangeFormula
A2:F4A2=FILTER(Bank!A1:F21,Bank!G1:G21=A1)
H2:L2H2=FILTER(ERP!A1:E28,ERP!F1:F28=A1)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
Bank!_FilterDatabase=Bank!$A$1:$I$21A2
ERP!_FilterDatabase=ERP!$A$1:$G$19H2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F:F,K:KCell ValueduplicatestextNO


and so on and so forth for 2400,15000,90000 using the office 365 formula of TRANSPOSE( FILTER).
 
Last edited:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

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
Back
Top