Filter out from the range that is coloured green, use those values to open new worksheet to compare and contrast

sleek12

Board Regular
Joined
May 3, 2014
Messages
72
Office Version
  1. 365
Platform
  1. Windows
After the reconciliation was complete, The range gives out as values in both worksheets, values only in first worksheet and values only in second worksheet. (see last portion)
But my area of interest is values in both worksheets. See below.

UseMasterrecon11
ABCDEF
1dec2021CASHBOOKdec2021NBK502BOTH
2AmountCount Of Credit Sum Of Credit Count Of Debit Sum Of Debit Frequency difference
32.50--512.50- 5
417.22--117.22- 1
530.00--260.00- 2
660.00--5300.00- 5
7150.00--2300.00- 2
8300.00--51,500.00- 5
9720.00--1720.00- 1
10960.00--1960.00- 1
115,063.00--15,063.00- 1
1213,000.00--113,000.00- 1
1314,000.00--114,000.00- 1
1414,113.00--114,113.00- 1
1520,000.00--120,000.00- 1
1623,000.00--123,000.00- 1
1723,750.00123,750.00123,750.00-
1830,449.65--130,449.65- 1
1932,700.00132,700.00--1
2040,000.00--140,000.00- 1
2141,225.00--141,225.00- 1
2241,276.65141,276.65141,276.65-
2350,000.004200,000.00301,500,000.00- 26
2465,076.65--165,076.65- 1
2570,000.00--2140,000.00- 2
2671,820.00171,820.00--1
2775,000.00--2150,000.00- 2
2880,776.65180,776.65--1
29100,000.003300,000.004400,000.00- 1
30104,000.00--1104,000.00- 1
31105,000.00--1105,000.00- 1
32110,776.651110,776.651110,776.65-
33139,729.951139,729.951139,729.95-
34145,776.651145,776.651145,776.65-
35150,000.00--5750,000.00- 5
36158,206.601158,206.601158,206.60-
37164,000.00--1164,000.00- 1
38167,450.001167,450.001167,450.00-
39200,000.002400,000.0051,000,000.00- 3
40200,553.301200,553.301200,553.30-
41208,800.00--1208,800.00- 1
42213,659.901213,659.901213,659.90-
43221,553.301221,553.301221,553.30-
44225,852.00--1225,852.00- 1
45238,803.301238,803.301238,803.30-
46269,153.301269,153.301269,153.30-
47269,222.501269,222.501269,222.50-
48271,183.251271,183.251271,183.25-
49276,450.00--1276,450.00- 1
50281,403.301281,403.301281,403.30-
51300,000.00--2600,000.00- 2
52309,883.251309,883.251309,883.25-
53319,756.651319,756.651319,756.65-
54350,000.00--82,800,000.00- 8
55352,994.201352,994.201352,994.20-
56500,000.001500,000.001500,000.00-
57770,000.001770,000.001770,000.00-
58826,576.551826,576.551826,576.55-
59869,536.271869,536.271869,536.27-
payments
Cell Formulas
RangeFormula
A3:A59A3= VStackλ(NBK.debit, ERP.credit)
B3:F59B3= LET( countCredit, COUNTIFS(ERP.credit, amounts), countDebit, COUNTIFS(NBK.debit, amounts), freqDiff, countCredit - countDebit, sumCredit, amounts * countCredit, sumDebit, amounts * countDebit, CHOOSE({1,2,3,4,5}, countCredit, sumCredit,countDebit,sumDebit,freqDiff))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A3:A1452Expression=$B3*$D3textNO
A3:A1452Expression=$D3textNO
A3:A1452Expression=$B3textNO



