Array or Macro to calculate based on three criteria

kcaenj

Board Regular
Joined
Oct 13, 2004
Messages
197
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
ABCDE
1EMPNOBILL_AMTSTATUSXBCOUNT$XBTOTAL
21896204B
31899354.49B
41899310B
5189917.63X
6189916.98B
7191559.17B
81948464.2X
91948321.7X
101949261.03B
11196521.18X
12201762B
132064202B
14206438.53B
1520661687.22B
162066939.4B
172066863.98B
182066756.52B
192066722.97B
202066671.78B
212066620B
222066490.38B
232066409.57X
242066387.12X
252066387.12X
262066387.12X
Final Data
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Book2
ABCDE
1EMPNOBILL_AMTSTATUS1Total
21896204B1204
31899354.49B  
41899310B  
5189917.63X  
6189916.98B4699.1
7191559.17B159.17
81948464.2X  
91948321.7X2785.9
101949261.03B1261.03
11196521.18X121.18
12201762B162
132064202B  
14206438.53B2240.53
1520661687.22B  
162066939.4B  
172066863.98B  
182066756.52B  
192066722.97B  
202066671.78B  
212066620B  
222066490.38B  
232066409.57X  
242066387.12X  
252066387.12X  
262066387.12X128323.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

Legend
Joined
Sep 3, 2002
Messages
10,473

kcaenj

Board Regular
Joined
Oct 13, 2004
Messages
197
=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

Board Regular
Joined
Oct 13, 2004
Messages
197

ADVERTISEMENT

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

Legend
Joined
Sep 3, 2002
Messages
10,473
Yes, you can do that; but I'm out of time at the moment but perhapssomeone else will pick you up.
 

kcaenj

Board Regular
Joined
Oct 13, 2004
Messages
197
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

Board Regular
Joined
Dec 21, 2004
Messages
76
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?
 

Forum statistics

Threads
1,148,530
Messages
5,747,234
Members
424,070
Latest member
smanni3

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
Top