Help with something more effcient (CPU usage high)

Grawill

New Member
Joined
Nov 24, 2011
Messages
27
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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,403
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.

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">22</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">Account #</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">Street Number</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">Address</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">Job Type</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">2nd Code</td><td style="font-weight: bold;border-bottom: 1px solid black;;">3rd Code</td><td style="text-align: center;;">Row</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;border-top: 1px solid black;;">5009150270443550</td><td style="text-align: center;border-top: 1px solid black;;">10441</td><td style="text-align: center;border-top: 1px solid black;;">FALDO CT</td><td style="text-align: center;border-top: 1px solid black;;">nr</td><td style="text-align: center;border-top: 1px solid black;;">024</td><td style="text-align: center;border-top: 1px solid black;;">0</td><td style="text-align: center;;">31</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;;">5009150270508420</td><td style="text-align: center;;">10417</td><td style="text-align: center;;">PIMBERTON DR</td><td style="text-align: center;;">nr</td><td style="text-align: center;;">024</td><td style="text-align: center;;">0</td><td style="text-align: center;;">32</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;;">5009150270522980</td><td style="text-align: center;;">9655</td><td style="text-align: center;;">PIMBERTON DR</td><td style="text-align: center;;">nr</td><td style="text-align: center;;">134</td><td style="text-align: center;;">0</td><td style="text-align: center;;">33</td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A23</th><td style="text-align:left">=IF(<font color="Blue">$G23="","",INDEX(<font color="Red">'Paste PM Data'!A:A,$G23</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B23</th><td style="text-align:left">=IF(<font color="Blue">$G23="","",INDEX(<font color="Red">'Paste PM Data'!B:B,$G23</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C23</th><td style="text-align:left">=IF(<font color="Blue">$G23="","",INDEX(<font color="Red">'Paste PM Data'!C:C,$G23</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D23</th><td style="text-align:left">=IF(<font color="Blue">$G23="","",INDEX(<font color="Red">'Paste PM Data'!E:E,$G23</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E23</th><td style="text-align:left">=IF(<font color="Blue">$G23="","",INDEX(<font color="Red">'Paste PM Data'!H:H,$G23</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F23</th><td style="text-align:left">=IF(<font color="Blue">$G23="","",INDEX(<font color="Red">'Paste PM Data'!I:I,$G23</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G23</th><td style="text-align:left">{=IF(<font color="Blue">ISERROR(<font color="Red">SMALL(<font color="Green">IF(<font color="Purple">'Paste PM Data'!$D$1:$D$2000="4254",ROW(<font color="Teal">'Paste PM Data'!$A$1:$A$2000</font>)</font>),ROW(<font color="Purple">1:1</font>)</font>)</font>),"",SMALL(<font color="Red">IF(<font color="Green">'Paste PM Data'!$D$1:$D$2000="4254",ROW(<font color="Purple">'Paste PM Data'!$A$1:$A$2000</font>)</font>),ROW(<font color="Green">1:1</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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 NumberAddressTech #Job Typeprim code (170 or 171)1st Code2nd Code3rd Code4th Code
Acct NumberStreet NumberStreet NameTech #JOB Typeprim code (170 or 171)codes 1codes 2codes 3codes 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 NRsNRs without 134sMR134sSpecial Project Time
Jobs10822
Point Value 020104010
Total Points016020800
Total Points:260
6
IdxAccount #Street NumberAddressJob Type2nd Code3rd Code
3550091502707323709021WILD COTTON CTnr0240
3750091502708169307702RIBBON FERN WAYnr0240
3950091502709646407310SKY FLOWER CTnr0240
4150091503068818707130CASTINE STnr0450
4250091503068824107116CASTINE STmr00
4850091503377603203746SKY FLOWER CTnr1340

<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
Joined
Nov 24, 2011
Messages
27
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203

ADVERTISEMENT

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
Joined
Nov 24, 2011
Messages
27
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:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,128
Messages
5,640,282
Members
417,133
Latest member
caaronh85

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