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.
 

AlphaFrog

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

Forum statistics

Threads
1,081,834
Messages
5,361,594
Members
400,639
Latest member
fleyd

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top