Forum Discussion
Run Multiple sets of data through set formula's
Hello,
First off thank you for taking the time to look at this question though I would not be at all surprised if I am completely wrong about what I actually need. One of my colleagues and I have been working on a calculator of sorts for and we're able to get the result we wanted when you run one set of information through it at a time. However, what we want to be able to do is run multiple sets of data through it otherwise it is not the time saver we are looking for. Here are some screen shots:
This is not real data as I am not sure if that would get me in trouble and I made the layout basic just to illustrate what I am trying to do. The Orange boxes are the information you would enter, so the artice, site and so on. The blue information are the result we receive back from the formula's we have already entered obtained from a data sheet we have setup.
I think the tricky part is when you fill in the orange information, data is pulled from our other sheet which fills out this table:
That's what make it hard to do multiple at once as the table as far as I can figure out, can only be populated one-by one.
The goal would be able to upload any number of sets of those article, site, vendor and QTY's groups, and be able to simultaneously run them all to gather the needed information to vastly speed up this process.
If providing more details on any of the other formula's we have will help, please let me know and again, any advice would be appreciated.
2 Replies
Yes, you should not post confidential data, but your sample data and description are too incomplete and vague for proper formulas. The "run multiple sets of data through it" suggests that you will paste a rectangular, horizontal range of values to the right of the cells filled with "Orange" (I would call it khaki), but pasting a vertically-oriented range in a differently structured worksheet is also possible.
Many Excel functions can be used to spill formula results to additional, adjacent (down and/or to the right) cells. Play with the SEQUENCE function for an example of where you (presumably) know the number of cells spilled to when you write the formula. And the XLOOKUP function can take a linear range of cells or an array as its first argument (lookup_value), generating a result range of similar structure.
See the attached workbook for one set of possibilities.
How about this to streamline your process:
- Use Excel Tables and Structured References
If your data is stored in a structured table, formulas can automatically apply to multiple rows without needing manual input for each set. - Array Formulas or Dynamic Arrays
Excel's newer dynamic array functions (like FILTER, SORT, and UNIQUE) can help process multiple sets of data at once. If you're using an older version, array formulas (CTRL + SHIFT + ENTER) might be useful. - Power Query for Bulk Processing
Power Query allows you to import and transform multiple sets of data efficiently. You can set up a query that pulls in all the data, applies the necessary calculations, and outputs the results in one go. - VBA Automation
If your current setup requires manual entry, VBA can automate the process by looping through multiple sets of data and applying formulas dynamically. - Data Tables for Scenario Analysis
Excel’s Data Table feature can be used to run multiple scenarios at once, especially if your formulas depend on a few key input variables.
- Use Excel Tables and Structured References
