I have a spreadsheet containing account numbers in Col F and value in column H
I would like VBA to extract the account numbers from Audit Trail Sample (Source Workbook) and to copy these in the respoective sheets as follows in the destinatination workbook
A) sheet 2215B
1) Where 2215B appears in Col F, I want the number above 2215B including 2215B to be extracted as well as the values two columns to the right of it, excluding the following numbers in Col F 6900, 1102, 2310, 2311, 2316, 2200 and the values one column to the right i.e col H
2) Once the numbers and values have been extracted to sheet 2215B, I want the values the are directly above 2215B added and multiplied by 0.14 to ensure that this agrees with the value two columns to the right of 2215B i.e in Col H-I have manually extracted some data to show you what is required
3) Where 2218B appears in Col F, this account and the accounts and values two columns to the right and directly above it are to be ignored
4) Where 2215S appears one row above 2215B , then this can be ignored i.e 2215B and 2215S directly above it not to be extracted
B) Sheet 2215S
Where 2215S appears in Col F, I want the number above 2215S including 2215S to be extracted as well as the values two columns to the right of it, excluding the following numbers in Col F 6900, 1102, 2310, 2311, 2316, 2200
2) Once the numbers and values have been extracted to sheet 2215B, I want the values that are directly above 2215S added and multiplied by 0.14 to ensure that this agrees with the value in 2215B-I have manually extracted some data to show you what is required
3) Where 2218S appears in Col F, this account and the accounts and values two columns to the right and directly above to be ignored
See Aduit trail extraction sample of what I extracted manually to give you an idea of what is required
Your assistance in this regard is most appreciated
http://windowssecrets.com/forums/sh...tracting-numbers-and-data?p=858239#post858239
I would like VBA to extract the account numbers from Audit Trail Sample (Source Workbook) and to copy these in the respoective sheets as follows in the destinatination workbook
A) sheet 2215B
1) Where 2215B appears in Col F, I want the number above 2215B including 2215B to be extracted as well as the values two columns to the right of it, excluding the following numbers in Col F 6900, 1102, 2310, 2311, 2316, 2200 and the values one column to the right i.e col H
2) Once the numbers and values have been extracted to sheet 2215B, I want the values the are directly above 2215B added and multiplied by 0.14 to ensure that this agrees with the value two columns to the right of 2215B i.e in Col H-I have manually extracted some data to show you what is required
3) Where 2218B appears in Col F, this account and the accounts and values two columns to the right and directly above it are to be ignored
4) Where 2215S appears one row above 2215B , then this can be ignored i.e 2215B and 2215S directly above it not to be extracted
B) Sheet 2215S
Where 2215S appears in Col F, I want the number above 2215S including 2215S to be extracted as well as the values two columns to the right of it, excluding the following numbers in Col F 6900, 1102, 2310, 2311, 2316, 2200
2) Once the numbers and values have been extracted to sheet 2215B, I want the values that are directly above 2215S added and multiplied by 0.14 to ensure that this agrees with the value in 2215B-I have manually extracted some data to show you what is required
3) Where 2218S appears in Col F, this account and the accounts and values two columns to the right and directly above to be ignored
See Aduit trail extraction sample of what I extracted manually to give you an idea of what is required
Your assistance in this regard is most appreciated
http://windowssecrets.com/forums/sh...tracting-numbers-and-data?p=858239#post858239
Audit Trail Sample.xls | ||||||
---|---|---|---|---|---|---|
F | G | H | I | |||
1 | 6900 | 24775.69 | ||||
2 | 1102 | -24775.7 | ||||
3 | 2244 | 8864.95 | 1908.922 | |||
4 | 1102 | -10106 | ||||
5 | 7958 | 2689.75 | ||||
6 | 1102 | -3066.32 | ||||
7 | 7958 | 680.68 | ||||
8 | 1102 | -775.98 | ||||
9 | 7940 | 474.14 | ||||
10 | 1102 | -540.52 | ||||
11 | 7940 | 462.82 | ||||
12 | 1102 | -527.62 | ||||
13 | 7940 | 462.82 | ||||
14 | 1102 | -527.62 | ||||
15 | 2200 | 37.57 | ||||
16 | 1102 | -37.57 | ||||
17 | 2215B | 1908.92 | ||||
18 | ||||||
19 | 2200 | 25640.64 | ||||
20 | 2311 | -25640.6 | ||||
21 | 2200 | 657.45 | ||||
22 | 6913 | -576.71 | ||||
23 | 2215S | -80.74 | ||||
Sheet1 |
Audit Trail Sample.xls | ||||||
---|---|---|---|---|---|---|
F | G | H | I | |||
45 | 1113M | -1726 | ||||
46 | 1111F | 662 | ||||
47 | 1113F | -662 | ||||
48 | 1112 | 2615 | ||||
49 | 1111F | -2615 | ||||
50 | 1113F | 672 | ||||
51 | 1112 | -672 | ||||
52 | 1113M | 694 | ||||
53 | 1111M | -694 | ||||
54 | 1112 | 500 | ||||
55 | 1113F | -500 | ||||
56 | 1113F | 570 | ||||
57 | 1111F | -570 | ||||
58 | 2218B | 0 | ||||
59 | 2218S | 0 | ||||
60 | ||||||
61 | 6930 | 88000 | ||||
62 | 2215B | 12320 | ||||
63 | 2311 | -100320 | ||||
Sheet1 |
Audit trail Extraction.Sample.xls | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | 2244 | 8864.95 | ||||
2 | 7958 | 2689.75 | ||||
3 | 7958 | 680.68 | ||||
4 | 7940 | 474.14 | ||||
5 | 7940 | 462.82 | ||||
6 | 7940 | 462.82 | ||||
7 | ||||||
8 | 2215B | 1908.92 | ||||
9 | ||||||
10 | Check | 1908.92 | ||||
11 | ||||||
12 | Variance | 0 | ||||
2215B |
Last edited: