I have been asked if I can create a pseudo Lottery/Bingo card of 7 images with no repeat cards. The best i can tell is that I need to create a Permutation of the numbers 1 - 7. There should be 5040 combinations of unique possible cards. My problem being I don't know how to create this Permutation in Excel.
View 4 RepliesI have several items that I would like to combine, lets call them characters A, B, C, D, E
Each character is situated in a different column, next to each other. Below, I would like to create a list of all possible combinations of these characters. The sequence is not important and each row should contain a unique combination of characters.
I have counted 31 combinations for the ABCDE set, namely
1 for ABCDE
5 for A, B, C, D, E each
5 for choosing 5 unique sets of four from a set of five (5!/(4!*(5-4)!)
10 for choosing 10 unique sets of three from a set of five (5!/(3!*(5-3)!)
10 for choosing 10 unique sets of two from a set of five (5!/(2!*(5-2)!)
To complicate matters, I have several other character strings where I would like a list of combinations. For example A, B, C, D, E, F, G, H 9 (a longer set).
By the way, each combination should be spread accross several columns (e.g. one colum per character, see the question marks in the table below) ....
getting All permutation and combinations of 4 letters and 4 numbers?
View 2 Replies View RelatedThis is for an online sports management game I play. Basically I have calculated each players skills in each position, and I need to find the best combination of the players on the field to give me the best team.
I have a table as below, and I need to find a solution that gives me what the best combination will be....
Can this be done in Excel? Using the digits 0,1,2,3,4,5,6,7,8,9 create all the permutations/combinations with limits on some of the digits. The digits 0,7,8,9 can only be used a max of 3 times in any one permutation/combination, digits 2,3,4,5,6 can only be used 4 time in any one permutaion/combination, producing a 5-digit string.
Example of expected results.
0 1 0 2 7
I am trying to create a list of permutations in a column for example I need a list of numbers 1-20 with no numbers repeating. This is causing my head to hurt and I have been working on this problem for some time.
View 9 Replies View Relatedi have read through all threads about permutation, and also a few others. I if I were an Excel Pro I would probably have been able to come up with a solution to my problem based on all the suggestions made, but unfortunately it is (still) all a bit too complex for me.
The problem:
I have between 2 and 5 lists of words, each list in one column:
Column A
red
green
yellow
black
blue green
pink
Column B
shirt
jumper
blouse
coat
t-shirt
skirt
Column C
for women
for men
for girls
extra large
for boys
one size fits all
Column D
Can Excel work out every possible combination of 3 numbers from a numbered set 1-24.
I need every combination of 3 numbers.
following issue:
The following table is given:
flower
20
rose
flower
21
rose
[Code] ........
Which needs to be turned into:
flower
20
22
rose
flower
31
32
blossom
tree
1
3
apple
The last column is the one that dictates when a new range of numbers start. There should be one range of numbers for Rose, One range for Blossom etc.
I'm new here, google helped me to find this place. Hope someone here can help me a bit on my excel spreadsheet. I have one list of numbers and it need to be sorted under Perm condition so the list become shorter. The function should go through the listed number and output new list after sorting it. I have attached my excel here.
View 7 Replies View RelatedI have found this very challenging, and have really confused my self on how to systematically approach this. I have the user enter the value in the black highlight boxes, some subtraction produces a base value, the user may also enter the tolerance of the working value (base value), effectivlly giving rise to a small range to generate the permutations from.
What I would like to do then is find all permutations of the inventory (letters in the sheet) with their corresponding values right above them to the small range determined via the user (working value and tolerance). There are also quantity limits for each item. There four basic rules that need to be adhered to:
1) Permutation can't have both S and PP, so one or the other
2) Sum of H and HN must be greater or equal to sum of S and PP
3) H or HN or both must be present, so their sum cannot be zero
4) If HN is less than or equal to 2, and H is greater than 2, then NA and NG must equal 0.
I would like to create a new products numbers column in Excel.
I have my supplier "SKU's numbers" in column A , I would like to create my "SKU's numbers" in XXXX format (only numbers) in column B.
Could someone help me pls todo it with Macro or if it possible todo it in formula ?
The code should check in the excel sheet, that it is not generate a new SKU that was used before !
See attached spreadsheet. I am trying to create one formula that will take the product of numbers in column B based on criteria in column A (want the product of %s in each year). Product if formula.xlsx
View 7 Replies View RelatedWay to create order and quote numbers. I'm currently starting an order number with a "O" in front of the order number and a "Q" in front of the quote number but this presents a problem when trying to create the next consecutive number because the number has a letter at the start of it so I can't use the method plus 1. I'd just simply remove the "O" and "Q" from these numbers but I need the numbers to be unique from each other and putting the "O" or "Q" in front was the only thing I could come up with to avoid the numbers ever being identical. I often need to look up these numbers and return the values in that row so the numbers need to be unique from one another.
The information for quotes and orders is saved on separate workbooks but I'd still like the numbers to be unique from one another.
Having a letter at the start of each number or a work around for using the plus 1 method when you do have a letter at the start of the number?
I'm working with some diagnostic messages produced by a communications network, and trying to isolate a certain kind of message. There are many different types of messages contained in the error logs, and I need to look at different ones at different times. As a single log may contain thousands of messages, I'm trying to use VBA to grab the ones I want. The easy part is that these logs can be exported as CSV files.
Each message contains an identifier which I can use for finding the correct messages of a given type. (I'm only looking for one type at a time.) However, this identifier is not on the first line of the message; it's buried a few lines down, and the number of lines is variable. Additionally, the length of the message is variable. The good news is, all messages of interest start with the same text string, and all of them end with another consistent text string. It looks something like this:
Message Start
blah
blah
Message Type
blah
blah
Message End
(Where "blah" is useful information that varies, and the number of lines of blah varies.)
The Message Start string is constant across all message types, as is the Message End string, so I can't use them to find the correct Message Type. However, they are still useful for marking the start and end of each message.
I'm using the find function to find the correct message type, then another find (going up) to get the start of the message, and putting the row number into a variable (msgStartRow). Then I find again for the end of message (msgEndRow).
That all works just fine.
What I can't figure out is how to turn those message row numbers into a range that can then be copied and pasted onto a separate worksheet.
How can I use these variables to copy the correct range? For example, this is what I recorded manually, but it's not intelligent. It can't accommodate different message lengths:
Code:
ActiveCell.Offset(6, 0).Range("A1:A145").Select
Selection.Copy
I have 12 Cells, years 1 to 12. I want to create a random number for each cell, which then depending on what the number is, either 1-75 being 8,000,000 or 76-100 being 10,500,000, place it in the cell and be able copy it down. What i have done already you can see on the attached workbook, or the table i used:
I used this table to generate the random number to give either 8m or 10.5m, except i put the VLOOKUP in the year 1 cell, added some dollars signs and copied down but that only makes them all the same, so i want a way to make each year have its own random number preferably without making 10 tables.
I have a column with many different names and want to put a number at the end of the name. Is there an excel formula that can do this? For example:
ColA
BUY
BUY
BUY
BUY
BUY
BUY
SELL
SELL
SELL
SELL
SELL
SELL
SELL
SELL
SELL
SELL
KEEP
KEEP
KEEP
KEEP
DESIRED RESULT:
BUY 1
BUY 2
BUY 3
BUY 4
BUY 5
BUY 6
SELL 01
SELL 02
SELL 03
SELL 04
SELL 05
SELL 06
SELL 07
SELL 08
SELL 09
SELL 10
KEEP 1
KEEP 2
KEEP 3
KEEP 4
As you can see, if the number of consecutive texts goes over 10, I need it to add a "0" onto the beginning of the number.
Is it possible to create a string with a range of numbers
1
3
4
5
6
8
10
Result: 1,3:6,8,10
Or
1
2
3
4
Result: 1:4
Or
1
3
5
7
Result: 1,3,5,7
Where consecutive numbers are separated by a ":" and other numbers by a ",". I use the following function to create the ","-concatenate, but I don't know how to create the ":"-ranges.
Function AConcat(a As Variant, Optional Sep As String = "") As String
'By Harlan Grove, March 2002
Dim Y As Variant
If TypeOf a Is Range Then
For Each Y In a.Cells
[Code] .........
I attempting to create a formula that will create random numbers in a column without repeats. For example, I have a spreadsheet with two columns. The first column I want to contain the random numbers and the second column will contain names. I just need the formula to create the random numbers once.
View 2 Replies View RelatedI need to create a purchase order page whereby each item is then transposed into seperate sheets detailing the product along with price etc. I need the po number to be automatically generated to follow on from the previous therefore giving us a continuous po thread. I also need the purchase order as typed to be saved into a seperate sheet / file each time so that the po is always blank when someone else creates a new post
View 4 Replies View RelatedI would like to have a sheet where there is a cell designated as "start number" (where any number may be entered) and a cell designated as "how many times". I would then like to be able to click a button and excel generates a list of sequential numbers starting from the number designated in the "start number" cell as many times as is designated in the "how many times" cell.
View 2 Replies View RelatedI am trying to design a function that grabs an average of only numbers that are within a certain deviation of the mean. I got the plan down, and have done this before without VBA, its just a user defined function will be much quicker.
The gist of what I am doing: Find the standard deviation of a range and then only find the average of numbers that fall within n deviations from the mean. I am stuck with the part that I would normally use an array code for. Here is what my array would look like:
{=AVERAGE(IF((nums>high)*(nums
I'm working on a long list of cells that have multiple entries of the same part number at different point on the list. I want to create a function that consolidates those part numbers in two columns to the right of the raw count.
PART
QTY
CONSOLIDATED PARTS
CONSOLIDATED
QUANTITIES
I have to make a macheine cycle chart. I have a column containing Step Names and a column containing step time in whole seconds. I need to paste the step names across Row 1 and whole numbers counting to X down colum A. I then need to have each second represented by coloring the cell, for example, Step 1 is 1 sec. and Step 2 is 2 sec. if "Step 1" is in B1 I need B2 colored then for "Step 2" in C1 i need C3-4 highlighted and so on for a variable number of steps. This is my first question so I hope I was clear enough. With this much programed I will be able to make simple changes to tweak it to fit my needs.
View 2 Replies View RelatedI have some code which, which I have modified it to create check boxes for all rows of data, but when I run it, it begins to wipe out the data, and create an endless column of numbers and check boxes. Here is the code:
Sub SelectCell()
Application. ScreenUpdating = False
Dim Z As Range
Set Z = Cells(1, 1).EntireColumn. Find("*", SearchDirection:=xlPrevious)
For i = 2 To Z
Range("A" & i) = i
Set cll = Range("A" & i).Offset(0, 1)
For Each shp In ActiveSheet.Shapes
If Left(shp.Name, 8) = "CheckBox" And shp.TopLeftCell.Address = cll.Address Then
shp.Delete
Exit For.......................
I have tried to set it to recognize how many rows are filled with number, give that to variable Z and then apply the code but it simply does not work.
I need to create a macro that can produce a new workbook with the list of the most recent users of my company, so that we can print their membership cards.
From my company's admin I can export the complete list of users with their name, serial number and address. Every time we export this list, the file is saved as "userdata-dd-mm-yyyy.xls", and "dd-mm-yyyy" as today
However, I would like to create a macro that would
1) open the most recent "userdata-dd-mm-yyyy.xls" workbook in the folder,
2) compare the user numbers in this file with the most recent "card_order-dd-mm-yyyy.xls" and
3) produce a new "card_order-dd-mm-yyyy.xls" workbook with the list of all new users since the last "card_order-dd-mm-yyyy.xls" ("dd-mm-yyyy" as today), based on the highest number for user number (this is a continuous list)
Note, the "userdata-dd-mm-yyyy.xls" workbook has name, serial number, address, expiration date and user number as columns.... but the extracted info to "card_order-dd-mm-yyyy.xls" is just of the name, serial number and expiration date.
What is the easiest way of doing so and what code should I use for the macro?
I need a macro to create a hyperlink i.e....... I have an excel sheet with numbers in column A.....I have files in a folder that matches the numbers in column A....
How would I create a macro to create a hyperlink from the numbers in column A to the "matching" file names in a folder?
I need to create a function that numbers rows with respect to data groups in a column (column labeled "Type" in this example). The result would be that shown in column A in this table.
How do I write the function?
#TypeName Date
1CarsJohn1/12/2008
2CarsJane11/10/2007
3CarsMary11/2/2004
4CarsBob12/7/2003
1TrucksMike12/12/2007
2TrucksSandy1/3/2007
3TrucksDale12/14/2006
4TrucksVince4/8/2005
*
I want to create a dynamic line graph using week and year numbers stated in another sheet.
e.g.
Start Year - 2012
End Year - 2014
Start Week - 3
End Week - 12
The top 2 rows above my graph data are as below:
Year - 2012 2012 2012
Week - 5 6 7 etc.
This works fine if the start and end year are the same but if it's greater than one year, it doesn't recognise that.
I'm trying to create a staff rota which will populate a rota randomly when prompted - I have been trying to find some way of connecting the random lists and the staff names, though this has proved difficult (to say the least!). How best to proceed? I'm also fully aware of the possibility that my present design will also double book people (place then on reception and telephone duty simultaneously).
View 3 Replies View Related