Help with something more effcient (CPU usage high)

G

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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
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 />
Book1
ABCDEFG
22Account #Street NumberAddressJob Type2nd Code3rd CodeRow
23500915027044355010441FALDO CTnr024031
24500915027050842010417PIMBERTON DRnr024032
2550091502705229809655PIMBERTON DRnr134033
Sheet1
Cell Formulas
RangeFormula
A23=IF($G23="","",INDEX('Paste PM Data'!A:A,$G23))
B23=IF($G23="","",INDEX('Paste PM Data'!B:B,$G23))
C23=IF($G23="","",INDEX('Paste PM Data'!C:C,$G23))
D23=IF($G23="","",INDEX('Paste PM Data'!E:E,$G23))
E23=IF($G23="","",INDEX('Paste PM Data'!H:H,$G23))
F23=IF($G23="","",INDEX('Paste PM Data'!I:I,$G23))
G23{=IF(ISERROR(SMALL(IF('Paste PM Data'!$D$1:$D$2000="4254",ROW('Paste PM Data'!$A$1:$A$2000)),ROW(1:1))),"",SMALL(IF('Paste PM Data'!$D$1:$D$2000="4254",ROW('Paste PM Data'!$A$1:$A$2000)),ROW(1:1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Grawill said:
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.
Click to expand...

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
 
Upvote 0
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.
 
Upvote 0
Grawill said:
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.
Click to expand...

Would you make the reworked wb available?
 
Upvote 0
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:
Upvote 0
Grawill said:
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.
Click to expand...

Grawill said:
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.
Click to expand...


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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Help with an aging table which uses more than just age as criteria. Need a wildcard text solution too.
Replies
1
Views
318
alsacdb
A
skyh3ck
  • Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
271
skyh3ck
skyh3ck
P
  • Question
Quicker way to process more than one row using Index Match
2
Replies
12
Views
1K
Fluff
Fluff
E
  • Question
Alternative formula to a multiple Index used in a 50,000 row table
Replies
2
Views
629
Fluff
Fluff
C
  • Question
Ton of script in case anyone wants to review, maybe something useful to someone
Replies
0
Views
2K
cizzett
C

Forum statistics

Threads
1,214,943
Messages
6,122,369
Members
449,080
Latest member
Armadillos

Share this page

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
Back
Top