Allocation Of Permutations In Excel

Nov 26, 2013

I have trying to assign cards to respondents to view. I have 16 cards total which are grouped in sets of 4. Each set (of 4) is given a color, (red, green, blue, and yellow). Each color has a 4 different shades (S1, S2, S3, S4) so I have the following 16 cards:

1: Red_S1
2: Red_S2
3: Red_S3
4: Red_S4
5: Green_S1
6: Green_S2
7: Green_S3
8: Green_S4
9: Blue_S1
10: Blue_S2
11: Blue_S3
12: Blue_S4
13: Yellow_S1
14: Yellow_S2
15: Yellow_S3
16: Yellow_S4

I will have a total of 10 respondents giving their opinions who will each see 5 of the cards, randomly. Each respondents must see each color at least once (given that 5th card will be a repeated color). Each card must be tested 3 times (given that the 2 additional cards can be any of the cards).

If there a function or cell code that I can write so that I can allocate the card #s (1-16) across my 10 respondents?

View 1 Replies


ADVERTISEMENT

Excel 2010 :: Fails To Release Memory Allocation

Jan 27, 2014

I am running Excel 2010 32bit under Windows 7. The Excel file worked perfectly under Excel 2007 and Windows XP. The Excel file is somewhat complex with numerous links and embedded cell formulas. Occasionally, I get the following error message:

5.jpg

Before I install the 64 bit version, I attempted to chack a few other things. I opened the task manager and while viewing the process tab, I opened my Excel file. Once the file loaded, this is the allocation displayed in task manager:

1.jpg

This seemed reasonable considering the size of the Excel file (33Mb). Without changing anything, I clicked save to make sure it was working properly. While viewing the process, I watched the allocation climb, substantially. when it completed, this is what the process displayed:

2.jpg

Continuing to click the save button, without changing anything, continued to increase the memory allocation until the error message was displayed:

3.jpg

4.jpg

Closing Excel and re-openeing the file resets the memory allocation back to normal, which is a popular solution to this issue in the forums.

I did check the same issue using Excel 2007 and the memory allocation releases after each save.

I can see how installing the 64 bit version of Excel can correct this issue, and with 12Gb of Ram, I will probably use this method until I can get this fixed.

View 3 Replies View Related

Specific Number Permutations In Excel

Aug 3, 2013

The problem is that there are two sets of up to thirty three (33) numbers each, numbers are NOT duplicated in each set. The goal is to make an excel file that combines all the numbers from both sets in combinations of five numbers each. There should be a maximum of three numbers chosen from each set, in order to limit the total numbers chosen.

I would like to see the five set listed on the left of the excel file and the intended numbers sets on the right of the same sheet. This would permit me to see at a glance if there are any missing numbers.

For instance, from the first set of numbers 1, 2, 3 are chosen and from the second set numbers 30, 31 are chosen. Therefore the table would show as:

1, 2, 3, 30, 31.
1, 2, 3, 30, 32.
1, 2, 3, 30, 33, ...

1, 2, 4, 30, 31
1, 2, 4, 30, 32, etc.

This would continue until all of the numbers are used with each number listed in a separate excel box but next to each other without the comma.

each numbered set of five numbers does not have any numbers repeated (i.e. the number three appears only once per set of five numbers).

If further clarification is needed please go to: [URL] ..... where n=33 and r=3.

Repetitions are NOT allowed and order is important. This will calculate a total of 32,736 permutations to choose from. NOTE: this calculates the first set of three numbers only and is used as an example of the intended output, however the second set of numbers is not included there which I would like to have included in the excel file.

View 2 Replies View Related

Excel 2007 :: Permutations Of Text String For Phone Scripts

Mar 21, 2012

I have phone scripts that can be built from 6 different categories. Each of the 6 categories has anywhere from 1 to 11 different scripts within them.

I want to generate every possible script from these options, i.e:

Part I:
"Hi, as of 8am, on march 20th"

Part II:
"our records indicate 2 changes"
"our records indicate 50 changes"
"our records indicate no changes"

[Code] ......

I am using Excel 2007.

