optimisation

  1. A

    Copy column from Sheet to another where value <>0

    Hello, I have two sheets: one with raw data, and one where I'd like to do some analysis. In Sheets("raw_data").Range("W:W") I have measured values. I would like to copy only the non 0 values to Sheets("analysis").Range("A:A") . The way I'm doing it now is: Sub copy_data() Dim lastrow As Long...
  2. A

    Get frequency of elements in Range

    Hello, I have a spreadsheet with error codes (column A) and timestamps (column B) . Every time there is an error, it is recorded and time stamped. I would like to write a macro that writes in another column (column D) the frequency of each error. I have filled column C with the distinct error...
  3. A

    Get different parts of timestamp without MID()

    Hello, I have a spreadsheet with a column containing timestamps (format dd/mm/yyyy hh:mm:ss). I am trying to select certain dates: within a certain month, within a certain year etc. The month I want to select within is selected in a combobox (format yyyy/mm). The way I'm doing it is comparing...
  4. M

    VBA for Optimal Combinations with Multiple Constraints

    Hi All, I am new to excel VBA - but I do understand after I read the code. I have the attached spreadsheet where I have sheet "Combinations" with all possible combinations for P1-P5 items. While the best combination is ranked based on Value, I want to create an output for 52 weeks in a year...
  5. I

    Using Excel solver (or other method) with a common objective in multiple cells by changing the same variables, subject to constraints

    Hi, I am struggling with quite a complex problem in which I think I may be able to solve or at least partially solve using the Excel solver tool. I am trying to set up a design of experiments analysis in which I have a number of parameters at two levels. I have one specific objective which I...
  6. P

    Why the lag before Excel calculates?

    Does anybody know the reason for the lag before calculation in a large workbook? And potentially how to resolve... Specifically, when I change a particular input it takes a minute to complete the calculation - during that minute I can look at the Processes tab of Task Manager in Windows and...
  7. F

    First time coding, want help optimizing a long macro

    Hi MrExcel members, I've been lurking the forums for a while for tips for writing my first macro, but I finally decided to make an account to ask for help optimizing my code. I feel that my code is pretty inefficient and, while the end goals are accomplished, I have to go through somewhat...
  8. F

    First time coding, want help optimizing a long macro

    Hi MrExcel members, I've been lurking the forums for a while for tips for writing my first macro, but I finally decided to make an account to ask for help optimizing my code. I feel that my code is pretty inefficient and, while the end goals are accomplished, I have to go through somewhat...
  9. D

    VBA Code to process large amounts of data quickly

    I have a large set of data, about 50,000 rows by 25 columns. I need to filter down the data into 10 groups based on different criteria. I have tried 2 methods, both are taking the better part of 2 hours to complete. I am hoping someone can think of a faster way to do this. For the first...
  10. M

    Speeding Up and Optimizing My Code

    I first want to apologize for dumping a lot of code into here, but I have been stuck on this issue for days. I was assigned to build a planning screen wherein I could pull corresponding information regarding Sales, Production, and Inventory. As a preface to all of this, this code works for me...
  11. J

    match formula in for loop OPTIMISATION

    Lastrow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row For i = 3 To Lastrow Sheets("sample").Range("AM1000000").End(xlUp).Offset(1, 0).Select Selection.FormulaArray = _ "=IF(ISNUMBER(MATCH(1," & Chr(10) & " (order!R2C15:R1000000C15=RC[-24])*" & Chr(10) & "...
  12. O

    Optimisation Routine to select optimal factors to yield maximum values in target range

    Hello I need help with an optimization routine<o:p></o:p> I have a 60 X 20 matrix of values which when combined with 20 factors generates a weighted average value for each row of 60 entries.<o:p></o:p> I have a target range (upper lower bound) for each row and would like to quickly develop an...
  13. V

    Selecting columns based off header

    I have got a very large dataset on which I need to perform some data analysis. There are roughly 100 variables but there are multiple iterations so in the end it is closer to 15,000 variables. I need a way to select just the variables with the same header in a new worksheet as that will make it...
  14. W

    Building the schedule for a speed date algorithm

    Hello everyone. The Problem ========== I am working on a speed date project. The "who meets whom" part is already solved, and the output of that is a full list with all the pairs that will be meeting that day. The actual speed dating day will consist of **N rounds**, and will have **P...
  15. G

    HowTo optimise use of .select and reduce the 10 min that it takes to collate 600 tables into 1

    Dear friends, I have the following issue with a macro that collates near 600 tables (5x13) and paste them in different sheets in order. In other words, the macro builds a DB with hundreds of tables distributed in several sheets. Because it needs to copy and paste so many cells the macro became...
  16. H

    Macro takes 8 minutes. HELP!!!

    Dear all I'm currently working in a giant budget spreadsheet with lots of data. I have a macro that i need to run to update my numbers. My issue is that it takes 8 min for it to finish which is quite frustrating. Does any of you have the skills and will to see my macro through and see if...
  17. D

    Need help for Optimization technique

    Hello all, I have a set of values which depict the length of steel rods. But manufacturer has default values of 40feet, 50feet and 60feet. I need to find how many 40', 50', 60' units shall come. The wastage should be minimum. I have done it manually, but have lot of data. wastage is about 6.9...
  18. N

    Solver using VBA on portfolio optimisation

    Hi, I am totally new to VBA and have been working on portfolio optimisation with mean-variance framework using solver with rolling window. I have managed to set up a simple linked model and excel and have been using Solver manually to compute the weights (minimising the variance as well as...
  19. J

    structured data export word to excel via vba - optimisation opinion

    Hello everyone, i would like your opinion on the following: I need to extract data from several word documents (about 80 pages each). These documents have somewhat of a standard formatting that enables the structured extraction. Example: word in upper case in document will go to column B...
  20. P

    Optimising using Excel

    Hi there, I've got two columns B & C. A B C D 140% 140% 78% 78% B corresponds to A and C corresponds to D. At the end of the day I want to have both B & C to...

Watch MrExcel Video

This Week's Hot Topics

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
Top