Excel worksheet becomes unresponsive at times

[jonathanwsmith]

A worksheet with a VLookup function runs extremely slowly when copying the formula down to new rows. I'm glad to put up the worksheet if anyone wishes to see it. Below is a screenshot.

Thank you greatly,

Jonathan Smith

All Cash and Wilshire All Cap Index_Jonathan Smith.PNG

[TMS]

Why don't you use IFERROR instead of IF(ISERROR(...

[jeffreybrown]

Hi Jonathan,

Your profile says you have 2010, so try IFERROR...

=IFERROR(VLOOKUP(C2,C2:D100,2,0),NA())

[TMS]

That said, if you want #N/A you might as well just use the VLOOKUP on its own and let it fail if there's no match. Most of your formula is redundant.

[jonathanwsmith]

Thank you all. You are keenly observant. I attempted removing ISERROR as jeffreybrown suggests. Apparently I missed something. Please see screenshot below. Thank you all. And thank you for noting my profile says 2010.

All Cash and Wilshire All Cap Index_with IFERROR_Jonathan_Smith.PNG

[finalazy]

Hmm why is there a repeat vlookup in the formula? Try removing the remaining formula starting from NA() onwards so it become like this
=IFERROR(VLOOKUP(D11969,$A$13:$B$2260,2,FALSE),"#N/A")

But like what TMS said, if you just want to show N/A when no result found, just use the vlookup on its own.

[TMS]

@jonathanwsmith: You're welcome. Thanks for the rep.

[TMS]

For information: https://support.office.com/en-gb/art...6-63f3e417f611

[TMS]

@finalazy: "#N/A" is not the same as NA(). NA() generates an error, whereas "#N/A" is a text string.

=IFERROR(VLOOKUP(D13,$A$13:$B$2141,2,FALSE),NA())

But the IFERROR is redundant, so just:

=VLOOKUP(D13,$A$13:$B$2141,2,FALSE)

[finalazy]

@finalazy: "#N/A" is not the same as NA(). NA() generates an error, whereas "#N/A" is a text string.

=IFERROR(VLOOKUP(D13,$A$13:$B$2141,2,FALSE),NA())

But the IFERROR is redundant, so just:

=VLOOKUP(D13,$A$13:$B$2141,2,FALSE)

Ah thanks for pointing that out. Will take note of it.

[TMS]

You're welcome.

[jonathanwsmith]

Fellow members,
thank you for trying to help me. I have tried to implement your thoughtful suggestions to no avail, due chiefly to my lack of understanding. If I am so permitted, may I upload a copy of the file I'm attempting to repair?

Kind regards,

Jonathan Smith

[jeffreybrown]

Yes, you can upload a workbook.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[jonathanwsmith]

Hi jeffreybrown,

I can't see to get the Manage Attachments window to open so I can select my file to upload.

Thanks,

Jonathan

[jeffreybrown]

Hi jeffreybrown,

I can't see to get the Manage Attachments window to open so I can select my file to upload.

Well maybe...

To attach a file to your post...

Note: be sure to desensitize the data

1. click “Go Advanced” (next to Post Quick Reply – bottom right),
2. scroll down until you see “Manage Attachments”, click that,
3. click “Browse”.
4. select your file(s)
5. click “Upload”
6. click “Close window”
7. click “Submit Reply”

[TMS]

Click on Reply, then observe the last line of the last post.

[jonathanwsmith]

TMS, Thank you. I'm fired.

jeffreybrown - Uploading file now. This is my cover note:

Hi jeffreybrown,

The “All Cash and Wilshire All Cap Index posted to Excel Forum.xlsx” workbook that I posted to the Forum has data in Columns A13:I11969, the data resides in two datasets.

Dataset #1 exists in Column A13:B2260. The series interval is weekly. The Federal Reserve releases updated data each Thursday, and I entered the data in Column A13:B2260. The dates in Column A are valid, but from Column B13:B316, I plugged in the value “0.01” because true monetary data was non-existent. The data in Column B316:B2260 is legitimate. As appears on Row 2260, the most recent Fed notification was on Thursday 2/8 for the week ending 1/31/2018; I manually entered the data in Column A Row 2260. The next update will be 2/15 for the week ending 2/7/2018 and I will make that entry on Row 2261.

Dataset #2 exists in Column D13:E11969. The series interval is daily. Prior to 11/30/1980, the series was available monthly, thus all the #N/As between month end values. The value in Column F, Wilshire Total Market Capitalization, is available each day but no historical file of this value is provided, thus I calculate historical values with a formula. I use today’s Wilshire Total Market Capitalization (which is available each day), the current day’s Wilshire 5000 Index and the previous day’s Wilshire 5000 Index. After entering today’s Wilshire Total Market Capitalization in F2260 (the most recent date for which I have data), I copy the Formula from F2259 to F13, thus arriving at my historically backfilled Wilshire Total Market Capitalization.

The Formula in Column G (graciously provided by a moderator on this forum) allows me to synchronize two series, one with a weekly interval and the other with a daily interval, so that I can plot the result in Columns D, F, G, H and I on a daily chart. The Formula in Column G shows the value in Column B if the date in Column A is the same Date in Column D.
Sometime in 2016, my Excel started running very slowly, taking hours to copy the Formula in Column G (which had to be revised each week, i.e., changing B2250 to B2260, and so on) to new Rows. I attempted to substitute IFERROR, as TMS pointed out and remove the formula redundancy, and then copy the same to the entire Column G. I got an Excel Unresponsive for 12 hours and finally killed the program. I was successful in copying the formula from G 11969 to G 11955, but even that took long, with my Excel Unresponsiveness. I'd like to be able to copy the formula into the entire active column G.

I appreciate greatly the assistance I get on this forum. I’d gladly remit a fee for your time or make a charitable contribution to a cause that matters to you. Please advise.

Thank you

Jonathan Smith

[jeffreybrown]

Hi jonathan,

Sorry, this attachment will not work for me. I know I don't have the fastest computer, but it took 5 - 10 mins just to open the workbook, and then when I touched a cell, locked up again, and again, and again.

At this point, you are going to have to provide us with a sheet that is manageable to at least diagnose a better formula.

Maybe cut it down from 12,000 rows to say 100. We don't necessarily have to see the whole monster to possibly figure out a better way to slay the monster.

[jonathanwsmith]

Thanks jeffreybrown, what you experienced is exactly what I experience. I'll try to cut it down to 100 rows and repost.

gratefully,

Jonathan Smith