View 1 Replies View Related

VBA For Stock Allocation

Jun 13, 2008

Let say I have 5 shops: A, B, C, D, and E

They request for stock replenishment from warehouse as follows:
A: 4 pieces
B:2 pieces
C:3 pieces
D:3 piece
E:3 piece

But since the warehouse only have 12 pieces of this product so need to allocate to the shops in rounds that is:
Round 1: one piece is allocated to each shops (Stock left=12-5=7)
Round 2: one piece is allocated to each shops (Stock left=7-5=2)
Round 3: one piece each is allocated to A and C (the priority should be B but since B just requested for 2 pieces only which has been fulfilled) since there are no more stock

how to write the scripts to do this allocation.

View 9 Replies View Related

Auto Allocation Of Data

Jun 29, 2013

I have a list of 500 'cases' for staff to work on and I want to auto allocate them to people based on their skills. Whilst I can do this easily with each person have their own page in my workbook, I'm having the issue of the same data being replicated onto multiple sheets.

The main data is on one page and each person has their own individual page for their cases for the day. Is there any way to avoid multiples being sent out whilst creating a kind of 'priority ladder' as such?

View 8 Replies View Related

Random Distribution/Allocation

Nov 1, 2009

I have a spreadsheet which has a list of staff (column B) and work categories (row 3).

Row 4 is updated each day with a count of the incoming work for each work category.

I want to be able to randomly distribute (as evenly as possible) the no of work items in Row 4 between the doers (B7:B13). Each item of work also has to be checked so I would also like to randomly distribute the checking between the checkers (B16:B18)....

View 9 Replies View Related

Time Allocation Calendar

Jul 12, 2007

I am creating a time allocation / holiday tracking calendar. 150 employes (1 per column) and then using rows (10 - 375) as a daily track. I am having trouble with two aspects and would value your assistance.

1. i have locked the top 9 rows and would like row10 to jump automatically to todays date. i.e current info directly below the titles block

2. How do i make the calendar wrap around i.e. if someone puts in for a week holiday starting dec 31st 2007, how do i make the calendar roll round to 2008.

View 4 Replies View Related

Coming Up With Inventory Allocation Formula

Aug 4, 2014

I've been assigned a project where I'm suppose to report inventory allocation to projects at end of day every Thursday. The past 2 Thursdays, I've just literally been manually plugging in the numbers into the allocated stock column.

The spreadsheet I came up with is attached. The 1st tab is the demand showing the needed quantities per item and respective due/on-site dates. The on-hand inventory (stock) is on the 2nd tab. My current process is:

1. once items have been shipped, I delete those entries/rows from the first tab. This way, only all the outstanding/open orders remain.
2. after completing #1, I then copy and paste on-hand inventory from our database (Navision) onto the 2nd tab.
3. I manually plug in the the qty for each item depending on stock available. This is the part that I would like to streamline.

Inventory Allocation.xlsx

View 2 Replies View Related

Formula For Batch Allocation Or Split Up?

Oct 2, 2013

I need a formula for batch allocation automatically.

See the attached file : Batch_Allocation.xlsx‎

View 5 Replies View Related

Changing Cost Allocation Macro?

May 23, 2012

I am trying to write a Macro to allocate a certain cost across thousands of orders. The number of orders will obviously change monthly. The cost is relatively small, and when divided evenly by the total orders needs to be rounded up to the nearest cent (Typically $.01 - $.02)

I then need the cost to be insered in a colum running next to a total cost column, but I need it to stop when it reaches the total cost and just start adding zeros to the rest of the orders so as not to overcharge.

Not sure if I need to incorporate solver somehow. I figured I would reference the allocated cost from a cell in the worksheet but not sure how to insert any algebraic logic into a macro.

View 9 Replies View Related

Fill Vehicle Allocation Table

Nov 26, 2006

I am reposting this as my previous post was deleted.. i dont know the real reason for the same...

The raw data in the attachment contains:

•C1 to AI1 refers to vehicle numbers.
•Column A refers to Shift Time
•Column B refers to Number of vehicles required in that particulars Shift time

