Better way to index

[allgeef]

I record expense reports monthly by totaling the sum for each employee. An indexing formula worked fine until we began to grow, and now the same formula takes a long time and locks up Excel for a period while returning results. Can you provide a better formula or method to obtain the same result? I uploaded a sample of what we're using now.

Regards,
Gary

[JBeaucaire]

In G2 entered as an array:

=INDEX(E$2:E$1000,MATCH(0,COUNTIF(E$2:E$1000,"<"&E$2:E$1000),0))


In G3 entered as an array, then copied down about 40-50 rows:

=IF(COUNTIF(E$2:E$1000,">"&G2), INDEX(E$2:E$1000, MATCH(COUNTIF(E$2:E$1000,"<="&G2), COUNTIF(E$2:E$1000,"<"&E$2:E$1000),0)),"")


I also fixed the other formulas...in H2 entered as a normal formula and copied down:

=IF(G2="","",SUMIF(E:E,G2,C:C))

In I2 and copied down:

=IF(G2="","",COUNTIF(E:E,G2))

[DonkeyOte]

Given the nature of your data and the fact you want to do this as efficiently as possible with formulae (rather than using a Pivot) I'd suggest:

G1: 
Account Name
[rather than name]

G2:
=IFERROR(IF(LOOKUP(REPT("Z",255),$E$1:$E$1000)=$G1,"",LOOKUP($G1,$E$1:$E$10000,$E$2:$E$10001)),"")
copied down as nec.

H2:
=IF($G2="","",SUMIF($E:$E,$G2,$C:$C))
copied down as nec.

I2:
=IF($G2="","",COUNTIF($E:$E,$G2))
copied down as nec.

The LOOKUP above assumes that your data is sorted by Account Name which would seem to be the case given sample - used in this form it is an extremely efficient formula (binary search)

Calculation time for your matrix using the above would be around 0.002 seconds.

[allgeef]

Jerry, this is precisely the answer to our problem. Thanks much for the quick response and taking time to make our spreadsheet functional. Regards, Gary

PS: your web page of Files & Macros looks like it could be a great aid for us.

[JBeaucaire]

Glad to help.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.