# Help with something more effcient (CPU usage high)

#### Grawill

##### New Member
I have this performing the tasks needed, however when ever data is changed or the file is saved, Excel takes 30-60 seconds to complete the task.

I'm sure there is a more effcient way of performing the same task, but I'm at a loss. I've linked to an example with 50 rows of fake data to illistrate the delay. I expect this form to have up to 2000 rows of data at times, so the delay may become a big issue.

http://dl.dropbox.com/u/102274702/help with delay.xlsx

What I've done:
Paste PM Data tab - data copied to this workbook
42** tabs - starting at A23, each tab searches for it's 42** number in 'Paste PM Data'D:D and copies all rows that contain the same 42** number.

The code used to perform this search is as follows:
Code:
``=IF(ISERROR(INDEX('Paste PM Data'!\$A\$1:\$K\$2000,SMALL(IF('Paste PM Data'!\$D\$1:\$D\$2000="4227",ROW('Paste PM Data'!\$A\$1:\$A\$2000)),ROW(1:1)),1)),"",INDEX('Paste PM Data'!\$A\$1:\$K\$2000,SMALL(IF('Paste PM Data'!\$D\$1:\$D\$2000="4227",ROW('Paste PM Data'!\$A\$1:\$A\$2000)),ROW(1:1)),1))``

Any help or suggestions are greatly appreciated.
Ted.

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### AlphaFrog

##### MrExcel MVP
Instead of using your formula for each column, you could use a similar formula to return just the row number in a separate column (e.g. column G in this example). Then use an Index formula to return the values from that row for each column.

<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

##### MrExcel MVP
I have this performing the tasks needed, however when ever data is changed or the file is saved, Excel takes 30-60 seconds to complete the task.

I'm sure there is a more effcient way of performing the same task, but I'm at a loss. I've linked to an example with 50 rows of fake data to illistrate the delay. I expect this form to have up to 2000 rows of data at times, so the delay may become a big issue.

http://dl.dropbox.com/u/102274702/help with delay.xlsx

What I've done:
Paste PM Data tab - data copied to this workbook
42** tabs - starting at A23, each tab searches for it's 42** number in 'Paste PM Data'D:D and copies all rows that contain the same 42** number.

The code used to perform this search is as follows:
Code:
``=IF(ISERROR(INDEX('Paste PM Data'!\$A\$1:\$K\$2000,SMALL(IF('Paste PM Data'!\$D\$1:\$D\$2000="4227",ROW('Paste PM Data'!\$A\$1:\$A\$2000)),ROW(1:1)),1)),"",INDEX('Paste PM Data'!\$A\$1:\$K\$2000,SMALL(IF('Paste PM Data'!\$D\$1:\$D\$2000="4227",ROW('Paste PM Data'!\$A\$1:\$A\$2000)),ROW(1:1)),1))``

Any help or suggestions are greatly appreciated.
Ted.

1. The formula you have is costly (also recognized in the reply by AlphaFrog) and not robust.

2. Tech # in Paste PM Data consists of text numbers, not of true numbers, probably intendedly, decided at the source.

3. The headers in Paste PM Data do not align with those in the sheets where the processing is done.

What follows robustifies the set up and is faster (efficient).

Add a new header row to Paste PM Data for the current one might be due to the source the data comes from like below:
 Account # Street Number Address Tech # Job Type prim code (170 or 171) 1st Code 2nd Code 3rd Code 4th Code Acct Number Street Number Street Name Tech # JOB Type prim code (170 or 171) codes 1 codes 2 codes 3 codes 4

<colgroup><col style="width: 93pt; mso-width-source: userset; mso-width-alt: 4408;" width="124"> <col style="width: 70pt; mso-width-source: userset; mso-width-alt: 3299;" width="93"> <col style="width: 106pt; mso-width-source: userset; mso-width-alt: 5034;" width="142"> <col style="width: 49pt;" span="2" width="66"> <col style="width: 106pt; mso-width-source: userset; mso-width-alt: 5006;" width="141"> <col style="width: 44pt; mso-width-source: userset; mso-width-alt: 2076;" width="58"> <col style="width: 47pt; mso-width-source: userset; mso-width-alt: 2218;" width="62"> <col style="width: 45pt; mso-width-source: userset; mso-width-alt: 2133;" span="2" width="60"> <tbody>
</tbody>

The 4427 sheet with the required processing in A:G...
 4227 Total NRs NRs without 134s MR 134s Special Project Time Jobs 10 8 2 2 Point Value 0 20 10 40 10 Total Points 0 160 20 80 0 Total Points: 260 6 Idx Account # Street Number Address Job Type 2nd Code 3rd Code 35 5009150270732370 9021 WILD COTTON CT nr 024 0 37 5009150270816930 7702 RIBBON FERN WAY nr 024 0 39 5009150270964640 7310 SKY FLOWER CT nr 024 0 41 5009150306881870 7130 CASTINE ST nr 045 0 42 5009150306882410 7116 CASTINE ST mr 0 0 48 5009150337760320 3746 SKY FLOWER CT nr 134 0