The output:

•As you can see in the Sheet “Output Required”, fields marked in the color “light green” are manually allocated vehicles numbers available and required according to shift time

•The logic used behind allocation is the same vehicle should not be used before or after within the span of 3 hrs, the gap between the vehicle usage before and after should be minimum 3 hrs. And the maximum time gap can be any value.

•Vehicle usage can be maximum (ie. You can use the same vehicle by rotating within 24 hrs but keeping in mind that it is not used before 3 hrs).

• all the available vehicles must be used before the same used vehicle is used in the new shift... ie. You should utilize all the vehicles before one is duplicated

View 9 Replies View Related

Allocation By Daterange - Single Cell

Apr 27, 2007

I m creating a single cell formula in 'report'B3 that would allocate 'data'D3 appropriately by month heading if copied across. A few other threads on this topic only acheive this by creating an addtional row, not in a single cell. Since I'm going to use this on a report in Access, I just need the calculation. Is it not possible to acheive this in a single cell?

View 2 Replies View Related

Revenue Allocation Using Combined/Nested IF Statements

Jul 24, 2008

I have been asked to go through some information which has over 200 000 lines, what I need to do is as follows:

where order number is unique, revenue source = "Unique"
where order number appears more than once, check division to see if it appears in more than one division, if it does revenue source = "Cross Selling", if it does not then revenue source = "Divisional Package"

I need all of the above in formula line which will be inserted in the revenue source column

I have attached the file with a sample of the information I am using.

View 9 Replies View Related

Summarise Data After Applying Allocation Split

Feb 25, 2009

The task involves summarising about 10,000 lines of data into months by Plant by Process. I can achieve it by stepping through a couple of schedules but I'm sure it can be done using one formula (but I can't find it!).

View 8 Replies View Related

VLookup With 2 Criteria - Provide Log Of Staff Allocation

Aug 31, 2012

I'm fairly new to excel and trying to create a simple spreadsheet to provide a log of staff allocation. I've created a first a sheet displaying a table with a week of rota. The first column lists where the staff are placed, the first row the date and inbetween the staff member covering that date. There are 5 further sheets, one for each member of staff with a simple table - first column is date, second is allocation.

Back on the first sheet below the timetable is a further grid showing on the first column staff names, and below each date a formula =VLOOKUP( B$3,David!$A:$Z,2,FALSE) pulls information from the 5 sheets displying whether a memeber of staff is on leave or there allocation on that particular day.

The problem i'm having is trying to get the 5 simple staff sheet to pull data from the Rota ie. I need say B3 to look at the date in A3, find the same date on the Rota sheet, look for the relevent staff name below it (if present), and display the allocation from the first column on the same row as the staff member.

I've tried a combination of Vlookup with Match and Index with Match but nothing seem to work.

View 7 Replies View Related

Input Allocation Percentage Dependent On Number Of Variables

May 15, 2014

On the attached sheet in column X, I am trying to input the Allocation percentage dependent on a number of variables:

Type (Bar, Blank, Coil, Sheet, Special)
Sub Fam Code (2A02, BC02-05, BC99, BZ99)
Sup Category (Arcelor, Independent, Tata/ Corus)

The allocation percentage is based on the below; anything outside of this should result in "Other".

Sheet (BC02/03/04) A=40%, T=40% & I=20%
Coil (BC02 & BC03) A=40%, T=40% & I=20%
Coil (BC04) A=30%, T=50% & I=20%

Example 1:
Type - Sheet & Coil
Sup Category - Independent
Result 20%

Example 2:
Type - Coil
Sup Category - Tata/ Corus
Sub Fam Code - BC04
Result 50%

Example 3:
Type - Coil
Sup Category - Arcelor
Sub Fam Code - BC04
Result 30%

View 1 Replies View Related

Permutations And Combinationns

Feb 24, 2009

I'm sure I should be able to do this but my brain is out of gear today!

Can someone tell me how I can create a list of every 5 letter permutation using only letters (A-D).

I also need to do the same for 4 letter and 3 letter, again using A-D.

