In this post, I’ll show you how this can be done using power query.In this example I’ve set up two lists and created tables from them called For each table, we’re going to create a connection only query that adds a column with the same constant value in each row.
B3 calculates the number of possible combinations based on the parameters in cells B1 and B2 B4 provides a switch by doubleclicking to display the combinations as numbers or with the labels in range A7:A32. One other option that I can think of is writing the output to a text file in such a scenario. Stack Overflow for Teams is a private, secure spot for you and Stack Overflow works best with JavaScript enabled Or, how many combinations of two letters are possible from a total set of 5 letters. If cell B1 isn't empty and cell B2 gets a value the execution of macro M_snb will be triggered. If your data is small then you can get away without using arrays and directly writing to the cells. Tick Not sure why you are averse to looping. I hope to be able to be as clear as possible: I have 6 groups made of 3 columns. But I learned a part of Excel I hadn’t really been exposed to, so I’m still happy.
Moreover, if you decide to continue work on this file I would really like to see it handle an original pool size of up to 53 numbers. Since writing this post, I’ve found a slightly quicker method to do this. May 8th 2008 #1; Hey all, I was wondering if you have any VBA code that will allow me to enter in a number and it will find all the combinations of those digits starting from the number 1. each column can contain any number of values)It assumes all values in each column are unique (if this is not true, you will get duplicate rows)It assumes you want to cross-join output based on whatever cells you have currently selected (make sure you select them all)It assumes you want the output to start one column after the current selection.How it works (briefly): See this example. I also added the total number of combinations (product of the counts). your coworkers to find and share information.
You can see now why the "two header" layout in step 1 is important--it tricks the driver into understanding the data correctly.Microsoft Query opens, and by default the tables you added will be cross-joined. The odd and even in this case is only five possible outcomes : OzGrid Free Excel/VBA Help Forum.
thank you. By using our site, you acknowledge that you have read and understand our Beginner. I had to combine list 1 of all our customers (1,276 rows), with list 2 of all our products (28 rows) to get 35,728 rows of data that could be imported back into our system. ... Modifying Excel vba that creates all possible combinations from multiple lists. I only wish I would have scrolled all the way down to see your easier method, before going through the steps to do the merge query. Sometimes its 13, sometimes 6, sometimes 12. I am trying to generate all possible combinations and permutations possible from the set of numbers from 0 to 9 taken four at a time BUT display ONLY the one’s that match the conditions. 1.
ehavs79 ; May 8th 2008; ehavs79. I added counts on the top row for convenience. site design / logo © 2020 Stack Exchange Inc; user contributions licensed under Free 30 Day Trial Featured on Meta Excel General.
A Slightly Quicker Method. Thanks again!Awesome to hear that you explored something new (definitely keep exploring power query) and solved your problem.