Show All Possible Combination Of Set Of Numbers?

Feb 10, 2006

I am trying to show the all possible combinations of a set of numbers in Excel, in my case I think permutations are more appropriate to use. For example: there are three numbers 1, 2, 3 I want to show results like:

1, 2, 3
1, 3, 2
2, 1, 3
2, 3, 1
3, 2, 1
3, 1, 2

The functions in Excel available only give the total number, but I want to see these combinations!

IF Formula - Column To Show Text Value Dependent On Combination Of Three Columns

May 12, 2014

I have three columns that each have a drop down of text options; column A has three options, column B has three options and column C has two options. I want to have a fourth column that will show a text value dependent on the combination of the three columns.

It ends up being 18 variations so my IF formula just won't compute.

This is basically the format I have and works for a few values then just chucks up an error when I extend it to all 18 variations:

IF(AND(A1="text",B1="text2",C1="text3"),"show this text",
IF(AND(B1="other",B1="other2",C1="other3"),"then show this", ) etc etc

Any way I can get this to work for all 18 possible outcomes??

Combination Of Numbers

Jun 22, 2007

I have a list of numbers from 1-7 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 1-7 that will complete the sequence 1-7. 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 1-7 and I always need to find every number that can complete the sequence 1-7 by using one number from each row.

Every Possible Combination Of A Group Of Numbers

Apr 26, 2009

The numbers are file attributes, as you know these are

Normal = 0
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.

Find A Combination Of Numbers That Equal A Given Sum

Dec 18, 2007

If I have A1 = 1, A2 = 3, A3 = 5, A4 = 7, and A5 = 9 in column A, Would someone know how to get the combination of numbers in column A that equal 13, or 24 for example?

Using Excel To Determine Combination Of Numbers That Equal Set Total?

Jan 30, 2009

Is there anyway to use Excel to determine which of a set of numbers equals a total? I encounter this issue when dealing with accounts receivable. We receive payments with no details on what is being paid. I have a list of open items and a total being paid. I would like to have Excel determine which combination of invoices can be added together to match the payment.

Count & Sort Numbers Based On Combination Of Occurences

Jan 13, 2008

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.)

Combination- List Of 10 Of My Favorite/lucky Numbers That I Want To Play In The Lottery

Aug 25, 2007

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 1-2-3-4-5 and 5-4-3-2-1 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-
1-2-3-4-5
1-2-3-4-6
1-2-3-4-7
1-2-3-4-8
1-2-3-4-9
1-2-3-5-6
1-2-3-5-7
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.

Only Display Value If Combination In Two Columns Matches Combination In Two Other Columns

Jul 12, 2012

I have a formula that I can't get to paste successfully in the forum - it keeps getting cut off?!? ... but I think I can probably simplify my explanation to get the answer I want anyway.

I need to only show the value from AUS!\$H\$2:\$H\$17 if the C2 & D2 combination are the same as the AUS!\$B\$2:AUS!\$B\$17 & \$AUS!\$C\$2:AUS!\$C\$17 combination.

Show Sum Of Only Numbers Seen Via Autofilter

Jan 7, 2009

I want to have a cell report the sum of a column and have that sum change depending on which values I have filtered on another column. I have a cell reporting a sum of times in column E, but if I filter Column F to show only jobs for 1/16/09, I want that number to display the total time for just those shown. At this point, it just shows the total regardless of what is filtered.

Show Numbers Not Text

Feb 6, 2009

I have over 2000 cells like the following

S304
S205
S204
S306
S207

and so on

Is there anything I can do to show just the numbers and not the S at the start without me having to delete each S individually?

Formula To Show The Numbers When They Are Below 30

Jun 4, 2009

I need a formula to show the numbers as they are when they are below 30, but when they go above 30 I need it to display 30 every time. I have attched a file.

Format To Show Specified Numbers

Aug 29, 2007

I have a number format that I need assistance with:

The number format I need is 1234-000 or 1234-001

The normal number format is 1234-000, very rarely does the 1234-001 be used. So I would like the number format to left fill the left section 1234 and right fill the right section 001.

How To Show Percentages Next To Numbers In Charts

Dec 2, 2013

So I have a bar chart with numbers as its data labels...so each bar is divided into 4 colors equaling to 100% ...how can i show percentages next to numbers in the chart...so for example if a bar equals to 200 and each color is lets say 20, 60, 40, 80 ...i would like to show these numbers as well as percentages..i know we can do this manually by inserting a text box but the percentages might change quite often and i dont want to go through the hassle of calculating and changing them each time.

How To Show Numbers Missing And Listed

Nov 17, 2013

The current method I'm using to get the results I need in Excel are a bit manual and excessive. is a simple all in one formula for what I'm trying to do,

The data is 3 rows. We can also assume the data is in the first three columns starting from A1.

698
885
499

As you can see in the above set from 0 through 9, the numbers that are not listed are 0,1,2,3,7
And As you can see the numbers that are listed are 4,5,6,8,9.

How do I find and show which numbers from 0 - 9 are not in the set.
How do I show the number that are listed in the set Example:
Note: the special characters aren't needed.

698 4,5,6,8,9 --- 0,1,2,3,7
885
499

Note the the evaluation is for every three rows. So the three rows in the example are from a table with 150 rows. Each row begins a new evaluation for the next three.....Example:

698 and the following two starts the next set
885 and the following two starts the next set
499 and the following two starts the next set
and so on ......

VBA Calendar Control Does Not Show Day Numbers

Jul 23, 2008

[url]

you can see we've hit a problem with the Calendar Control object.

I submitted a workbook which has an Activex calendar control embedded on a User Form. On my PC everything appears as you'd expect, with the calendar showing the individual days in the calendar matrix. However when the user loads the form on their system, although they see the calendar as you'd expect, and they can change the month & year at the top of the calendar, they don't see the individual day numbers, 1,2,3 etc. within the body of the calendar.

Grouping Numbers To Show How Many Scores Are Between 30 And 50

Feb 14, 2012

if i had a group of numbers , for example, the scores would be 30, 40 , 50 , 60 , 70 and 80 , how would i group that. like it would show me how many scores are between 30 and 50 , it would show up as 3 , then between , 51 and 60 it would show 1

Show Numbers In Column A If Value Found

Feb 26, 2008

What formula can I use to return the value in column A if the value is found.

Something like:
=LOOKUP(\$M\$1,\$B\$18:\$O\$31,give the value in Column A of the row of where the result is)

Show Plus Sign On Numbers Greater Than X

Aug 9, 2008

I'm trying to write a macro in Excel that would change any number greater than 10 in a spreadsheet to say "+10"

Getting Symbols To Show Up As Numbers In Linked Document.

Oct 8, 2008

I have a linked document, only 2 sheets. On the first sheet there is a column for + or - signs, I need a formula for the second sheet to show the + as a 6 and the - as a 5 instead of just the symbols carrying over.

Show Only The Number Of A Cell Containing Text And Numbers

Nov 23, 2009

Lets say cells A1 to A5 contain these lines of information:

Till 174 (T0215) - till keeps turning itself off.
Till 245 - stuck on windows screen
116 - keyboard is unresponsive
Berkel Scale is constantly beeping
ped not reading cards Till 156

How can I show only the numbers from these cells (i.e. B1 will be 174, B2 will be 245 etc...)? As you can see the number isn't always in the same place, and doesnt always have the same characters either side.

How To Format Numbers To Show Actual First Digit

Aug 5, 2013

How do I format numbers so it will show the actual first digit, and the rest as x?

so that \$1,234.56

will appear as \$1,xxx.xx

or alternatively,

\$1,234,567.89

will appear as \$1,xxx,xxx.xx

I would prefer to do this with custom formatting rather than VBA,

(this is so I can generate sample reports for prospective clients, without showing the real numbers in the tables)

Show Numbers Based On Percentage In Cell

Sep 15, 2007

I have a rather large report full of data, 10+ columns contain a RANKING column, and these ranks, 1-10, are assigned by a % bracket:

1-20% = 1
21-40% = 2
41-60% = 3
61-80% = 4
81-100% = 5
101-120% = 6
121-140% = 7
141-160% = 8
161-180% = 9
181-200% = 10

There are several hundred rows in each column that all need individual ranks. My first thought is to program a if then else statement for 1 to 10 with a % limit range, but this isnt feasable as i would have to program a separate if statement for each of the many hundred rows, or do one heck of a fancy loop that i just cant contemplate (as the rows are split and sepperated... no solid uniform place for the data to be plugged.) What is the best way to go about this? I'm at a total loss of ideas with my knowledge of excel.

Show Numbers Greater Than An Amount In A Pivot Table

Dec 17, 2009

Is it possible to just show numbers greater than an amount in a pivot table?

List Column Numbers Result Show In Textbox

Mar 23, 2008

The list columns 5,7 and 9 has number entries.The userform has 3textboxes.is it possible to link total amount of this numbers result into the textboxes.Like column 5"CZ" entries total will showup in textbox10,column 9"DD" = textbox14

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim a, i As Long, ii As Long, b(), n As Long
ListBox6.Clear
With TextBox2
If .Text = "" Then Exit Sub
If WorksheetFunction.CountIf(Range("cv:cv"), .Text) = 0 Then
MsgBox "No Entry !"
TextBox2 = ("")
Exit Sub..........

Custom Formatting - Show Numbers On Excel Spreadsheet In Thousands

Jun 14, 2013

I want to show numbers on my excel spreadhseet in thousands. However, the challenge is I want to show negtive numbers in red within brackets. For instance, I have a number -104,784,089. I want to display it as (104,784) in red color. Similarly, I want to show 577,090 as 577. Is there a customer formatting I can set up in excel that do that?

Truncate Range Of Numbers Where Number Will Not Show More Than 2 Past The Decimal

Oct 21, 2013

How do I truncate a range of numbers where a number will not show more than 2 past the decimal ....and this be in vba as part of a macro.

Number Formatting :: Show A Variety Of Account Numbers Properly

Jul 22, 2009

I am trying to find the right format that will show a variety of account numbers properly.

I have these types of numbers displaying like this right now:

9.04585E+13 (should be 90458524211818)
785656.001 (showing properly)
NRDE1312089 (showing properly)
SCGA 396.00 (showing properly)

The formatting is set to general, which works for all but the top type of account number. I tried setting to a custom format like "#.#######" ...The only problem with that format is that the "." shows up whether there is a decimal in the number or not. What's the way around that?

Forcing Excel To Show Numbers In Scientific Notation To A Constant Power?

Mar 8, 2013

I need to have all of my number be "x10^-6", but Excel wants to make them, for instance, 7.66x10^-3, when I need it to read "7660x10^-6". How can I force excel to do this?

Show Sum Of Numbers In One Column IF Another Column Has A Specific Category

Apr 28, 2009

I have a worksheet which basically tracks time. the time is reported in Column C. In that row in Column E, there is a validation list with about 6 different categories in it. On the side of this "table" I have a list of all the categories and I want a value to be next to it that reports the sum of time (C) for each category (E).

So for the "Routing" category, I would want the value to be the sum of just data on the timesheet that have "routing" in Column E.