Would prefer to do it without VB but if this is the only way......

View 6 Replies View Related

Combinations (not Permutations)

Apr 26, 2006

You will find a VBA Code for a PRIVATE example - displaying all 6 numbers combinations out of 10 (1-10).

My code displays them (in column "A") BUT What my question to you is - does someone have a General/Universal code to handle other kinds of combinations.

To my opinion, the usage of Nested (For-Next) Loops will not be the ideal solution - so maybe by using VBA Recursion ?

View 3 Replies View Related

All Combinations/Permutations

Jun 2, 2007

I must first thank the forums for the amazing help in making my life just a little easier, especially member "shg" for helping set up the VBA code to help me.

So I have a sheet to develop all possible combinations of units, the letters with their associated values above, as well as the maximum number of units possible. The user inputs a top value, this is followed by some subtraction leading to a working value with a tolerance level in which the sum of the combinations needs to remain within.

What my predicament is that while the VBA code in the attached book works to develop most combinations, when I input a small number such as 405, the combinations that arise are not complete. The results are shown in the attached workbook: show combinations of H and PP, but any human can read that there should also be 2 HN in the results, as the math add's up to within the tolerance level.

View 9 Replies View Related

Class Of Permutations

Jul 23, 2007

I am creating a class of permutations. The problem is that there are many ways to describe what a permutation does. for example.

Dim aPerm As New clsPermutation
Dim bPerm As New clsPermutation
aPerm.DoesTranspose(2,3) : Rem aPerm.Action({a,b,c}) = {a,c,b}
MsgBox (aPerm Is aPerm.Inverse)

I want the message box to return True. But since aPerm and aPerm.Inverse are different instances of clsPermutaion, it returns False. (.Inverse is a read-only property that returns a clsPermutation.). What I would like to do is have a collection, myPermutations. When I specify the action that aPerm does, I would like

1) if nothing in myPermutations does the same thing as aPerm, then add aPerm to myPermutations.

2) if something (xPerm) does the same thing as aPerm, then

Set aPerm=xPerm
so that "xPerm Is aPerm" returns True.
The problem I have is that in the class module,.....................

View 8 Replies View Related

Permutations For WORDS In A Cell

Jul 23, 2012

I need VB codes for an activity in excel. Im trying to create an example file for the suppliers to enter keywords (Search keyword) where it should generate different formats of the search (WORDS) and its case sensitive. Suppliers would be typing the keywords in A2 and it should generate the Permutations horizontally against the keyword (B2 C2 D2 E2 etc). Let me know if you need more clarifications.

Example given below and also attached the file for your reference.

New Keyword – Singular Proper Case
Singular-PR
Singular-LC
Singular-UC

[Code]....

View 4 Replies View Related

VBA To Create Permutations Of Different Rows

Apr 22, 2014

Simple permutation problem that will most likely requires nested loops. I need a list of every possible permutation of words in a row broken out by column. Order does matter and not every row has the same number of words in it. I have attached a sample worksheet with a sample table and then the needed output format.

View 5 Replies View Related

Permutations Of A List (groups Of 3)

Aug 31, 2007

Is there any way to determine all possible unique combinations (or sets) of any 3 strings in a table? I need to make a master set list from a list of strings which could be over 100 elements long.

Once I have a result set, I need to get rid of any groups which have a value contained in another group.

Example:
cells A1 through B100 contain:
Item1 45
Item2 50
Item3 98
...
Item100 75

Result set would contain groups of any 3 item combinations where the corresponding "B" value is within a certain range (between 40 and 80), while also trying to create sets with the lowest possible sum (placed in the 4th column) of the 3 numbers.

One possible combination would be:
Item1 Item2 Item100 170

etc...

I need to list all possible combinations, without re-using an element from a previous (lower sum) combination that matched the criteria.

View 13 Replies View Related

Generating Permutations And Combinations

Jun 27, 2009

For Ex:- If there are three Items.. A B C and forming a group of 2
then the the total no of combinations would be permut(3,1) = 6...

I need this to be shown as follows depending on the no of itmes and no of group no=2 in this case...

