Generate List Of Combinations - 20 Questions With 3 Answers
Feb 26, 2011
How to generate a list of numbers of this function in Excel: COMBIN(20,3), but i want the combinations of numbers. I want to create a list of 20 questions and every question has 3 answer. I think that are 1140 combinations.
I have a list in excel with ten values, but I need to get all combinations possible, picking 4 of 10 each combination. This must be as a combination and no permutation or variations either, I mean, values can't be repeated in a single combination.
I'm struggling to do this in a Macro but I don't get it.
Is there an Excel Addin that will let me figure out the total number of combinations for any size number pool I specify (39,49, 53) and with any combination quantity I specify (3, 4, 5, 6)? And most important is that all these combinations be displayed in a column separated by commas.
Is a VBA script designed to handle something this complex.
I have two columns of data in a worksheet, and I need a third column that shows all of the possible combinations of the data in those two columns. For example:
A A AA
B B AB
[Code] .....
I don't need permutations so, for example, the third column should only give the combination "AB" and exclude "BA".
The aim is to find those combinations of variable values which generate highest total gain. I attached the spreadsheet which shows the variables (A through K) and a Gain column. I created 5 additional tabs which show all possible 2,3,4 and 5-member combinations of the variables. These tabs are like coordinates of which variable combinations should be examined. As an example I used the first combination from the second tab = A and B. If you look at these two columns on the EXAMPLE CALCULATION tab you will see 7,7 in the Number combination which is the first number pair for these two variables. The headings of the red and the yellow columns calculate the total count for this number pair and the total gain. These were recorded on a separate EXAMPLE RESULTS tab along with some other pairs which appear afterwards (these were recorded only from the first 39 rows of the AB data). I need a macro which will cycle through each variable pair (only using the combinations from the tab 2 for now, annd later from 3,4 and 5 tabs) collecting statistics for each unique number combination it encounters (printing to a separate sheet one after one), such as shown on the EXAMPLE RESULTS.
I have a large amount of data, 40,000+ rows about how students do in remedial classes including from whom they took the classes. I have pulled from it a subset of student success as relates to the separate instructors. Now I would like to extract a consolidated list of those instructors for whom the student gain on criterion based pre and post course assessments is above a specific level. I have no trouble writing if statements that will look at two types of data, the course taken and the average student increase to determine which instructors meet or surpass the criterion, but I can only do that within a list that includes all the instructors. Because there are more than an hundred such instructors, it would be easier for the dean if I could provide a short list of those that meet specific criteria. I believe that it would be an array that I should use, but I have been fooling around with this for 2 days now, and am finding myself at a series of deadends!
I have ten numbers (i.e. 1,2,3,4,5,6,7,8,9,10) and I'm trying to figure out how to make Excel generate a list of all the combinations possible for these 10 numbers. I'm trying to avoid using a VBA script. Is it possible to accomplish this task only using a function formula? This way I will have the ability to tweak the formula and test different qauntities of numbers (i.e. 1,2,3,4,5,6,7 or 44,56,77,33). Is getting a list of all possible combinations to output on a single column with commas between numbers possible with a formula?
I know the COMBIN function, i want to list all combinations from a given set of cells (numbers), each item of the combination - in separate cell. Example:
1 2 3 4 5 a b 6 ....
after combin (x,3) i want to list me this: 123........but each number must be in separate cell (1-one cell, 2 -one cell, 3 -one cell)
I am trying to generate a list based on the value of a cell. That list will then be used as the range for a drop-down list. Example: Cell A1 returns a value of 15 A drop-down list displays the values 1 through 15. Cell A1 changes to 20 Drop-down list displays the values 1 through 20. I assume I'll need a two-step macro to accomplish this but I can't figure out the logic to populate the drop-down.
I have a set of accounts (general ledger accounts) that the accounting group posts expenses to. every once in awhile a new account is added. This is captured through a pivot that i have built (sheet A).
on another sheet (sheetB) i want to display the accounts that are shown in the pivot so that i can forecast their future activity.
What i am trying to figure out is how can i make the list on Sheet B change when new accounts are added to the pivot on Sheet A (without simply referencing the pivot table making one cell equal the other)? I am not looking for a data validation pull down, i want a full list of the accounts.
I am trying to rank questions within a survey with excel calculations and not VBA. The rank would take place for each individual section and would provide the top three right questions and top three wrong questions. The example below explains the ranking.
Brief facts: Each survey has up to 30 sections.
Each section has up to 150 questions.
Each question has none or no limt of sub-questions.(usually between 1 and 20, weekly changes require this number to change)
The section's number of questions change weekly with updates.
This week section 2 begins with #12, next week begins with #16 because more questions were added to section 1.
There are three possible choices to answer a question (yes, no, or n/a.)
Sub-questions are checkboxes of explanations on why the question could be answered wrong.
Each question may have 0-10 points assigned.
Each sub-question may have 0-10 points assigned.
When a question is answered "no" is when a sub-question may be checked to help explain why.
A question with sub-questions may only be assigned points at the sub-question level. The sub-questions have the points and do not sum up to total the question value. When a question is answered "no" and a sub-question is checked, the points received are 0 points for that sub-question. Unchecked sub-questions receive the full amount of points.
When a question is answered "yes" all points are received.
The Rank:
The rank would take place for each individual section and would provide the top three right questions and top three wrong questions at the question level. My example below has more than three to help explain further.
Select the highest actual score of question or sub-question. Start with 10 points to 0 points.
When a match is found that becomes the first "correct" question rank. #17 is 1 since 17.1 had an 8 point sub-question.
When there is a tie #14,15,18, the tie breaker is the sum of correct sub-questions added to the question score. #14 = 7 points.
The second sort is the # in ascending order #15 and then 18.
The hardest part of the rank is the flexibility of a changing survey with question/sub-question deletions and additions in each section. ...
I am trying to write VBA code that will list all possible combinations of data contained in a worksheet based on a few conditions. I have a list that varies in length. In the first column is a list of numbers denoting a particular course number. In the second column is a list of numbers denoting the specific class number for the course. As an example, I have a 6 courses. The first course has 1 class, the second course has 3 classes, the third has 11, the fourth has 3, the fifth has 7, and the sixth has 6. Thus, my columns look like this:
A B 1 1 2 1 2 2 2 3 3 1 3 2 3 3 3 4 3 5 ...
I need to list out all possible combinations of classes given that I must have all six courses. EXAMPLE (Course, Class):.......................
I need to be able to create a list of all combinations (where position doesn't matter, therefore its not a permutation) of a list of numbers with repetition enabled. I need to provide anywhere up to at least 10-15 numbers if possible and they are integers normally between 1 and 72. Example: (for numbers 1, 2, 3)
Am looking for an easy way to produce a list of combinations (maximum 6 numbers) from a range of numbers listed in 6 different columns:
Example Column A contains : 1,2,3 Column B contains : 7,8,11,15 Column C contains : 12,16,18,19 Column D contains : 17,30,31 Column E contains : 30,31,32,33,34 Column F contains : 37,39,40 The rules are:
In each combination of 6 numbers, numbers should always be taken from ALL 6 columns. In each combination, numbers cannot repeat.
An example will be as follows. List all possible outcomes for 3 matches. That will be 27 possible outcomes.
I would like results for my request of 50 matches to be displayed as follows.
HHH HHD HHA HDH HDD HDA
[Code] ...........
Where: H=HOME D=DRAW A=AWAY
Is there a way i can have the possible outcomes listed as above for the outcomes of 50 football matches? I do know that the outcomes will be hundreds of millions if not billions.
I have a worksheet that is several thousand rows long. It is sectioned into various "topics" (e.g. Revenue, Profit); each topic (there are about 100) has 10 companies. The 10 companies are the same for each topic. For instance, if 2 of the companies are Microsoft and Intel, then there will be a Microsoft row and an Intel row within each topic.
I am trying to write some code to delete every instance of a company (the user indicates the company in a form). So, for instance, if I want to delete Microsoft, the user would enter the stock ticker, MSFT, into the form and press the appropriate command button. When the command button is pressed, the code should find each row that contains the ticker MSFT (the tickers happen to be in column B) and delete it.
I have written the following unsuccessful Private Sub CommandButton1_Click()
I have 7 cells containing strings but not numbers on a row.
Now I want to list out all the combinations of drawing out 3 cells out of these 7 cells while the remaining cells that haven't been drawn out could also be listed out one column next to the drawn cells.
For example, I got 7 cells like this.
A B C D E F G (each letter in ONE cell)
And I want to list out all the combinations like this:
ABC DEFG ABD CEFG ABE CDFG ABF CDEG
etc.
For more details, please refer to the attached sample (an .xls file being zipped).
I have a single rangle of numbers in Column A, and in Columns B and C and want to list all possible combinations of these numbers. If there is a possible solution without using a Macro I would perfer that, but if not I'll take what I can get.
I coordinate access requests for several contracts, and I have to list the approved accesses in a list where each line represents one person and one contract. For each access request, there will be an arbitrary number of persons obtaining access to an arbitrary number of contracts.
The input would then be as follows: Joe A and Jill B request access to contracts 1001, 1002 and 1003 ->
Joe A 1001 Joe A 1002 Joe A 1003 Jill B 1001 Jill B 1002 Jill B 1003
To automate this task, I have made a simple macro for generating a combination list of all persons having obtained access to a selection of contracts.
My macro worked well when I only wanted to list unique and independent list items, but now I have been asked to include each person's email address. How I can change my code so that only one email address is copied into my list for each person?
Code: Sub AccessList() Sheets("requests").Select Dim rng As Range, c As Range Dim rng1 As Range, c1 As Range Dim rng2 As Range, c2 As Range
I have created a custom floating toolbar that works perfect (thanks to all the reading on here!). My workbook is a stand alone estimating tool that many users will have a copy of.
I have two things I want to do yet but I cant find it on here.
1) My buttons on my toolbar - I want to have custom icons. I can do this temporarily but when I exit my worksheet and re-open they are lost. Is there anyway to save a custom icon. I am willing to add it to the icon list if necessary and then when users open the workbook copy the custom icons onto their computer. But I am not sure how/where I could do this.
Worse case, can I color my button text?
2) When my toolbar appears, it is wide, all buttons are going left to right. I want the size to be small width so the toolbar buttons are on top of each other. Any thoughts? I tried adding a .width but it didnt work.
I have an event that is one week long (7 days), with three functions happening each day (7 days - 3 columns per day).
There are 11 groups with various number of possible attendees listed in rows. Attendees of a given function is indicated by entry of a 1 or 2 (attending partner), Blank = not attending. I would like to show a list of the attendees for a given function by clicking or moving my mouse pointer on the event function column header.
The list would need to be automatically updated when the attendees status changes (entry or deletion of 1 or 2)
Find attached an example sheet. I have one list with data... Name, Handle, Result. I want to have three lists from that data
List 1 would have top ten highest results List 2 would have top ten lowest results
Some results will be the same so I would need each Name listed as they appear in the main list until ten is reached. List 3 would have top ten handle, but would not include anyone in list 1 or 2. This will be a shared workbook