<colgroup><col style="width: 48pt;" width="64"> <col style="width: 178pt; mso-width-source: userset; mso-width-alt: 8419;" width="237"> <col style="width: 131pt; mso-width-source: userset; mso-width-alt: 6229;" width="175"> <col style="width: 110pt; mso-width-source: userset; mso-width-alt: 5233;" width="147"> <col style="width: 100pt; mso-width-source: userset; mso-width-alt: 4750;" span="3" width="134"> <tbody>
</tbody>

Note that 4427 in bold is in B5 and B5:G5 is a merged range.
The column with Idx is a new inserion.
As a closer look reveals, the value in B5 is a true number, not a text number.

A16, just enter:
Rich (BB code):
``=COUNTIF('Paste PM Data'!D3:D2000,B5)``

A22: Idx

A23, control+shift+enter, not just enter, and copy down:
Rich (BB code):
``````=IF(ROWS(\$A\$23:A23)<=\$A\$16,
SMALL(IF('Paste PM Data'!\$D\$3:\$D\$2000=\$B\$5&"",
ROW('Paste PM Data'!\$D\$3:\$D\$2000)-ROW('Paste PM Data'!\$D\$3)+1),
ROWS(\$A\$23:A23)),"")``````

Note the B5&"" usage, which makes its value a text number in accordance with Paste PM Data.

B23, just enter, copy across, and down:
Rich (BB code):
``````=IF(\$A23="","",INDEX('Paste PM Data'!\$A\$3:\$K\$2000,\$A23,
MATCH(B\$22,'Paste PM Data'!\$A\$1:\$K\$1,0)))``````

Try to adjust the other processing sheets in the same way.

See the wb as modified:

http://tinyurl.com/crjughc

#### Grawill

##### New Member
Thanks! I really appreciate the help. Both of you. I went to working on this tonight and noticed an error.
The 2nd code line is pulling data from 3rd code while others show up as zero and not the required value.

!Paste PM Data row 37 should match indexed row 35, however they do not match.

....5009150270732370 9021 WILD COTTON CT nr 045 024 <-- original data (unused columns removed)
35 5009150270732370 9021 WILD COTTON CT nr 024 0 <-- error

The 3rd column code (024) appears to be showing in the 2nd column and the 045 code is missing.
I've managed to learn how indexing rows and MATCH works, however I do not understand why it has the error. The column names match perfectly.

Thanks again.
Ted.

##### MrExcel MVP

Thanks! I really appreciate the help. Both of you. I went to working on this tonight and noticed an error.
The 2nd code line is pulling data from 3rd code while others show up as zero and not the required value.

!Paste PM Data row 37 should match indexed row 35, however they do not match.

....5009150270732370 9021 WILD COTTON CT nr 045 024 <-- original data (unused columns removed)
35 5009150270732370 9021 WILD COTTON CT nr 024 0 <-- error

The 3rd column code (024) appears to be showing in the 2nd column and the 045 code is missing.
I've managed to learn how indexing rows and MATCH works, however I do not understand why it has the error. The column names match perfectly.

Thanks again.
Ted.

Would you make the reworked wb available?

#### Grawill

##### New Member
I didn't provide a new WB because the error appears in your examples above. Something I noticed when trying to mesh your new formulas with my original workbook.

Thanks for the help.
Ted.

Last edited:

##### MrExcel MVP
Thanks! I really appreciate the help. Both of you. I went to working on this tonight and noticed an error.
The 2nd code line is pulling data from 3rd code while others show up as zero and not the required value.

!Paste PM Data row 37 should match indexed row 35, however they do not match.

....5009150270732370 9021 WILD COTTON CT nr 045 024 <-- original data (unused columns removed)
35 5009150270732370 9021 WILD COTTON CT nr 024 0 <-- error

The 3rd column code (024) appears to be showing in the 2nd column and the 045 code is missing.
I've managed to learn how indexing rows and MATCH works, however I do not understand why it has the error. The column names match perfectly.

Thanks again.
Ted.

I didn't provide a new WB because the error appears in your examples above. Something I noticed when trying to mesh your new formulas with my original workbook.

Thanks for the help.
Ted.

The set up I provided is a standard one. What you need to check is whether the formulas reference the correct ranges and whether the new headers I suggested are properly placed (correspond correctly with the intended data). If both done correctly, the results should be correct.

Replies
12
Views
372
Replies
2
Views
322
Replies
0
Views
749
Replies
0
Views
178
Replies
0
Views
469

1,129,837
Messages
5,638,643
Members
417,040
Latest member
EC1728

### 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?

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