Combination Without Repetition
how to create a list (I know how to calulate the count) of combinations without repetition when choosing 2,3,4 and 5 words from a set of 5 in Excel 2007.
e.g. Alpha,Bravo,Charlie,Delta,End
(AlphaBravo=BravoAlpha)
Choosing 2 = 10
Choosing 3 = 10
Choosing 4 = 5
Choosing 5 = 1
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
To Compare Details In 2 Workbook And Highlight If There Is Repetition
If i have a string of information in 2 workbook and I need to check if the details in column a in workbook 1 has a duplicate entry in column b in workbook 2 and if there is a duplication, then highlight it in both workbook. How can I go about it to create the codes so that I will be able to use it for different workbooks without changing the codes every now & then? Scenario:  Monthly invoice verifications for a few vendors. To verify info from the manual invoice againts the auto invoice (2 different workbook for each vendor) Need to verify info from Column A in Manual invoice against column B in Auto invoice. If there is a same data in both column, then data to be highlighted.
View Replies!
View Related
Numeric Combination
on combination of numbers on the extreme left column, i have 23 numbers from A1:A23. All 23 numbers are in the form of 4 digit. For example A1 there is 1234, i need to display the possible 3 digit combination of this in the same row (like say 123,124,234,134 in B1,C1,D1 AND E1). Another example in A2 there is 3545, i need to display 354,455,355 in the same row in B2,C2,D2 I need to perform this operation for the 23 numbers on the extreme left row. Can give me some hint on the code.
View Replies!
View Related
Combination Of Numbers
I have a list of numbers from 17 would look like this each number in a seperate cell. 3 5 1 2 3 5 6 7 2 4 5 6 7 6 7 4 6 6 I want to use one number from each row (which there is only 6 rows) and then find every number from 17 that will complete the sequence 17. So with the numbers above (using one number from each row) the only other numbers that could be used would be 3 or 5. The combos that would work: row 1 use 3  row 1 use 5 row 2 use 1  row 2 use 1 row 3 use 2  row 3 use 2 row 4 use 7  row 4 use 7 row 5 use 4  row 5 use 4 row 6 use 6  row 6 use 6 5 would complete  3 would complete Remember the numbers and how many numbers in each row can change but will always be 17 and I always need to find every number that can complete the sequence 17 by using one number from each row.
View Replies!
View Related
IF And OR Function Combination
I have 2 cells with numbers. In a 3rd cell I want to create a formula which looks at the 2 data cells and shows a value. The rules are the following: If C1 or C2 are bigger than Xthen C3=value1 else C3=value2. I have some basic excel knowledge but im not very familiar with functions. I'm using Excel 2007.
View Replies!
View Related
2 Digit Number Combination
I'm trying to figure out how to setup a worksheet to find the most common 2 digit numbers going vertically from the bottom(cold) to the top(hot) it would consist of 90 digits 0 thru 9 it would look like this 4 0 3 9 0 4 3 3 2 9 2 5 6 5 6 9 6 6 8 9 9 3 1 0 2 9 8 1 6 7 5 9 9 8 2 5 2 7 2 2 2 8 5 1 3 0 1 4 7 4 7 6 0 9 3 5 6 4 3 3 0 4 4 7 8 8 1 8 5 7 8 7 5 4 1 8 6 1 1 5 1 6 3 0 0 7 2 4 7 0 each vertical line would be considered weeks 9 thru 1. week 9 would be the first vertical line of digits on the left. it could also contain the most common 2 digits horizontally. Both 2 digit values would be color coded ex. blue equals most common 2 digit horizontally and green equals vertically. I would also like to color code the most common 2 digit value diagonally as long as it is the most common of either the vertical or horizontal 2 digit. Each number is seperate on the worksheet they would not be pairs. im using excell 2003.
View Replies!
View Related
Lookup/If Statement Combination
I am trying to do a lookup from the price list based on the below data file, my problem is I need the price that falls within the qty range from the price list, the price list is not consistent and changes with every item. I would like one formula that is sophisticated enough to work no matter where the qty range lies. Item #_____Qty Ordered________Price List 20800______1,500 20800______4,000 20800______6,500 20800______8,000 20800______9,050 20800______11,000 20800______14,000 Price List Item #____Order Qty________List Price 20800_____2,000___________$3.00 20800_____4,000___________$2.95 20800_____6,000___________$2.90 20800_____8,000___________$2.85 20800_____10,000__________$2.80 20800_____12,000__________$2.75 20800_____14,000__________$2.70
View Replies!
View Related
Every Possible Combination Of A Group Of Numbers
The numbers are file attributes, as you know these are Normal = 0 Read Only = 1 Hidden = 2 System = 4 Volume = 8 Directory = 16 Archive = 32 These numbers are cumulative, so if a file has an attribute of 5 it is Read Only and System (1 + 4), it can't be anything else. Or if it has an attribute of 6 it can only be Hidden and System (2 + 4). What I need is a spreadsheet that calculates every possible combination of these numbers, so I can check my Select Case statement has covered all possible combinations. If it was just a one off project I could just work it out "by hand", but I have realised that there are several other projects I have that this would be useful in. e.g. I am doing a skills matrix at work. If I give each skill a number, then give each employee a cumulative total number then I can have a spreadsheet that shows their skills. For each employee number there will only be one possible combination of skill that add up to that number. My employer often adds new skills, so each time this happens I will have to check every combination is covered. So I really need a spreadsheet solution, something I can input a group of numbers and it will show me a list of every possible combination of those numbers. The number of numbers in the group will vary, so a solution that only works for a group of (say) 6 numbers won't work. It has to work on a variable group of numbers.
View Replies!
View Related
Combination Of Ifsum And Indirect
i've attached a worksheet yet removed my attempt at formulas as it would have made most of you cry... what i'd like to do is select an item from a dropdown list (B1) (that i've built and it works, phew) and display the summary data (B3:B10) from the column or sum of columns in array (A12:F21) as explained in the relationship matrix. I've tried ifsum and dsum (copied in each cell B3 to B10 of course) yet it doesn't extract items from the array with the dropdown selection. nor does it add columns together.
View Replies!
View Related
IF, VLookup, Sumproduct Combination
formula below... currently is giving me a:#N/A =IF(EG55="FCST",VLOOKUP(EE55,Control!$R$8:$R$20,6,0)*(SUMPRODUCT(($HH$11:$HH$62=EE55),($HJ$11:$HJ$62=EJ55),($HL$11:$HL$62))),NA()) If I remove the vlookup part the formula return the correct results, but I want the resulting number be multiplied by a percentage (that what's the vlookup is trying to accomplish).
View Replies!
View Related
Searching Combination Of The Criteria
I have created a program where there is a spreadsheet containing all of the items in a loan tools store, which are issued out to people. I have created a "Search/Find" function within the "Issued Items" sheet. Within this search function, you are able to search all or any combination of the following criteria: Serial; Description; Name; Location. For example: If you just type data in the "Serial" field, it will just search that column and select the cells which contain that value. The problem I am having is when searching multiple criteria, each and every cell in the columns which are searched is selected. Whereas, I would like only the cells which match all of the criteria to be selected. For example: If I was to type "1" into the "Serial" field, "2" into the "Description" field, "Liam" into the "Name" field, and "Workshop" into the location field: Current: Serial column is searched and all cells with "1" in are selected. Description column is searched and all cells with "2" in are selected. Name column is searched and all cells with "Liam" in are selected. Location column is searched and all cells with "Workshop" in are selected. What I Would Like: Program to search each of the specified columns and only select data which meets the searched criteria. For example: Rows 20 & 28 to be selected as they both contain, Serial "1", Description "2", Name "Liam", Location "Workshop". Note: The sheet will have password protection.
View Replies!
View Related
Lookup And Countif Combination
I'm trying to use a combination of Hlookup and COUNTIF. I'm selecting a date value in a cell using data validation. I'm then wanting to write a formula to lookup that value in a row of dates, and then use a countif to find all the '1' values in that column.
View Replies!
View Related
Check A Combination Of Two Cells
I would like to check a combination of two cells, if these two cells are both empty (not zero, just blank) then it will return a blank in another cell. I tried using AND but am unsure how it works. I would like to use a "Case" Function. Function FirstCheck(Count1, Count2) Select Case FirstCheck Case Count1 = "", Count2 = "" FirstCheck = "" Case Else FirstCheck = Abs((Count1  Count2) / (Count1 + Count2)) End Select End Function
View Replies!
View Related
Transpose And Vlookup Combination
Ive attached my example but explaination of what i am trying to do is below: In sheet 1 i have products listed with a product ref In sheet 2 i have a list of features by product ref. I want to be able to put each feature next to the relevant product in sheet 1, some products may have 3 features, others may have 5 or more.
View Replies!
View Related
Comboboxsheets Combination
I have a form (userform) which has a combo box and this combo box functions as a selector. I mean, when the person chooses one of the items (sheet1, sheet2, sheet3, etc.) inside the combo box, it views the sheet he/she picked. How will I do that thing? What code/s will I write in my module?
View Replies!
View Related
Choose Best Option/combination Of Options
I have a bunch of parts each with a list of packages sizes, prices, and additional piece prices. For example: Part #305213 1000 pieces  $7.99/Pkg  $.05/add'l piece 600 pieces  $4.99/Pkg  $.06/add'l piece 200 pieces  $3.00/Pkg  $.07/add'l piece 0 pieces  $0.00/Pkg  $.15/add'l piece I need to determine what is the most efficient way to order these items given various amounts. For example: If I need 1200 pieces, it's cheaper to order 2 of the 600 pkgs than a 1000 and a 200. If I need 400 pieces, it's cheaper to order 600 at $4.99 than to buy 2x200 at $3.00/pkg. If I need 1250 pieces, it's cheaper to buy 2x600 @ $4.99/pkg plus 50 pieces at $0.06/piece......
View Replies!
View Related
Finding Optimal Combination In A Matrix
I've been assigned a task of finding a combination of three or four machines. For example AD,AJ,AQ, and AB would equal 7 therefore it would be the best combination of workstations for that cell. However, I'm having an issue that if AD, AJ, AQ, and AB are being selected more than once. My question is, how can I analyze all the data and determine the best combination given the relationships for each row given the column. 3 = Absolutely Necessary 2 = Extremely Necessary 1 = Necessary 0 = Do not associate
View Replies!
View Related
Combination Of Pairs, Power And String
I have a string of n pairs and want to check various combination of that string. Example: Pairs 58 78 15 Since I know I have 3 pairs (but it can be 2 or 4), I know the number of combination I want to test, ie 2 power 3 = 8 combinations. How can I program a code creating the various strings, ie 587815, 587851, 588715, 588751, 857815, 857851, 858715, 858751 ? This is what I have so far (not much): Public unique_pair 'number of pairs provided by another macro Public mystring 'provided by another macro Sub make_guess() Dim number_of_combination, i number_of_combination = 0 number_of_combination = 2 ^ unique_pair For k = 1 To number_of_combination 'how to generate the various string ???? Next k End Sub
View Replies!
View Related
Auto_Open And Application.Close Combination
I have a macro that auto opens does a couple of open and saves but then the Application.Close doesn't want to close the blank Excel screen. Can't you use Application Close in a Auto_Open macro? Sub Auto_Open() ' ' Auto_Open Macro ' Macro recorded 1/27/2006 by Rich ' ' Keyboard Shortcut: Ctrl+c ' Application.DisplayAlerts = False
View Replies!
View Related
Barline Combination Chart
I have a table with 7 rows and 7 columns: First 6 rows  months i.e. Mar to Aug 7th row  Average no. of Breakdowns Colums  Mon to Sun I am trying to create a barline chart with the 'Average No. of Breakdowns' to be on the 2nd Yaxis and displayed as a line. The rest of the data would be on the primary Yaxis and displayed as bars, But I am unable to get what I want. Instead, the data on the 5th and 6th row is displayed as lines. I even tried formatting these 2 rows to the primary Yaxis but they are still displayed as lines.
View Replies!
View Related
Using Advanced Filter In Combination With Other Filters
I am having some problems trying to filter a list to display exactly what I want to see. The list has one column of part numbers, a second with due dates, and then another with quantity. I want to use an advanced filter on the part numbers to only look at unique entries. Then I want to filter that list using a custom filter on the due dates to only view those due within a certain period. So ultimately I want to view only unique entries due during a given period. I am able to apply one filter, but when I go to apply the second, the second filter removes the first. For example once I have filtered out duplicates, when I try to filter based on date all of the duplicates return.
View Replies!
View Related
Search Records With Combination Of More Than 2 Values
i've written the code below but somehow the output is not what i want. i have a multiple records and each records contain 30 rows of info i have 3000 over records and they are all in one sheet. i need to find record using more than 1 criteria. I've written the code below. how to use VBA to search for particular strings in cells? for e.g. A1 : hello A2 : world A3 : anyhow i want to find the cell that contains "yh" using VBA, and the output should be in cells A3.....
View Replies!
View Related
Index / Match / Rank Combination
I have several months worth of data, lets say January to December in cells A1:L1. In the rows underneath I have data, however, it is several "sets" of data. For example, I have data in A1:L10 (the first batch) and another batch in A21:L30 and so on. So you can see that there are rows between each batch of data (this must remain thus). What I would like to do is set up a formula that reads along the the dates at the top, then reads down to the batches of information only, and then ranks them. Ultimately, what I want is to tap in say "September" and then in a table I want to have the top 10 ranked in order from the batches of information from the September column, and this will change according to the month I tap in etc. I think it may be some combination of Index / Match & Rank, but I am struggling with the Rank formula applied to nonconsecutive ranges!
View Replies!
View Related
IF Formula Combination: Analysis Of The Month Value
I have to calculate the following: PREV = VND * NSF * GROWTH. But there can more than one choice for the values: VND: analysis of the month value; if this is empty then analysis the average; if the average is blank then returns “No data” NSF: analysis of the month value; if this is empty then analysis the average; if the average is blank then the value is 1 GROWTH: analysis of the month value; if this is empty then analysis the average; if the average is blank then the value is 1 You can find in the excel file attached the formulas that are possible to exist.
View Replies!
View Related
Sum Index And Match Combination
I am trying to sum 12 columns based on looking up a reference that is in one column. Basically I have 2 files where on both files Column A has a G/L account number. On the data file I have credits for each month going from column C to Column O. On the other I have one column where I want to bring in the sum of all the months based on looking up the G/L number in column A.
View Replies!
View Related
Number Of Unique Customers For Every Possible Product Combination
I am looking for an efficient solution to the following problem. I have a sales table with two columns, titled C1 and C2. The first column lists the product sold, and the second column lists the associated customer. Here's what I mean (though I can't figure out how to create neat columns in this post): [C1] [C2] Prod1 CharlieCo Prod3 AlphaCorp Prod2 BetaInc Prod3 BetaInc Prod1 AlphaCopr......................
View Replies!
View Related
Complex Character Combination Validation Code
I really need a validation code for Cell B15. I realize that a macro could do this, but a validation code is what I really would need: Cell B15 can only allow at least one of the following values, or two or more of the following values separated by '&' (Note the spaces between the digits): I I I I I I IV IA I IA I I IA IVA or (some combination examples): IA & I I I I I & I I IA I VA & I IA If the user fails to meet these requirements, then he should get an error message telling him to try again.
View Replies!
View Related
Projected Date Formula From A Combination Of A 2 Cells
I am trying to make the "Next Phase up DATE" cell automatically configure accorfing to the Issue date and the PHASE 1, PHASE 2, PHASE 3 Selection. Phase 1 last for 14 days Phase 2 last for 21 days Phase 3 has no phase up date So if I put 1 July 09 in Issued Date cell and select Phase 1, the "Next phase up date will be 15 July 2009. If 1 July 09 in Issued Date cell and select Phase 2, the "Next Phase up date will be 22 jul 09. If 1 July 09 in Issued Date cell and select Phase 3, the "Next Phase up date cell will be (N/A or something to that nature.) The Doortag portions are refrenced to the upper portions, so I only need the formula for (merged)CDE10 & (merged)CDE32
View Replies!
View Related
Vlookup, Index, Match, Offset, What Combination Should I Use?
I have attached an example s/sheet. Basically this is an excerpt of the data that sits in a pivot table. What I want to do is from another sheet query this data. I don't want to use another pivot table as they are quite hungry in terms of memory and the data source we have is quite large. In essence what I want to achieve is in cell G2 the user enters a code. A function (vlookup?) will then scan column A to find that code. The function then needs to look across and sum the total of Requests and Responses for all the dates. Whilst the dates may change, the number of dates will remain the same. Once it has summed them it needs to return the totals to cells G4 and G5. Additionally it needs to fill in the relevant total (offset?) for the corresponding week as detailed in columns HAH. It seems quite a simple lookup issue but I am not very versed in nested lookups. I have looked around and it seems INDEX woudl do the job but I am at a loss on how to construct this type of function.
View Replies!
View Related
Dificulty Finding The Right Combination For A Complex Lookup.
I am having a difficult time with a look up. It would be very hard to explain so I'll attach a copy of the section of the worksheet that the problem lies on with comments so you can see whats going on. The problem there is a numbered list with a reference number i can't seem to figure out a lookup that will look in the chart above and find the row associated to the reference number and according to how many before it have that reference number find a secondary reference number listed in the column above. The attachment should clear it up.
View Replies!
View Related
Align X Y Data Points On Combination Chart
Following my bosses recent charting attempts involving multicoloured backgrounds, graduated bars, textured boxes, mismatched fonts etc, etc, which frankly showed no information whatever, I was asked to simplify them. I did so, as in the two attachments, but the response is now along the lines of "well, yes, but they aren't very exciting, are they?"
View Replies!
View Related
Complicated different Combinations Of 2 And Summing Data For Each Combination Of 3
I have one last question. is there a way to make this a little more complicated? for every two possible combinations of names, i have a value. is it possible to create a fourth colum, in which the sum from the three values is calculated? for example 12 different letter names (al) after running the code derk sent, i now have al in cells A1:A12 I also have every combination of 3 using these 12 names in columns C D and E take for instance the combination of three names (a, b, c). i have values for ab, ac, and bc in columns G, H and R respectively. can these values be summed together and averaged in the fourth column?
View Replies!
View Related
Variable Rows Per Column/ Automatic Combination Generation
The following table/ code is something which I've been trying to tailor from a previous post so I'm not taking the credit for what I think is some very good code. Unfortunately I can't find the link to it  sorry! Right, I have a number of columns containing a various amount of data entries in each with the first row being the header. I would like to generate all possible combinations of this data in one column, the entries separated by commas, that will eventually be exported as a csv file. The number of columns and number of rows in each column will be changed regularly ...
View Replies!
View Related
Find And Replace A Specific Letter/number Combination
I have a column of references I wish to standardize. Contained within a general text description there is also an orderspecific reference number, which is not relevant for my purposes. I wish to find all of these numbers and replace them with nothing (i.e. retain the rest of the description). The reference numbers are always in the format "P#####/##". Unfortunately these references are in the middle of the text field, not at the start or end, so I can't use a LEFT or RIGHT formula to delete them. Once these reference numbers have been deleted I will then be able to filter for unique records only. When I do this at the moment the filtering has no effect due to these specific reference numbers.
View Replies!
View Related
Counting Items Unique To A Day/customer Combination
I need to identify the number of occasions on which a product type is bought by a customer in isolation from other product types. I have attached a sample to illustrate. The actual data is more complex and is actually medical data concerning issue of oral or IV drugs. There are many thousands of records. To clarify, in the example, there was only one occasion when Bread was bought on its own by a particular customer on a particular day. The way the data is presented, 'Bread' could be listed before 'Milk' or, as with 'Steve' on the 2/4, it could be in the middle of a series of 'Milk' purchases. I can sort by date/name/type, but I cant work out a formula to resolve the count.
View Replies!
View Related
Optimal Combination Model With Constraints & Variables
I need to optimize an Excel model with 5 variables each of which cam be any value from 1 to 20. The solution space, therefore, contains 3,200,000 combinations. I have a software(Palisade Risk) which can give out a random sampling of this solution space. I need to know how many combinations can be considered as a representative sample of this solution space  e.g. can it be 1%?.
View Replies!
View Related
Combination Chart: Present Source Data Differently
I've taken over a spreadsheet and been asked to produce various charts. One chart in particular asks for views of the following (hyperthetical) data from different workbooks on the same sheet July 06  24% June 06  22% May 06  29% April 06  21% overlaid with this data April 06  42 May 06  68 June 06  47 July 06  55 The problem I'm having is that firstly, the dates are presented the opposite way round, and secondly one set of data is in percentages, the other in basic integers. The spreadsheet data is large, and historically has always been done like this so it's not easy to change the way its presented, but is there an easy way to show it in a combination chart?
View Replies!
View Related
Combination List Of 10 Of My Favorite/lucky Numbers That I Want To Play In The Lottery
I have a list of 10 of my favorite/lucky numbers that I want to play in the lottery. The lottery picks 5 numbers total. I need a way to show me all the possible combinations of my 10 numbers picked in a 5 number draw (hope that makes sense). There are no repeat combinations for example I DO NOT WANT 12345 and 54321 to come up as separate combinations so each of my favorite #s needs to be used only once in each combination, and each set used once. I have searched this board for 2 hours now read tons of other posts, but not finding a real solution. The output will be a list of all the possible combinations (no repeats, and no permutations) using my 10 favorite numbers. Another example 12345 12346 12347 12348 12349 12356 12357 and so on. How do I create this? I realize the resulting table will be quite a large number of combinations but we're going to have fun with it and pick a few at random.
View Replies!
View Related
Accumulate The Hours For An Invoice Period And Job Code Combination
My problem is that I have a worksheet tab (RawTimeSheetData) which contains a whole series of week/timecode values for a range of people. I want to accumulate the hours for an invoice period / job code combination. As an example in the tab InvoicePeriodSummaryTimes cell D6 i want to sum all the hours from RawTimeSheetData where both cells A6 & B6 from InvoicePeriod tab = cells D6 & E6 from the rawdata tab.
View Replies!
View Related
Count & Sort Numbers Based On Combination Of Occurences
I have provided an attachment. what I am trying to accomplish. I am trying to have a worksheet that if I input multiple 3 number combinations into the input cell range, after pressing the sort button, it would then sort, rank and count each 3 number combination for me. So as my attached file illustrates, the input cells would be A9:D14. In this sample the ranking consists of cells A19  A31 as the ranking columns. Cells F19  F31 show the counted and sorted results and are ranked accordingly. I need a sort button as illustrated in cell F10 to make the worksheet function after the 3number combinations are inputted in cells A9:D14. How do I get started to make this work? I do not know VBA codes or macros so I will need guidance along the way if this is what is needed. I do have some working knowledge of formulas (e.g. countif, rank, etc.)
View Replies!
View Related