What i want is Green portion filtered out, and all those values posted as separate worksheets in same workbook.
UseMasterrecon11
ABCDEF
1dec2021CASHBOOKdec2021NBK502BOTH
1723,750.00123,750.00123,750.00-
2241,276.65141,276.65141,276.65-
2350,000.004200,000.00301,500,000.00- 26
29100,000.003300,000.004400,000.00- 1
32110,776.651110,776.651110,776.65-
33139,729.951139,729.951139,729.95-
34145,776.651145,776.651145,776.65-
36158,206.601158,206.601158,206.60-
38167,450.001167,450.001167,450.00-
39200,000.002400,000.0051,000,000.00- 3
40200,553.301200,553.301200,553.30-
42213,659.901213,659.901213,659.90-
43221,553.301221,553.301221,553.30-
45238,803.301238,803.301238,803.30-
46269,153.301269,153.301269,153.30-
47269,222.501269,222.501269,222.50-
48271,183.251271,183.251271,183.25-
50281,403.301281,403.301281,403.30-
52309,883.251309,883.251309,883.25-
53319,756.651319,756.651319,756.65-
55352,994.201352,994.201352,994.20-
56500,000.001500,000.001500,000.00-
57770,000.001770,000.001770,000.00-
58826,576.551826,576.551826,576.55-
payments
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A3:A1452Expression=$B3*$D3textNO
A3:A1452Expression=$D3textNO
A3:A1452Expression=$B3textNO


An example of the one of the values would be as :-

