Macros to replace array formuas?

[sreed39]

A new question. I have another spreadsheet which is a conglomeration of data regarding truck trips. Unfortunately the design of the sheet is not conducive to getting data. I have been working on a new concept for this which would allow for sorting of different portions of the trips (basic trip data, fuel add data, issue data and fault code data), but these also span different tests. So at the top I created a section called 'Ongoing test summary). I want to be able to show the total truck data as well as data related to specific test plans (identified as RG##### where the pound symbols represent specific letters.)

The array formulas I use work perfectly, but the workbook could have anywhere from three to 30 different tabs (one for each truck) which then makes the entire workbook very slow and freezes up my system. Wondering if there is a macro or better formulas (and I don't know macros or VBA at all) that would resolve this dilemma.

Appreciate any help you can offer!

 

[Jerry Sullivan]

Yes, replacing array formulas with VBA code can often speed up the performance of a workbook.

There's many ways to speed up the performance of a workbook that don't necessarily involve VBA. If you aren't familiar with VBA, you might consider doing a web search (excel slow workbook) for alternative things to try first.

 

[sreed39]

I took most of those steps; but they were to no avail. I uploaded teh 64 bit version; turned calculation to manual. But the biggest suggestion is to avoid array formulas - which leads me to this loop again.

 

[Jerry Sullivan]

If you post a small example that illustrates a typical array formula from your workbook, I’ll try to suggest some VBA code to replace it.

 

[sreed39]

A brief intro to what I do; we run testing programs for vehicles. We have anywhere from 10-35 vehicles each with their own unique data. Within each of those, the vehicle might undergo multiple tests unique to that vehicle. So I may have vehicle ZZ1400 run 13,000 miles under RG13000 and another 18000 miles in RG14000. We put in all of the information about the trip in the rows. But there are also fault codes which are text not numbers; there are issue descriptions to which we assign a number as well as an occurrence count. I am looking for methods to be able to quickly calculate a 'running total' for the vehicle plus each unique test within that vehicle.

Here is an example of the Basic Values I need to compute. Some of them are pretty simple; like total miles, etc.

Total RG13000
Total Shifts Driven =SUM(--(FREQUENCY($E$15:$E$4802,$E$15:$E$4802)>0)) =COUNTIF($A$15:$A$4802,"RG13000")
Total Test Miles =SUM(E15:E4802) =$B$4-($B$3+$B$5)
Total Issue Count =SUM(--(FREQUENCY($L$15:$L$4802,$L$15:$L$4802)>0)) Would need to count when cell A references RG13000
Total Fault Codes =SUM(IF(FREQUENCY(IF(LEN($Y$15:$Y$4802)>0,MATCH($Y$15:$Y$4802,$Y$15:$Y$4802,0),""), IF(LEN($Y$15:$Y$4802)>0,MATCH($Y$15:$Y$4801,$Y$15:$Y$4802,0),""))>0,1))

This would also need to be calculated for each unique test identifier.









[TABLE="width: 145"]
<colgroup><col></colgroup><tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Jerry Sullivan]

Please provide a small example dataset (with 5-10 rows of fictional data showing just the columns referenced by these formulas).

Use a posting method that will allow me or another member to copy and paste the data into a workbook, such as those described here.
https://www.mrexcel.com/forum/about-board/508133-attachments.html