optimisation

  1. A

    Help improve .DBF import speed

    I am working on a project to automate importing multiple .dbf files into a single excel sheet. The idea being I can import a group of .dbf files representing one dataset and the trend/graphs I need are automatically generated. The .dbf files always have the same data columns/header (38 columns)...
  2. F

    VBA optimisation - takes too long to process

    Hi guys! first time posting here. I'm french canadian so sorry about my poor english. So basically i wanna make like a little database on Excel with notes/jurisprudence/doctrine/laws and for every entry that i add on this sheet, i'm giving the notes/jurisprudence/doctrine/laws a tag on the...
  3. M

    Small optimization task in Excel (Solution in AMPL )

    Hello! I want to create this model in Excel to find the optimal solution with 2 constraints (Labor Hours and Material Available pr. week), below you will see the description and the solution in AMPL. For my own learning in other courses, I wish to make this in Excel using Solver. "Packing can...
  4. N

    How to find best possible selection through optimisation

    Hi experts, I am trying to identify the optimal solution on the below problem. Have tried using solver but without luck given the amount of variables. Thus, I hope someone can guide me on how to solve this with VBA or another solution. Situation: I have - 200 balls in 6 different colours -...
  5. 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...
  6. 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...
  7. 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...
  8. 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...
  9. 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...
  10. 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...
  11. 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...
  12. 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...
  13. 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...
  14. 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...
  15. 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) & "...
  16. 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...
  17. 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...
  18. 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...
  19. 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...
  20. 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...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

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