sleek12
Board Regular
- Joined
- May 3, 2014
- Messages
- 62
- Office Version
- 365
- Platform
- 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:-
What I want is macro 2 that creates worksheets from circled area :-
Bank worksheet details
ERP worksheet details
Recon (Macro1 , I already have as Add-in)
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
60000
and so on and so forth for 2400,15000,90000 using the office 365 formula of TRANSPOSE( FILTER).
My macro1 yields the following data, please place special emphasis on circled items:-
What I want is macro 2 that creates worksheets from circled area :-
Bank worksheet details
| workbook1.xlsx | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | Trn Dt | Reference No | Transaction Detail | Addl Text | Value Dt | Instrument Code | Debit Amt | Credit Amt | Balance | ||
| 2 | 07-Jan-2019 | 0001606190070205 | INWARD CLEARING | 63-006-ELVIS DSOUZA | 07-Jan-2019 | 1783 | 10000 | 500,000.00 | |||
| 3 | 08-Jan-2019 | 0001606190080026 | INWARD CLEARING | 63-006-ACME INSURANCE COMPANY LTD | 08-Jan-2019 | 1804 | 10000 | 490,000.00 | |||
| 4 | 09-Jan-2019 | 0006300190090212 | INWARD CLEARING TRANSFER | 78_021_KING'S BANK_KING'S BANK | 09-Jan-2019 | 0 | 50,000.00 | 540,000.00 | |||
| 5 | 10-Jan-2019 | 000INTT190100490 | INCOMING SWIFT TRANSFER | FT19010C6THK BY_ORDER: -KING'S INSURANCE AGENCY P.O BOX 4179.00 - | 10-Jan-2019 | 6,500.00 | 546,500.00 | ||||
| 6 | 11-Jan-2019 | 041LOCH190110022 | IN-HOUSE CHEQUE | 2020190000090964 - ACME INSURANCE COMPANY LIMITED- | 11-Jan-2019 | 1805 | 50,000.00 | 496,500.00 | |||
| 7 | 11-Jan-2019 | 041LOCH190110023 | IN-HOUSE CHEQUE | 2020190000090914 - ACME INSURANCE COMPANY LIMITED- | 11-Jan-2019 | 1806 | 50,000.00 | 446,500.00 | |||
| 8 | 16-Jan-2019 | 0006300190160860 | INWARD CLEARING TRANSFER | 78_021_KING'S INSURANCE AGENCY_KING'S INSURANCE AGENCY | 16-Jan-2019 | 0 | 5,000.00 | 451,500.00 | |||
| 9 | 23-Jan-2019 | 0006300190230008 | INWARD CLEARING TRANSFER | 78_021_KING'S INSURANCE AGENCY_KING'S INSURANCE AGENCY | 23-Jan-2019 | 0 | 5,000.00 | 456,500.00 | |||
| 10 | 28-Jan-2019 | 000INTT190280013 | INCOMING SWIFT TRANSFER | 046250119RTGS002 BY_ORDER: -ACME INSURANCE CO LTD P O BOX 44444-00623 NAIROBI- | 28-Jan-2019 | 20,000.00 | 476,500.00 | ||||
| 11 | 28-Jan-2019 | 0418314190280001 | ELECTRONIC FUNDS TRANSFER | SALARY | 28-Jan-2019 | 1808 | 60,000.00 | 416,500.00 | |||
| 12 | 28-Jan-2019 | 0418314190280001 | ELECTRONIC FUNDS TRANSFER CHARGES | SALARY | 28-Jan-2019 | 12,000.00 | 404,500.00 | ||||
| 13 | 28-Jan-2019 | 0418314190280001 | EXCISE DUTY | SALARY | 28-Jan-2019 | 2,400.00 | 402,100.00 | ||||
| 14 | 28-Jan-2019 | 0418315190280001 | ELECTRONIC FUNDS TRANSFER | SALARY | 28-Jan-2019 | 1807 | 60,000.00 | 342,100.00 | |||
| 15 | 28-Jan-2019 | 0418315190280001 | ELECTRONIC FUNDS TRANSFER CHARGES | SALARY | 28-Jan-2019 | 17,000.00 | 325,100.00 | ||||
| 16 | 28-Jan-2019 | 0418315190280001 | EXCISE DUTY | SALARY | 28-Jan-2019 | 10,000.00 | 315,100.00 | ||||
| 17 | 28-Jan-2019 | 0418316190280001 | ELECTRONIC FUNDS TRANSFER | SALARY | 28-Jan-2019 | 1809 | 60,000.00 | 255,100.00 | |||
| 18 | 28-Jan-2019 | 0418316190280001 | ELECTRONIC FUNDS TRANSFER CHARGES | SALARY | 28-Jan-2019 | 15,000.00 | 240,100.00 | ||||
| 19 | 28-Jan-2019 | 0418316190280001 | EXCISE DUTY | SALARY | 28-Jan-2019 | 3,000.00 | 237,100.00 | ||||
| 20 | 28-Jan-2019 | 0418317190280001 | ELECTRONIC FUNDS TRANSFER | SALARY | 28-Jan-2019 | 1810 | 90,000.00 | 147,100.00 | |||
| 21 | 28-Jan-2019 | 0418317190280001 | ELECTRONIC FUNDS TRANSFER CHARGES | SALARY | 28-Jan-2019 | 10,000.00 | 137,100.00 | ||||
Bank | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| I3:I21 | I3 | =I2-G3+H3 |
ERP worksheet details
| workbook1.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Date | Receipt No | Description | Cheque No | Debit | Credit | Balance | ||
| 2 | MARKETING to ACME INSURANCE COMPANY | 1938 | 20000 | 480,000.00 | |||||
| 3 | 01/01/19 | R/HQ/19/008197 | Bank Deposit Code DEP19/11251 | 16,000.00 | 0 | 496,000.00 | |||
| 4 | 02/01/19 | R/HQ/19/000002 | Bank Deposit Code DEP19/00181 | 145 | 12,000.00 | 0 | 508,000.00 | ||
| 5 | 02/01/19 | R/HQ/19/000003 | Bank Deposit Code DEP19/00181 | 145 | 22,000.00 | 0 | 530,000.00 | ||
| 6 | 02/01/19 | R/HQ/19/000013 | Bank Deposit Code DEP19/00181 | 1804 | 10,000.00 | 0 | 540,000.00 | ||
| 7 | 02/01/19 | R/HQ/19/000016 | Bank Deposit Code DEP19/00181 | 15854 | 6,500.00 | 0 | 546,500.00 | ||
| 8 | 02/01/19 | R/HQ/19/000017 | Bank Deposit Code DEP19/00181 | 15854 | 6,500.00 | 0 | 553,000.00 | ||
| 9 | 07/01/19 | SALARY ARREARS to ACME INSURANCE COMPANY LIMITED | 1783 | 10000 | 543,000.00 | ||||
| 10 | 08/01/19 | Being internal account transfer( Rachel Imo: recovery unpaid premium) | 1804 | 10000 | 533,000.00 | ||||
| 11 | 10/01/19 | R/VET/19/000443 | Bank Deposit Code DEP19/09848 | FT190080RQ0V | 5,000.00 | 0 | 538,000.00 | ||
| 12 | 10/01/19 | R/VET/19/000442 | Bank Deposit Code DEP19/09849 | FT190080RQ0V | 5,000.00 | 0 | 543,000.00 | ||
| 13 | 10/01/19 | R/VET/19/000450 | Bank Deposit Code DEP19/09847 | FT9010C6THK | 6,500.00 | 0 | 549,500.00 | ||
| 14 | 11/01/19 | Payment to Kenya Revenue Authority | 1805 | 50000 | 499,500.00 | ||||
| 15 | 11/01/19 | Payment to Kenya Revenue Authority | 1806 | 50000 | 449,500.00 | ||||
| 16 | 28/01/19 | R/VET/19/000739 | Bank paymentCode DEP19/10788 | FT19015MXWQ0 | 60000 | 389,500.00 | |||
| 17 | 28/01/19 | R/VET/19/001115 | Bank payment Code DEP19/00667 | FT190ZZTS73 | 2400 | 387,100.00 | |||
| 18 | 28/01/19 | BEING INTERNAL TRANSFER TO DTB | 15000 | 372,100.00 | |||||
| 19 | 28/01/19 | BEING INTERNAL TRANSFER TO DTB | 90000 | 282,100.00 | |||||
| 20 | 28/01/19 | BEING INTERNAL TRANSFER TO DTB | 10000 | 272,100.00 | |||||
| 21 | 28/01/19 | BEING INTERNAL TRANSFER TO DTB | 15000 | 257,100.00 | |||||
| 22 | 28/01/19 | BEING INTERNAL TRANSFER TO DTB | 0 | 257,100.00 | |||||
| 23 | 28/01/19 | BEING INTERNAL TRANSFER TO DTB | 0 | 257,100.00 | |||||
| 24 | 28/01/19 | BEING INTERNAL TRANSFER TO DTB | 0 | 257,100.00 | |||||
| 25 | 28/01/19 | BEING INTERNAL TRANSFER TO DTB | 0 | 257,100.00 | |||||
| 26 | 28/01/19 | BEING INTERNAL TRANSFER TO DTB | 0 | 257,100.00 | |||||
| 27 | 28/01/19 | BEING INTERNAL TRANSFER TO DTB | 0 | 257,100.00 | |||||
| 28 | 28/01/19 | BEING INTERNAL TRANSFER TO DTB | 0 | 257,100.00 | |||||
ERP | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G3:G28 | G3 | =G2+E3-F3 |
Recon (Macro1 , I already have as Add-in)
| workbook1.xlsx | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | EXAMPLEUSE.XLSX!ERP | EXAMPLEUSE.XLSX!BANK | RESULTS USING Macro 1 | ||||||||
| 2 | Credit | Count Of Credit | Sum Of Credit | Count Of Debit Amt | Sum Of Debit Amt | Comparison of Credit Based on Non-Zero value in Numerical field | |||||
| 3 | 10000 | 3 | 30000 | 4 | 40000 | -1 | Of course, every different scenario will give its own | ||||
| 4 | 50000 | 2 | 100000 | 2 | 100000 | 0 | results with no. of rows varying | ||||
| 5 | 60000 | 1 | 60000 | 3 | 180000 | -2 | |||||
| 6 | 2400 | 1 | 2400 | 1 | 2400 | 0 | |||||
| 7 | 15000 | 2 | 30000 | 1 | 15000 | 1 | |||||
| 8 | 90000 | 1 | 90000 | 1 | 90000 | 0 | |||||
| 9 | 20000 | 1 | 20000 | 0 | 0 | MARKETING to ACME INSURANCE COMPANY | 1938 | ||||
| 10 | 12000 | 0 | 0 | 1 | 12000 | 28/01/2019 | 0418314190280001 | ELECTRONIC FUNDS TRANSFER CHARGES | SALARY | ||
| 11 | 17000 | 0 | 0 | 1 | 17000 | 28/01/2019 | 0418315190280001 | ELECTRONIC FUNDS TRANSFER CHARGES | SALARY | ||
| 12 | 3000 | 0 | 0 | 1 | 3000 | 28/01/2019 | 0418316190280001 | EXCISE DUTY | SALARY | ||
Resultsof Recon | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F3:F8 | F3 | =B3-D3 |
| F9:G9 | F9 | =FILTER(ERP!C1:D28,ERP!F1:F28='Resultsof Recon'!A9) |
| F10:I12 | F10 | =FILTER(Bank!$A$1:$D$21,Bank!$G$1:$G$21='Resultsof Recon'!A10) |
| Dynamic array formulas. | ||
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| Bank!_FilterDatabase | =Bank!$A$1:$I$21 | F10: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 | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | |||
| 1 | 10000 | Bank | ERP | |||||||||||
| 2 | 07/01/2019 | 0001606190070205 | INWARD CLEARING | 63-006-ELVIS DSOUZA | 07/01/2019 | 1783 | 07/01/2019 | 0 | SALARY ARREARS to ACME INSURANCE COMPANY LIMITED | 1783 | 0 | |||
| 3 | 08/01/2019 | 0001606190080026 | INWARD CLEARING | 63-006-ACME INSURANCE COMPANY LTD | 08/01/2019 | 1804 | 08/01/2019 | 0 | Being internal account transfer( Rachel Imo: recovery unpaid premium) | 1804 | 0 | |||
| 4 | 28/01/2019 | 0418315190280001 | EXCISE DUTY | SALARY | 28/01/2019 | 0 | 28/01/2019 | 0 | BEING INTERNAL TRANSFER TO DTB | 0 | 0 | |||
| 5 | 28/01/2019 | 0418317190280001 | ELECTRONIC FUNDS TRANSFER CHARGES | SALARY | 28/01/2019 | 0 | ||||||||
10000 | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A2:F5 | A2 | =FILTER(Bank!A1:F21,Bank!G1:G21=A1) |
| H2:L4 | H2 | =FILTER(ERP!A1:E28,ERP!F1:F28=A1) |
| Dynamic array formulas. | ||
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| Bank!_FilterDatabase | =Bank!$A$1:$I$21 | A2 |
| ERP!_FilterDatabase | =ERP!$A$1:$G$19 | H2 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| F:F,K:K | Cell Value | duplicates | text | NO |
60000
| workbook2.xlsx | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | |||
| 1 | 60000 | Bank | ERP | |||||||||||
| 2 | 28/01/2019 | 0418314190280001 | ELECTRONIC FUNDS TRANSFER | SALARY | 28/01/2019 | 1808 | 28/01/2019 | R/VET/19/000739 | Bank paymentCode DEP19/10788 | FT19015MXWQ0 | 0 | |||
| 3 | 28/01/2019 | 0418315190280001 | ELECTRONIC FUNDS TRANSFER | SALARY | 28/01/2019 | 1807 | ||||||||
| 4 | 28/01/2019 | 0418316190280001 | ELECTRONIC FUNDS TRANSFER | SALARY | 28/01/2019 | 1809 | ||||||||
| 5 | ||||||||||||||
60000 | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A2:F4 | A2 | =FILTER(Bank!A1:F21,Bank!G1:G21=A1) |
| H2:L2 | H2 | =FILTER(ERP!A1:E28,ERP!F1:F28=A1) |
| Dynamic array formulas. | ||
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| Bank!_FilterDatabase | =Bank!$A$1:$I$21 | A2 |
| ERP!_FilterDatabase | =ERP!$A$1:$G$19 | H2 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| F:F,K:K | Cell Value | duplicates | text | NO |
and so on and so forth for 2400,15000,90000 using the office 365 formula of TRANSPOSE( FILTER).
Last edited:
