Array or Macro to calculate based on three criteria

[kcaenj]

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

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

 

[just_jon]

Book2
	A	B	C	D	E
1	EMPNO	BILL_AMT	STATUS	1	Total
2	1896	204	B	1	204
3	1899	354.49	B
4	1899	310	B
5	1899	17.63	X
6	1899	16.98	B	4	699.1
7	1915	59.17	B	1	59.17
8	1948	464.2	X
9	1948	321.7	X	2	785.9
10	1949	261.03	B	1	261.03
11	1965	21.18	X	1	21.18
12	2017	62	B	1	62
13	2064	202	B
14	2064	38.53	B	2	240.53
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	12	8323.18
Sheet2

Not sure if this is it; not sure how sloooow it's gonna get on ~50k rows, either...

EDIT - Nah, this just does changes in A. Have to concatenate A/C and work off that.

 

[just_jon]

Book2
	A	B	C	D	E	F
1	EMPNO	BILL_AMT	STATUS	1	Total	concat
2	1896	204	B	1	204	1896B
3	1899	354.49	B			1899B
4	1899	310	B	2	664.49	1899B
5	1899	17.63	X	1	17.63	1899X
6	1899	16.98	B	1	16.98	1899B
7	1915	59.17	B	1	59.17	1915B
8	1948	464.2	X			1948X
9	1948	321.7	X	2	785.9	1948X
10	1949	261.03	B	1	261.03	1949B
11	1965	21.18	X	1	21.18	1965X
12	2017	62	B	1	62	2017B
13	2064	202	B			2064B
14	2064	38.53	B	2	240.53	2064B
15	2066	1687.22	B			2066B
16	2066	939.4	B			2066B
17	2066	863.98	B			2066B
18	2066	756.52	B			2066B
19	2066	722.97	B			2066B
20	2066	671.78	B			2066B
Sheet2

Any better?

 

[kcaenj]

=IF(A2<>A3,SUMPRODUCT(--($A$2:A2=A2),--($D$2:D2=D2),$C$2:C2),IF(D2<>D3,SUMPRODUCT(--($A$2:A2=A2),--($D$2:D2=D2),$C$2:C2),0))

I used this formula in Column E for the total. It worked fine but made the worksheet function really slow. I will try your solution and see if it runs any faster.

Adam

 

[kcaenj]

Is there away to take the formula for the message above and make a macro that would paste this forumla into each cell in row E, but as you progress down it would adjust the amount of rows it has to search through since it becomes very slow as it gets down past 10,000 rows.

Make the range adjust to accomdate the size of the file.

Any info would be helpful,

KC

 

[just_jon]

Yes, you can do that; but I'm out of time at the moment but perhapssomeone else will pick you up.

 

[kcaenj]

CAn someone suggest a way to make this formula function faster? As it gets past 10,000 rows it starts to slow down a lot. i need the formula to adjust as it gets down the file as the numbers are sorted and there will never be repeats that are 10,000 rows apart. ANy suggestions would be appreciated.

Thanks,
KC

 

[RobShaw]

you could make match the current value with the rest of the list instead of all the list, so at row 10 say, it will check row 11 - end or somethign like that, but would that catch all the matches you would need?