Lookup and Sorting speed

[Dan Wilson]

Good day. I am running Excel out of Office365 (updated) on Windows 10 Home. I have a worksheet containing almost 5000 songs over the period of 1930 through 2000. I have a second worksheet containing data from the years 1950 through 1969 in the same format as the first worksheet. The first worksheet uses extensive formulas to extract data from the Field Properties of the song files located in my music library. The second worksheet was created by sorting the first worksheet and then copying the data from the first worksheet using the same cell formats. The second worksheet has several Macros that are used to sort the data by different Field Properties. As I am continually adding more music to the library and thus to the first worksheet, it would be easier if the second worksheet contained formulas in Columns B through H that would copy the data from the first worksheet using the song title in Column A. As it is now, I have to enter data in Columns B through H manually for each song added. I know how to create the formulas to extract the data from the first worksheet. My question is -- will the existing Macros in the second worksheet that are used to sort the data, still work if there are formulas creating the data in those Columns? Will the search process using the formulas prevent the Macros from sorting? Will the process take longer because of the formulas? Thank you for any help.

 

[dougmarkham]

Good day. I am running Excel out of Office365 (updated) on Windows 10 Home. I have a worksheet containing almost 5000 songs over the period of 1930 through 2000. I have a second worksheet containing data from the years 1950 through 1969 in the same format as the first worksheet. The first worksheet uses extensive formulas to extract data from the Field Properties of the song files located in my music library. The second worksheet was created by sorting the first worksheet and then copying the data from the first worksheet using the same cell formats. The second worksheet has several Macros that are used to sort the data by different Field Properties. As I am continually adding more music to the library and thus to the first worksheet, it would be easier if the second worksheet contained formulas in Columns B through H that would copy the data from the first worksheet using the song title in Column A. As it is now, I have to enter data in Columns B through H manually for each song added. I know how to create the formulas to extract the data from the first worksheet. My question is -- will the existing Macros in the second worksheet that are used to sort the data, still work if there are formulas creating the data in those Columns? Will the search process using the formulas prevent the Macros from sorting? Will the process take longer because of the formulas? Thank you for any help.

Hi Dan,

It's a bit hard to imagine your workbook (wb) exactly without seeing it. However, there are some generalisations I can share about Excel which might help:

1) Excel has a limited memory in the registry, and if you have too many formulas and too many lines, Excel could even crash. Not sure about your wb, but I've stressed out workbooks with 3/4 of a million rows and eventually realised there were better ways. Even using dynamic tables, there is a limit to what is possible or even desirable.

2) In general, it's always better to turn formulas into values, especially before applying filters. If you need to reapply a formula, you ClearContents on the Column and reapply the formula, then make the formula data into values again.

3) It's quicker to do calculations by taking the data into the VB editor but using a variant array ie, so that the calculations happen away from the ws cells. This is the next level of learning, but well worth it because it's so much faster.

4) You might benefit from Power Query and paramatized queries. Although I can't analyse your wb, my guess is that my solution would include a Power Query aspect.

5) In your situation, I'd probably use a userform to enter new data, unless you have another form of data that can be imported..