UseMasterrecon11
ABCDEFGH
150000dec2021Cash bookdec2021NBK502
223/12/2021private info0031/12/2021Private info50000
323/12/2021private info004668030/12/2021Private info50000
423/12/2021private info004672030/12/2021Private info50000
523/12/2021private info004688029/12/2021Private info50000
629/12/2021Private info50000
729/12/2021Private info50000
828/12/2021Private info50000
924/12/2021Private info50000
1023/12/2021Private info50000
1123/12/2021Private info50000
1223/12/2021Private info50000
1323/12/2021Private info50000
1422/12/2021Private info50000
1521/12/2021Private info50000
1620/12/2021Private info50000
1720/12/2021Private info50000
1817/12/2021Private info50000
1916/12/2021Private info50000
2015/12/2021Private info50000
2114/12/2021Private info50000
2214/12/2021Private info50000
2310/12/2021Private info50000
2409/12/2021Private info50000
2508/12/2021Private info50000
2607/12/2021Private info50000
2706/12/2021Private info50000
2806/12/2021Private info50000
2903/12/2021Private info50000
3002/12/2021Private info50000
3101/12/2021Private info50000
50000
Cell Formulas
RangeFormula
A2:D5A2=FILTER(dec2021COREcashbook!A1:D491,dec2021COREcashbook!E1:E491=A1)
F2:H31F2=FILTER(Table1_2[[#All],[Date]:[Debit ]],Table1_2[[#All],[Debit ]]=A1)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
dec2021COREcashbook!_FilterDatabase=dec2021COREcashbook!$A$1:$F$491A2
dec2021nbk502!_FilterDatabase=dec2021nbk502!$A$1:$D$569F2
ERP.debit=dec2021COREcashbook!$D$2:$D$491A2
dec2021nbk502!ExternalData_1=dec2021nbk502!$A$1:$D$569F2



To keep information complete, also refer to :- (I believe XL2BB made a constraint here as info was too large), herewith is portion from dec2021NBK502 bank statement

UseMasterrecon11
ABCD
1DateParticulars Debit Credit
231/12/2021Private info17.22
331/12/2021Private info113,000.00
431/12/2021Private info11,498.00
531/12/2021Private info281,403.30
631/12/2021Private info145,776.65
731/12/2021Private info36,734.00
831/12/2021Private info60,000.00
931/12/2021Private info500,000.00
1031/12/2021Private info6,064.00
1131/12/2021Private info300.00
1231/12/2021Private info2.50
1331/12/2021Private info60.00
1431/12/2021Private info164,000.00
1531/12/2021Private info9,732.00
1631/12/2021Private info3,550.00
1731/12/2021Private info150,000.00
1831/12/2021Private info11,671.00
1931/12/2021Private info99,751.00
2031/12/2021Private info83,000.00
2131/12/2021Private info10,500.00
2231/12/2021Private info18,084.00
2331/12/2021Private info19,000.00
2431/12/2021Private info46,000.00
2531/12/2021Private info16,000.00
2631/12/2021Private info43,087.00
2731/12/2021Private info42,302.00
2831/12/2021Private info90,054.00
2931/12/2021Private info50,000.00
3031/12/2021Private info15,000.00
3131/12/2021Private info1,200.00
3231/12/2021Private info15,000.00
3331/12/2021Private info33,143.00
3431/12/2021Private info7,187.00
3531/12/2021Private info62,467.00
3631/12/2021Private info29,600.00
3731/12/2021Private info29,600.00
3831/12/2021Private info221,553.30
3931/12/2021Private info238,803.30
4031/12/2021Private info110,776.65
4131/12/2021Private info26,922.00
4231/12/2021Private info8,949.00
4331/12/2021Private info35,953.00
4431/12/2021Private info2,100.00
4531/12/2021Private info11,000.00
4631/12/2021Private info11,000.00
4731/12/2021Private info24,691.00
4831/12/2021Private info24,691.00
4931/12/2021Private info21,210.00
5031/12/2021Private info46,297.00
5131/12/2021Private info1,659.00
5231/12/2021Private info12,008.00
5331/12/2021Private info9,033.00
5431/12/2021Private info14,023.00
5531/12/2021Private info10,251.00
5631/12/2021Private info11,055.00
5731/12/2021Private info7,101.00
5831/12/2021Private info5,116.00
5931/12/2021Private info24,385.00
6031/12/2021Private info27,594.00
6131/12/2021Private info11,367.00
6231/12/2021Private info50,000.00
6331/12/2021Private info6,860.00
6431/12/2021Private info12,324.00
6531/12/2021Private info32,567.00
6631/12/2021Private info5,100.00
6731/12/2021Private info5,670.00
6831/12/2021Private info19,690.00
6931/12/2021Private info1,300.00
7031/12/2021Private info30.00
7131/12/2021Private info150.00
7231/12/2021Private info39,478.00
7331/12/2021Private info30.00
7431/12/2021Private info150.00
7531/12/2021Private info21,414.00
7631/12/2021Private info19,756.00
7731/12/2021Private info30,315.00
7831/12/2021Private info13,204.00
7931/12/2021Private info1,870.00
8031/12/2021Private info100,000.00
8131/12/2021Private info104,000.00
8231/12/2021Private info350,000.00
8331/12/2021Private info150,000.00
8430/12/2021Private info600,000.00
8530/12/2021Private info327,457.00
8630/12/2021Private info4,300.00
8730/12/2021Private info11,093.00
8830/12/2021Private info352,994.20
8930/12/2021Private info40,000.00
9030/12/2021Private info269,153.30
9130/12/2021Private info3,800.00
9230/12/2021Private info85,900.00
9330/12/2021Private info22,401.00
9430/12/2021Private info25,805.00
9530/12/2021Private info75,876.00
9630/12/2021Private info38,240.00
9730/12/2021Private info8,841.00
9830/12/2021Private info11,011.00
9930/12/2021Private info6,000.00
10030/12/2021Private info12,000.00
10130/12/2021Private info20,000.00
10230/12/2021Private info6,209.00
10330/12/2021Private info10,000.00
10430/12/2021Private info9,800.00
10530/12/2021Private info15,000.00
10630/12/2021Private info43,000.00
10730/12/2021Private info45,810.00
10830/12/2021Private info11,500.00
10930/12/2021Private info2,500.00
11030/12/2021Private info52,197.00
11130/12/2021Private info20,000.00
11230/12/2021Private info29,000.00
11330/12/2021Private info70,000.00
11430/12/2021Private info10,000.00
11530/12/2021Private info50,000.00
11630/12/2021Private info50,000.00
11730/12/2021Private info120,000.00
11830/12/2021Private info15,423.00
11930/12/2021Private info15,423.00
12030/12/2021Private info10,000.00
12130/12/2021Private info10,000.00
12230/12/2021Private info30,000.00
12330/12/2021Private info30,000.00
12430/12/2021Private info2,500.00
12530/12/2021Private info3,800.00
12630/12/2021Private info7,441.00
12730/12/2021Private info2,600.00
12830/12/2021Private info20,876.00
12930/12/2021Private info50,000.00
13030/12/2021Private info31,854.00
13130/12/2021Private info49,002.00
13230/12/2021Private info16,290.00
13330/12/2021Private info49,479.00
13430/12/2021Private info10,500.00
13530/12/2021Private info15,163.00
13630/12/2021Private info13,285.00
13730/12/2021Private info27,522.00
13830/12/2021Private info3,800.00
13930/12/2021Private info5,101.00
14030/12/2021Private info7,656.00
14130/12/2021Private info3,800.00
14230/12/2021Private info25,870.00
14330/12/2021Private info3,800.00
14430/12/2021Private info2,067.00
14530/12/2021Private info7,113.00
14630/12/2021Private info5,771.00
14730/12/2021Private info29,678.00
14830/12/2021Private info12,903.00
14930/12/2021Private info13,182.00
15030/12/2021Private info15,411.00
15130/12/2021Private info23,447.00
15230/12/2021Private info17,600.00
15330/12/2021Private info42,744.00
15430/12/2021Private info6,205.00
15530/12/2021Private info36,697.00
15630/12/2021Private info70,000.00
15730/12/2021Private info100,000.00
15830/12/2021Private info50,000.00
15930/12/2021Private info150,000.00
16030/12/2021Private info350,000.00
16129/12/2021Private info3,899.00
16229/12/2021Private info8,870.00
16329/12/2021Private info300.00
16429/12/2021Private info2.50
16529/12/2021Private info60.00
16629/12/2021Private info200,000.00
16729/12/2021Private info44,000.00
16829/12/2021Private info43,449.00
16929/12/2021Private info25,000.00
17029/12/2021Private info13,000.00
17129/12/2021Private info17,000.00
17229/12/2021Private info12,290.00
17329/12/2021Private info62,126.00
17429/12/2021Private info17,000.00
17529/12/2021Private info46,214.00
17629/12/2021Private info50,000.00
17729/12/2021Private info10,000.00
17829/12/2021Private info50,000.00
17929/12/2021Private info20,000.00
18029/12/2021Private info23,000.00
18129/12/2021Private info50,000.00
18229/12/2021Private info13,000.00
18329/12/2021Private info17,000.00
18429/12/2021Private info15,711.00
18529/12/2021Private info18,000.00
18629/12/2021Private info4,072.00
18729/12/2021Private info32,541.00
18829/12/2021Private info50,000.00
18929/12/2021Private info55,863.00
19029/12/2021Private info2,800.00
19129/12/2021Private info45,321.00
19229/12/2021Private info91,696.00
19329/12/2021Private info21,865.00
19429/12/2021Private info34,652.00
19529/12/2021Private info15,121.00
19629/12/2021Private info11,907.00
19729/12/2021Private info17,452.00
19829/12/2021Private info6,199.00
19929/12/2021Private info22,876.00
20029/12/2021Private info15,517.00
dec2021nbk502


And also portion from dec2021 Cash book:-
UseMasterrecon11
ABCD
1DateParticulars Debit Credit
231/12/2021Private info17.22
331/12/2021Private info113,000.00
431/12/2021Private info11,498.00
531/12/2021Private info281,403.30
631/12/2021Private info145,776.65
731/12/2021Private info36,734.00
831/12/2021Private info60,000.00
931/12/2021Private info500,000.00
1031/12/2021Private info6,064.00
1131/12/2021Private info300.00
1231/12/2021Private info2.50
1331/12/2021Private info60.00
1431/12/2021Private info164,000.00
1531/12/2021Private info9,732.00
1631/12/2021Private info3,550.00
1731/12/2021Private info150,000.00
1831/12/2021Private info11,671.00
1931/12/2021Private info99,751.00
2031/12/2021Private info83,000.00
2131/12/2021Private info10,500.00
2231/12/2021Private info18,084.00
2331/12/2021Private info19,000.00
2431/12/2021Private info46,000.00
2531/12/2021Private info16,000.00
2631/12/2021Private info43,087.00
2731/12/2021Private info42,302.00
2831/12/2021Private info90,054.00
2931/12/2021Private info50,000.00
3031/12/2021Private info15,000.00
3131/12/2021Private info1,200.00
3231/12/2021Private info15,000.00
3331/12/2021Private info33,143.00
3431/12/2021Private info7,187.00
3531/12/2021Private info62,467.00
3631/12/2021Private info29,600.00
3731/12/2021Private info29,600.00
3831/12/2021Private info221,553.30
3931/12/2021Private info238,803.30
4031/12/2021Private info110,776.65
4131/12/2021Private info26,922.00
4231/12/2021Private info8,949.00
4331/12/2021Private info35,953.00
4431/12/2021Private info2,100.00
4531/12/2021Private info11,000.00
4631/12/2021Private info11,000.00
4731/12/2021Private info24,691.00
4831/12/2021Private info24,691.00
4931/12/2021Private info21,210.00
5031/12/2021Private info46,297.00
5131/12/2021Private info1,659.00
5231/12/2021Private info12,008.00
5331/12/2021Private info9,033.00
5431/12/2021Private info14,023.00
5531/12/2021Private info10,251.00
5631/12/2021Private info11,055.00
5731/12/2021Private info7,101.00
5831/12/2021Private info5,116.00
5931/12/2021Private info24,385.00
6031/12/2021Private info27,594.00
6131/12/2021Private info11,367.00
6231/12/2021Private info50,000.00
6331/12/2021Private info6,860.00
6431/12/2021Private info12,324.00
6531/12/2021Private info32,567.00
6631/12/2021Private info5,100.00
6731/12/2021Private info5,670.00
6831/12/2021Private info19,690.00
6931/12/2021Private info1,300.00
7031/12/2021Private info30.00
7131/12/2021Private info150.00
7231/12/2021Private info39,478.00
7331/12/2021Private info30.00
7431/12/2021Private info150.00
7531/12/2021Private info21,414.00
7631/12/2021Private info19,756.00
7731/12/2021Private info30,315.00
7831/12/2021Private info13,204.00
7931/12/2021Private info1,870.00
8031/12/2021Private info100,000.00
8131/12/2021Private info104,000.00
8231/12/2021Private info350,000.00
8331/12/2021Private info150,000.00
8430/12/2021Private info600,000.00
8530/12/2021Private info327,457.00
8630/12/2021Private info4,300.00
8730/12/2021Private info11,093.00
8830/12/2021Private info352,994.20
8930/12/2021Private info40,000.00
9030/12/2021Private info269,153.30
9130/12/2021Private info3,800.00
9230/12/2021Private info85,900.00
9330/12/2021Private info22,401.00
9430/12/2021Private info25,805.00
9530/12/2021Private info75,876.00
9630/12/2021Private info38,240.00
9730/12/2021Private info8,841.00
9830/12/2021Private info11,011.00
9930/12/2021Private info6,000.00
10030/12/2021Private info12,000.00
10130/12/2021Private info20,000.00
10230/12/2021Private info6,209.00
10330/12/2021Private info10,000.00
10430/12/2021Private info9,800.00
10530/12/2021Private info15,000.00
10630/12/2021Private info43,000.00
10730/12/2021Private info45,810.00
10830/12/2021Private info11,500.00
10930/12/2021Private info2,500.00
11030/12/2021Private info52,197.00
11130/12/2021Private info20,000.00
11230/12/2021Private info29,000.00
11330/12/2021Private info70,000.00
11430/12/2021Private info10,000.00
11530/12/2021Private info50,000.00
11630/12/2021Private info50,000.00
11730/12/2021Private info120,000.00
11830/12/2021Private info15,423.00
11930/12/2021Private info15,423.00
12030/12/2021Private info10,000.00
12130/12/2021Private info10,000.00
12230/12/2021Private info30,000.00
12330/12/2021Private info30,000.00
12430/12/2021Private info2,500.00
12530/12/2021Private info3,800.00
12630/12/2021Private info7,441.00
12730/12/2021Private info2,600.00
12830/12/2021Private info20,876.00
12930/12/2021Private info50,000.00
13030/12/2021Private info31,854.00
13130/12/2021Private info49,002.00
13230/12/2021Private info16,290.00
13330/12/2021Private info49,479.00
13430/12/2021Private info10,500.00
13530/12/2021Private info15,163.00
13630/12/2021Private info13,285.00
13730/12/2021Private info27,522.00
13830/12/2021Private info3,800.00
13930/12/2021Private info5,101.00
14030/12/2021Private info7,656.00
14130/12/2021Private info3,800.00
14230/12/2021Private info25,870.00
14330/12/2021Private info3,800.00
14430/12/2021Private info2,067.00
14530/12/2021Private info7,113.00
14630/12/2021Private info5,771.00
14730/12/2021Private info29,678.00
14830/12/2021Private info12,903.00
14930/12/2021Private info13,182.00
15030/12/2021Private info15,411.00
15130/12/2021Private info23,447.00
15230/12/2021Private info17,600.00
15330/12/2021Private info42,744.00
15430/12/2021Private info6,205.00
15530/12/2021Private info36,697.00
15630/12/2021Private info70,000.00
15730/12/2021Private info100,000.00
15830/12/2021Private info50,000.00
15930/12/2021Private info150,000.00
16030/12/2021Private info350,000.00
16129/12/2021Private info3,899.00
16229/12/2021Private info8,870.00
16329/12/2021Private info300.00
16429/12/2021Private info2.50
16529/12/2021Private info60.00
16629/12/2021Private info200,000.00
16729/12/2021Private info44,000.00
16829/12/2021Private info43,449.00
16929/12/2021Private info25,000.00
17029/12/2021Private info13,000.00
17129/12/2021Private info17,000.00
17229/12/2021Private info12,290.00
17329/12/2021Private info62,126.00
17429/12/2021Private info17,000.00
17529/12/2021Private info46,214.00
17629/12/2021Private info50,000.00
17729/12/2021Private info10,000.00
17829/12/2021Private info50,000.00
17929/12/2021Private info20,000.00
18029/12/2021Private info23,000.00
18129/12/2021Private info50,000.00
18229/12/2021Private info13,000.00
18329/12/2021Private info17,000.00
18429/12/2021Private info15,711.00
18529/12/2021Private info18,000.00
18629/12/2021Private info4,072.00
18729/12/2021Private info32,541.00
18829/12/2021Private info50,000.00
18929/12/2021Private info55,863.00
19029/12/2021Private info2,800.00
19129/12/2021Private info45,321.00
19229/12/2021Private info91,696.00
19329/12/2021Private info21,865.00
19429/12/2021Private info34,652.00
19529/12/2021Private info15,121.00
19629/12/2021Private info11,907.00
19729/12/2021Private info17,452.00
19829/12/2021Private info6,199.00
19929/12/2021Private info22,876.00
20029/12/2021Private info15,517.00
dec2021nbk502


Any help with PQ, Macros, LET, λ is welcome, mine is Office 365 home version on Windows 10 platform.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Forum statistics

Threads
1,223,880
Messages
6,175,154
Members
452,615
Latest member
bogeys2birdies

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