I have a code that generate some combinations for a specific sum from a given data set. This code has two errors.
1. code does not look into data. it generates
2. Code generate those combinations for which sum is greater than target value.
E.g. first combination code generates is 3+7+10+12+12
This wrong by two means sum is
1) 44 while we need 40
2) 12 is not in data.
I have attached an example. If I have a set of numbers such as the one attached, is it possible to create a formula that will show me all the combinations of numbers that add up to 55.52? In the attached I have highlighted in different colours all the number combinations that add up to 55.52. The numbers highlighted in blue appear within more then one combination. Is there a formula that can do this for me, instead of randomly adding numbers hoping they add up to 55.52.
View 1 Replies View RelatedI have attached an example. If I have a set of numbers such as the one attached, is it possible to create a formula that will show me all the combinations of numbers that add up to 55.52? In the attached I have highlighted in different colours all the number combinations that add up to 55.52. The numbers highlighted in blue appear within more then one combination. Is there a formula that can do this for me, instead of randomly adding numbers hoping they add up to 55.52.
examples.xlsx
I'm trying to generate a dataset to use as an example in a class I'm helping out with.
I want 75 cases (that is, 75 rows)
Column A
randomly pick 1 or 2
Column B
60% 1 and 40% 2 randomly assigned to cases 1-75 (Thus, 45 1's and 30 2's randomly assigned to rows 1-75)
Column C
Randomly pick from the range of numbers 18-65. Make sure the mean for the column is 48.72.
Column D
Randomly pick from the range of numbers 1-7. Make sure the mean for the column is 5.49.
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".
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.
View 9 Replies View RelatedHow can I generate all the combinations a 3 digit number produces? Example 123 can be written as the following:
123
321
213
312
132
231
I THINK this is all the combinations it will produce.
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.
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.
View 9 Replies View RelatedWe collect loan payments for 36 months from customers.
Column A lists 1000+ customers.
Column J lists the date we received payment 1 ... Column Q lists the amount we received on payment 1.
Column R lists the date we received payment 2 ... Column Y lists the amount we received on payment 2.
Column Z lists the date we received payment 3 ... Column AG lists the amount we received on payment 3.
This repeats for all 36 payments.
New customers are loaded in each month, so be aware that Column J, Column R, Column Z (and so on) have dates from 2011 and 2012 and 2013.
We'd like to create a list of all customers that have not made a payment for the current month as of a certain day (say the 12th). So this month, on January 12th, we'd like to search our data for all customers that don't have a payment listed between January 1st - January 12th.
I attached partial file so you can see what i mean. I pasted only a few combinations cause the file was to big and i wasnt able to attach it...there are 142506 combinations . But you can see what i mean. A VBA or a macro on the worksheet will do the job?
View 9 Replies View RelatedIf a user inputs "Dispatch" into any cell on column G, I need to generate a Msgbox pop up.
View 3 Replies View RelatedAny way/method to make a certain number of cells randomly generate to a specific value you want? I only want to use the number from 1 to 60.
example:
1 37
2 29
3 34
4 2
5 58
6 38
7 =198
I am trying to generate a folder within a specific location (For now lets call it C:Jobs) that will be named as per the adjacent cell (Column H on attached)
Obviously if the folder already exists I want the code to stop.
But this code will have to generate a different folder for each row within the spreadsheet.
I have a spreadsheet that keeps track of my travel. Column A has the date I arrived somewhere, and Column B has the date I departed, and Column C has the name of the city I went to.
I am wondering if there is a way to generate a calendar using my list that will mark those dates. For example, a calendar for the month of June 2008 that would show I was traveling from June 3 to June 14, either by marking those dates with a different color or labeling them with the city names, or even just putting an x in the box.
I have 25 random numbers and I would like to get a possible 5 digit combinations of these numbers. Can anybody help me with the possible formula?
View 9 Replies View Relatedi want to generate sheet automatically from existing sheet applying auto filter on specific column. i attached the sample which 3000 rows actually i have more than one lac rows so i have to put filter on sub_div column and then copy and paste to another sheet and give the name of sheet like F21. i want to do automatically this provide vba code or function for this
View 3 Replies View RelatedWhat I am looking for is to select between 7 and 15 numbers in total, I want all the possible 6 digit combinations for this.
EG: if I choose 2,9,11,13,15,17&26, it would look something like this
2,9,11,13,15,17
2,9,11,13,15,26
9,11,13,15,17,26
And so on.
If I chose more numbers (10) 1,2,3,4,3,6,7,8,9,10 it would start something like this
1,2,3,4,5,6
1,2,3,4,5,7
1,2,3,4,5,8
1,2,3,4,5,9
1,2,3,4,5,10
And so on.
Please remenber I would like to be able to secelt between 7 and 15 number and be given all the possible combinations.
I would like it to be in one sheet but if that can not be done on as many as it takes.
It would be good if I could just type the required number into A1,B1,C1 and so on and they just gave the combinations required.
I am having trouble finding an efficient way to expand a set of data that I have by adding another column to it.
Look at attachment : help.xlsx
I can do it manually but I have 5000+ rows of data that I need to selectively expand do accommodate the data from the new data set.
How to create a sheet to generate Random numbers from 2 specific list of a main list range of 36 Numbers. (List A = Specific 15 numbers, List B = The remaining Numbers from these 36)
Example :
-The Main List Range is from Number 01 to 36
-The Specific random list will be 15 Numbers of these 36
List A-. The Specific 15 Numbers are: 01,02,03,10,11,12,13,20,21,22,23,30,31,32,33
List B-. Remaining Numbers : 04,05,06,07,08,09,14,15,16,17,18,19,24,25,26,27,28,29,34,35,36
So
I need to generate ONE Unique Random Numbers of List A ( 01,02,03,10,11,12,13,20,21,22,23,30,31,32,33)
In Cell A1
In Cell A2
In Cell A3
And from List B, (04,05,06,07,08,09,14,15,16,17,18,19,24,25,26,27,28,29,34,35,36)
I need to do the same thing In
Cell A4
Cell A5
Cell A6
It's possible to have a 6 cells with random but Unique Digits? (not repeated numbers between the 6 cell ??)
Trying to add a vlookup to a data set and cannot remember how to do.....
I have a table with State and Suburbs. I want to look at both these within another sheet to ensure the post code is correct......
In the attached I want to show in Sheet1 Column D the post code from Sheet2. As there may be suburbs with the same name within Sheet1 I need to ensure the correct Postcode for the suburb and the state.
(Checking manually entered data....)
Exceltest.xlsx
I have a grid that I need to lookup and return the project hours for each system from the data set. The grid has the projects listed in the rows and the systems listed across the columns. The data set has a list of each projects systems hours. I am having problems with the formula when the data set has multiple records (multiple systems) for each project. It seems very simple, but I just can't get my arms around it. Please see the attached for example.
View 7 Replies View RelatedI have a set of 5,800+ data points between 0 and 1 that I would like to multiply together. When I use PRODUCT for the whole set, the formula returns 0. However, I can use a smaller subset of the data to return a very small number. I'm curious if Excel has a closest-number-to-0 or number-of-cells-for-PRODUCT limitation. Is there another way to perform this calculation?
View 4 Replies View RelatedI was recently helped very much by the forum moderator and code from D_Rennie in a file to transpose about 25,000 rows into a few organized columns. The code worked very well. I have a slightly new twist on the problem. I have uploaded a different file with a different data structure in the columns but the same need for the data to be transposed to the columns under the same headings. This VBA worked very well before, but I can't seem to re-use it for this new dataset.
View 8 Replies View RelatedI have a spreadsheet which has general usage and summary data at the top for ease of use. Begining at Row 15 the data is a standard table which includes columns for the following.
Cust Name | Status | Loan Amt | etc.
I have created a macro to make it easier to add new data rows at the top of the data already in the file (this makes it easier to view with the newest data at the top). I would like to create a conditional sum of the data in "Loan Amt" if the value in "Status" ="Active". I had no problem achieving this with the conditional sum tool but the problem is that the range changes whenever a new row is added to the top of the list. Unfortunately the range in the conditional sum adjusts as the old data shifts down for the new row and the sum does not contain the new row data.
I have to do a search on a dataset (about 3000 entries) and part of the exercise is to look in some project titles if there is a country or other key words included.
I have look on the web and unable to find a formula that works. Basically what I want to do is to have a formula a looks in the column of titles for a list of keywords on another sheet in the same book.
a macro (that i will link to a control button) that will autofilter a data set. The problem i have is the macro i wrote below, It might not be the best looking macro in the world, but it would work for my purposes if i can get the part that does the autofiltering to be more dynamic. meaning, instead of a hard coding "Retail" in the macro, id like it to reference a cell so that the user can type whatever they want, then click the button and it will filter based on what they type in.
this is what i have
Sub Filter_Button()
With Sheet2
AutoFilterMode = False
Range("A6:M6").AutoFilter
Range("A6:M6").AutoFilter Field:=2, Criteria1:="Retail"
End With
End Sub
I want to create a simple data base of times but it must be as I have in col A .
I am not able to change from using these : .
I have tried scrolling down but when I get to .99 it rolls over to .100
My data starts at 44.00 seconds and wanting to go up to 9:00.0 minutes .
GOOD A1TIME259.97359.98459.9951:00.0061:00.0171:00.0281:00.0391:00.0410
All the way up to 1:00.97111:00.97121:00.98131:00.9914
Then next is 1:01.0015Then next is 1:01.01 16Then next is 1:01.0217
Then next is 1:01.0318Then next is 1:01.0419Then next is 1:01.0520 21
Wanting to go all the way to 229:00.0 and end there
I am looking for a macro that is capable of deleting all rows in a dataset where the following statements are not true: column B is equal to "OP00" (o, p zero zero), the left hand character of C is equal to "L" and D is equal to "CC", as in the scenario below. I basically need to keep all rows which match the structure below, i.e. to clean the data.
B C D
OP00LKAOCC
OP00LMRPCC
OP00LVOFCC
OP00LVOFCC
OP00LVOFCC
OP00LVOFCC