I want to be able to calculate in the XB Count and the XB total columns everytime that a new employee number begins or any time that a new Status begins. On the row where this occurs I want to count how many B's there were in the XBCount column (also count Xs as well) and also total all of the amounts in the Bill_AMT Cell in the row where the last X or B is. I hope this makes sense. If this can be done with a formula that would be great. See the portion of the file below. it is over 50,000 lines of data.
If possible I would like the macro at the end to only keep the rows where there is an amount in the XB amount column.
Thank you,
KC
If possible I would like the macro at the end to only keep the rows where there is an amount in the XB amount column.
Thank you,
KC
2004 non applied AMEX charges July 1 October 13.xls | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | EMPNO | BILL_AMT | STATUS | XBCOUNT | $XBTOTAL | ||
2 | 1896 | 204 | B | ||||
3 | 1899 | 354.49 | B | ||||
4 | 1899 | 310 | B | ||||
5 | 1899 | 17.63 | X | ||||
6 | 1899 | 16.98 | B | ||||
7 | 1915 | 59.17 | B | ||||
8 | 1948 | 464.2 | X | ||||
9 | 1948 | 321.7 | X | ||||
10 | 1949 | 261.03 | B | ||||
11 | 1965 | 21.18 | X | ||||
12 | 2017 | 62 | B | ||||
13 | 2064 | 202 | B | ||||
14 | 2064 | 38.53 | B | ||||
15 | 2066 | 1687.22 | B | ||||
16 | 2066 | 939.4 | B | ||||
17 | 2066 | 863.98 | B | ||||
18 | 2066 | 756.52 | B | ||||
19 | 2066 | 722.97 | B | ||||
20 | 2066 | 671.78 | B | ||||
21 | 2066 | 620 | B | ||||
22 | 2066 | 490.38 | B | ||||
23 | 2066 | 409.57 | X | ||||
24 | 2066 | 387.12 | X | ||||
25 | 2066 | 387.12 | X | ||||
26 | 2066 | 387.12 | X | ||||
Final Data |