Permuations & Combinations are as follows:

AB
AC
BC
CA
CB
BA

View 10 Replies View Related

VB For Combinations Permutations (3 Datasets)

Mar 1, 2013

I own a custom print shop and I am trying to generate skus based on variables..Column A will only have one item (the product code), column B will have the sizes, Column C may contain more or less colors depending on what is offered for the shirt style, Column D hopefully can contain all the combinations. All this data will be in contained within one worksheet..

Column A
Column B
Column C
Column D

G200
S
Red

[Code] .......

View 3 Replies View Related

IF - FORMULA RESTRICTED TO 7 PERMUTATIONS

Nov 15, 2006

This formula will only do 7 permutations, I need it to do 8. Is there some way to do that? Thanks in advance.

=IF(AND(A10,B10),B10,(IF(AND(A10,B11),B11,(IF(AND(A10,B12),B12,(IF(AND(A10,B13),B13,(IF(AND(A10,B14),B14,(IF(AND(A10,B15 ),B15,(IF(AND(A10,B16),B16)))))))))))))

View 9 Replies View Related

Permutations Checkspelling Speed

Mar 21, 2007

A1 has some characters
this code will generate all possible words, that can be made using all characters

system
permutate and checkspelling: if OK then write to column B

example
A1: iftrs
results: first frits rifts

Option Explicit


Dim CurrentRow
Const col = 2

Sub correctly_spelled_permutations()
Dim InString As String
Dim CalcSet As Integer
InString = Range("A1")
If Len(InString) < 2 Then Exit Sub

With Application
.ScreenUpdating = False
CalcSet = .Calculation
.Calculation = xlCalculationManual
.EnableCancelKey = xlErrorHandler
.StatusBar = "searching valid combination"....................

View 9 Replies View Related

Permutations Or Combinations Of A Set Of Numbers

Mar 11, 2009

I want to output every combination of a set of numbers. These permutations must include combinations that use only a few of the numbers as well as all eight...ie.

1,
1,2
1,2,3
as well as 1,2,3,4,5,6,7,8

The macro below is from
http://www.j-walk.com/ss/excel/tips/tip46.htm
This only produces combinations using every number. I'm not sure how this macro works but hopefully someone with better know how could run with it or break it down for me!

I only need to achieve this once but am pretty sure doing it manually will cause error and or madness

Dim CurrentRowSub GetString() Dim InString As String InString = InputBox("Enter text to permute:") If Len(InString) < 2 Then Exit Sub If Len(InString) >= 8 Then MsgBox "Too many permutations!" Exit Sub Else ActiveSheet.Columns(1).Clear CurrentRow = 1 Call GetPermutation("", InString) End IfEnd SubSub GetPermutation(x As String, y As String)' The source of this algorithm is unknown Dim i As Integer, j As Integer j = Len(y) If j < 2 Then Cells(CurrentRow, 1) = x & y CurrentRow = CurrentRow + 1 Else For i = 1 To j Call GetPermutation(x + Mid(y, i, 1), _ Left(y, i - 1) + Right(y, j - i)) Next End IfEnd Sub

View 9 Replies View Related

Combinations/Permutations Of Values

Oct 5, 2006

I have a number 23753.2570, to be precise.

in another column, i have 400 rows filled with different numbers, from 1.4 to 23000,7840.

I need to find out all the possible combinations of which numbers from that 400 can make up 23753.2570.

so if my number was 40,000, and i had 4 cells with 10,000 in them, the outcome will be:

"There are 4 possible combination to your value"
then paste it into a new worksheet with the results
[cell1] = 10000 + [cell2] = 10000 + [cell3] = 10000 + [cell4] = 10000
Total = 40000

i found this code, but i tested it on the example above and it said "All Combinations exhausted" which they wasn't?

Sub findsums()

'This *REQUIRES* VBAProject references to
'Microsoft Scripting Runtime
'Microsoft VBScript Regular Expressions 1.0 or higher

Const TOL As Double = 0.000001 'modify as needed
Dim c As Variant

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved