Help with something more effcient (CPU usage high)

[Grawill]

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:

=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]

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
	A	B	C	D	E	F	G
22	Account #	Street Number	Address	Job Type	2nd Code	3rd Code	Row
23	5009150270443550	10441	FALDO CT	nr	024	0	31
24	5009150270508420	10417	PIMBERTON DR	nr	024	0	32
25	5009150270522980	9655	PIMBERTON DR	nr	134	0	33
Sheet1

Cell Formulas
Range	Formula
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.

 

[Aladin Akyurek]

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:

=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:
[TABLE="width: 654"]
<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>[TR]
[TD="class: xl71, width: 124, bgcolor: transparent"]Account #[/TD]
[TD="class: xl71, width: 93, bgcolor: transparent"]Street Number[/TD]
[TD="class: xl71, width: 142, bgcolor: transparent"]Address[/TD]
[TD="class: xl71, width: 66, bgcolor: transparent"]Tech #[/TD]
[TD="class: xl71, width: 66, bgcolor: transparent"]Job Type[/TD]
[TD="class: xl71, width: 141, bgcolor: transparent"]prim code (170 or 171)[/TD]
[TD="class: xl71, width: 58, bgcolor: transparent"]1st Code[/TD]
[TD="class: xl71, width: 62, bgcolor: transparent"]2nd Code[/TD]
[TD="class: xl71, width: 60, bgcolor: transparent"]3rd Code[/TD]
[TD="class: xl71, width: 60, bgcolor: transparent"]4th Code[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 124, bgcolor: silver"]Acct Number[/TD]
[TD="class: xl68, width: 93, bgcolor: silver"]Street Number[/TD]
[TD="class: xl68, width: 142, bgcolor: silver"]Street Name[/TD]
[TD="class: xl69, width: 66, bgcolor: silver"]Tech #[/TD]
[TD="class: xl70, width: 66, bgcolor: silver"]JOB Type[/TD]
[TD="class: xl70, width: 141, bgcolor: silver"]prim code (170 or 171)[/TD]
[TD="class: xl70, width: 58, bgcolor: silver"]codes 1[/TD]
[TD="class: xl70, width: 62, bgcolor: silver"]codes 2[/TD]
[TD="class: xl70, width: 60, bgcolor: silver"]codes 3[/TD]
[TD="class: xl70, width: 60, bgcolor: silver"]codes 4[/TD]
[/TR]
</tbody>[/TABLE]

The 4427 sheet with the required processing in A:G...
[TABLE="width: 767"]
<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>[TR]
[TD="class: xl68, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 961, bgcolor: transparent, colspan: 6"]4227[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl71, width: 175, bgcolor: transparent"]Total NRs[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]NRs without 134s[/TD]
[TD="class: xl71, width: 134, bgcolor: transparent"]MR[/TD]
[TD="class: xl71, width: 134, bgcolor: transparent"]134s[/TD]
[TD="class: xl71, width: 134, bgcolor: transparent"]Special Project Time[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"]Jobs[/TD]
[TD="class: xl70, bgcolor: transparent"]10[/TD]
[TD="class: xl70, bgcolor: transparent"]8[/TD]
[TD="class: xl70, bgcolor: transparent"]2[/TD]
[TD="class: xl70, bgcolor: transparent"]2[/TD]
[TD="class: xl72, bgcolor: #D8E4BC"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"]Point Value [/TD]
[TD="class: xl70, bgcolor: transparent"]0[/TD]
[TD="class: xl70, bgcolor: transparent"]20[/TD]
[TD="class: xl70, bgcolor: transparent"]10[/TD]
[TD="class: xl70, bgcolor: transparent"]40[/TD]
[TD="class: xl70, bgcolor: transparent"]10[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"]Total Points[/TD]
[TD="class: xl70, bgcolor: transparent"]0[/TD]
[TD="class: xl70, bgcolor: transparent"]160[/TD]
[TD="class: xl70, bgcolor: transparent"]20[/TD]
[TD="class: xl70, bgcolor: transparent"]80[/TD]
[TD="class: xl70, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl73, bgcolor: transparent"] [/TD]
[TD="class: xl74, bgcolor: transparent"]Total Points:[/TD]
[TD="class: xl75, bgcolor: transparent"]260[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl73, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl73, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl73, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]Idx[/TD]
[TD="class: xl76, bgcolor: transparent"]Account #[/TD]
[TD="class: xl76, bgcolor: transparent"]Street Number[/TD]
[TD="class: xl76, bgcolor: transparent"]Address[/TD]
[TD="class: xl76, bgcolor: transparent"]Job Type[/TD]
[TD="class: xl76, bgcolor: transparent"]2nd Code[/TD]
[TD="class: xl77, bgcolor: transparent"]3rd Code[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]35[/TD]
[TD="class: xl70, bgcolor: transparent"]5009150270732370[/TD]
[TD="class: xl70, bgcolor: transparent"]9021[/TD]
[TD="class: xl70, bgcolor: transparent"]WILD COTTON CT[/TD]
[TD="class: xl70, bgcolor: transparent"]nr[/TD]
[TD="class: xl70, bgcolor: transparent"]024[/TD]
[TD="class: xl70, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]37[/TD]
[TD="class: xl70, bgcolor: transparent"]5009150270816930[/TD]
[TD="class: xl70, bgcolor: transparent"]7702[/TD]
[TD="class: xl70, bgcolor: transparent"]RIBBON FERN WAY[/TD]
[TD="class: xl70, bgcolor: transparent"]nr[/TD]
[TD="class: xl70, bgcolor: transparent"]024[/TD]
[TD="class: xl70, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]39[/TD]
[TD="class: xl70, bgcolor: transparent"]5009150270964640[/TD]
[TD="class: xl70, bgcolor: transparent"]7310[/TD]
[TD="class: xl70, bgcolor: transparent"]SKY FLOWER CT[/TD]
[TD="class: xl70, bgcolor: transparent"]nr[/TD]
[TD="class: xl70, bgcolor: transparent"]024[/TD]
[TD="class: xl70, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]41[/TD]
[TD="class: xl70, bgcolor: transparent"]5009150306881870[/TD]
[TD="class: xl70, bgcolor: transparent"]7130[/TD]
[TD="class: xl70, bgcolor: transparent"]CASTINE ST[/TD]
[TD="class: xl70, bgcolor: transparent"]nr[/TD]
[TD="class: xl70, bgcolor: transparent"]045[/TD]
[TD="class: xl70, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]42[/TD]
[TD="class: xl70, bgcolor: transparent"]5009150306882410[/TD]
[TD="class: xl70, bgcolor: transparent"]7116[/TD]
[TD="class: xl70, bgcolor: transparent"]CASTINE ST[/TD]
[TD="class: xl70, bgcolor: transparent"]mr[/TD]
[TD="class: xl70, bgcolor: transparent"]0[/TD]
[TD="class: xl70, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]48[/TD]
[TD="class: xl70, bgcolor: transparent"]5009150337760320[/TD]
[TD="class: xl70, bgcolor: transparent"]3746[/TD]
[TD="class: xl70, bgcolor: transparent"]SKY FLOWER CT[/TD]
[TD="class: xl70, bgcolor: transparent"]nr[/TD]
[TD="class: xl70, bgcolor: transparent"]134[/TD]
[TD="class: xl70, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

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:

=COUNTIF('Paste PM Data'!D3:D2000,B5)

A22: Idx

A23, control+shift+enter, not just enter, and copy down:

=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:

=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]

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]

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]

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.

 

[Aladin Akyurek]

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.