Free Microsoft Excel 2013 Quick Reference

I am quite a novice when it comes to excel. I am attempting to get excel to fill in all the possible permutations of six numbers in multiples of 10 equaling to 100. For example

10 10 10 10 10 10 10 10 10 10

10 10 10 10 10 50 40 30 20 10

10 10 10 10 10 10 10 10 10 10

10 10 10 10 10 10 10 10 10 10

10 20 30 40 50 10 10 10 10 10

50 40 30 40 10 10 20 30 40 50

these are just a few possible sets. All the columns add up to 100

any ideas?

10 10 10 10 10 10 10 10 10 10

10 10 10 10 10 50 40 30 20 10

10 10 10 10 10 10 10 10 10 10

10 10 10 10 10 10 10 10 10 10

10 20 30 40 50 10 10 10 10 10

50 40 30 40 10 10 20 30 40 50

these are just a few possible sets. All the columns add up to 100

any ideas?

- How do you calculate the nth root of a number in Excel 2003?
- How do I isolate the Fractional part of a number in Excel VB?
- Displaying all possible permutation of a number in a given list
- All Possible Combinations/Permutations Of Values
- How to obtain all the possible strings from a table of values.
- Creating combinations of fixed numbers in sets of 5 in excel
- Write a MAcro to choose the highest value of 6 numbers
- The first instance of a number in a string
- Find position of a number in a string
- Find position of a number in a string
- Count no of occurences of a cell in multiple columns and combine them together
- How to show all the possible combination of a set of numbers?
- Create All Possible Combinations Of X Numbers
- Listing possible combinations of numbers
- Solving for all possible combinations of a set of numbers
- Count the number of unique Numbers in a column
- All Combinations From Series Of Numbers
- Inserting an extra 4 zeros to the front of a number in a cell
- Findingpossible combinations of numbers in a column that sum to a particular number
- Find all possible combinations of letters and numbers
- Need way to get a list of all cell combinations in a given range, that equal to target SUM
- Display all combinations from a set of numbers
- Possible Combinations Of A Given Set Of Numbers??
- Macro to define a range of serial numbers in Excel

want to calculate the annalized percentage rate of return from a table to

absolute percentage returns for a series of years, so I nee to be able to

calculate the nth root of the total return + 1, where n is the number of

years.

the fractional part of a number. In other words...

Input: 23.0891467

Output: 0.0891467

The only way I know of to do this is (Result = Input Mod 1). The

problem, is that while the Mod Operator works as a Worksheet Function - when

used as a VB Operator, Excel truncates the fractional portion of the result.

This, of course, makes it useless to me.

I need to know the best way to isolate the fractional portion of a

number (with maximum precision), that will work in Excel VB.

Any help will be appreciated! Thanks!

I am new to this forum and I hope I had posted in the correct section. I appreciate if someone would render me some help regarding my excel application. I need help in 2 areas:

1) I had an Excel Sheet 1 with cell A1 containing 4 number eg. 1234. And In sheet 2 in the same workbook I have a column of 23 datas (they are 4 numbers data (5678,4532,...) in each cell from A1,A2,A3 till A23).

- I need an application to compare sheet1 cell A1 data (1234) with the data in sheet 2. If 1234 or 1234 permutation (eg 3421) is found, display the result (3421) in sheet 1 cell B1.

-If no permutation of 1234 is found, search the 23 datas again for results matching any 3digits (for eg if the list contains 2435 (3 digit the same,1 different then this should also be displayed in the next available row, in cell C1 of sheet 1 and so on)) ( eg if lists have 9423 then 9423 should also be displayed on the next available cell, D1,..)

2) Secondly, I would need help to copy some external data from websites eg www.abc.com/3011. I would need to create a textbox (for user to type the web address) and 2 buttons (1 for copying the data in the website listed on the chatbox and another to copy the next data) The web address (eg www.abc.com/3011) is to be keyed into the text box and when i click on button 1, the data from the web www.abc.com/3011 will be copied into the excel sheet and then when i click on the 2nd button, www.abc.com/3012 (previously is /3011 and when i click on button 2 will copy /3012 and click again will copy /3013, always incremental of 1 when i click button 2)will be copied into my excel program.

Any kind soul please help. Thank you for reading!

I have a mathematical problem.

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?????

VB:If anyone can understand this or modify or even find a different solution, i would be very gratefull.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 Dim j As Long, k As Long, n As Long, p As Boolean Dim s As String, t As Double, u As Double Dim v As Variant, x As Variant, y As Variant Dim dc1 As New Dictionary, dc2 As New Dictionary Dim dcn As Dictionary, dco As Dictionary Dim re As New RegExp re.Global = True re.IgnoreCase = True On Error Resume Next Set x = Application.InputBox( _ Prompt:="Enter range of values:", _ Title:="findsums", _ Default:="", _ Type:=8 _ ) If x Is Nothing Then Err.Clear Exit Sub End If y = Application.InputBox( _ Prompt:="Enter target value:", _ Title:="findsums", _ Default:="", _ Type:=1 _ ) If VarType(y) = vbBoolean Then Exit Sub Else t = y End If On Error Goto 0 Set dco = dc1 Set dcn = dc2 Call recsoln For Each y In x.Value2 If VarType(y) = vbDouble Then If Abs(t - y) < TOL Then recsoln "+" & Format(y) ElseIf dco.Exists(y) Then dco(y) = dco(y) + 1 ElseIf y < t - TOL Then dco.Add Key:=y, Item:=1 c = CDec(c + 1) Application.StatusBar = "[1] " & Format(c) End If End If Next y n = dco.Count Redim v(1 To n, 1 To 3) For k = 1 To n v(k, 1) = dco.Keys(k - 1) v(k, 2) = dco.Items(k - 1) Next k qsortd v, 1, n For k = n To 1 Step -1 v(k, 3) = v(k, 1) * v(k, 2) + v(IIf(k = n, n, k + 1), 3) If v(k, 3) > t Then dcn.Add Key:="+" & _ Format(v(k, 1)), Item:=v(k, 1) Next k On Error Goto CleanUp Application.EnableEvents = False Application.Calculation = xlCalculationManual For k = 2 To n dco.RemoveAll swapo dco, dcn For Each y In dco.Keys p = False For j = 1 To n If v(j, 3) < t - dco(y) - TOL Then Exit For x = v(j, 1) s = "+" & Format(x) If Right(y, Len(s)) = s Then p = True If p Then re.Pattern = "" & s & "(?=(+|$))" If re.Execute(y).Count < v(j, 2) Then u = dco(y) + x If Abs(t - u) < TOL Then recsoln y & s ElseIf u < t - TOL Then dcn.Add Key:=y & s, Item:=u c = CDec(c + 1) Application.StatusBar = "[" & Format(k) & "] " & _ Format(c) End If End If End If Next j Next y If dcn.Count = 0 Then Exit For Next k If (recsoln() = 0) Then _ MsgBox Prompt:="all combinations exhausted", _ Title:="No Solution" CleanUp: Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic Application.StatusBar = False End Sub Private Function recsoln(Optional s As String) Const OUTPUTWSN As String = "findsums solutions" 'modify to taste Static r As Range Dim ws As Worksheet If s = "" And r Is Nothing Then On Error Resume Next Set ws = ActiveWorkbook.Worksheets(OUTPUTWSN) If ws Is Nothing Then Err.Clear Application.ScreenUpdating = False Set ws = ActiveSheet Set r = Worksheets.Add.Range("A1") r.Parent.Name = OUTPUTWSN ws.Activate Application.ScreenUpdating = False Else ws.Cells.Clear Set r = ws.Range("A1") End If recsoln = 0 ElseIf s = "" Then recsoln = r.Row - 1 Set r = Nothing Else r.Value = s Set r = r.Offset(1, 0) recsoln = r.Row - 1 End If End Function Private Sub qsortd(v As Variant, lft As Long, rgt As Long) 'ad hoc quicksort subroutine Dim j As Long, pvt As Long If (lft >= rgt) Then Exit Sub swap2 v, lft, lft + Int((rgt - lft + 1) * Rnd) pvt = lft For j = lft + 1 To rgt If v(j, 1) > v(lft, 1) Then pvt = pvt + 1 swap2 v, pvt, j End If Next j swap2 v, lft, pvt qsortd v, lft, pvt - 1 qsortd v, pvt + 1, rgt End Sub Private Sub swap2(v As Variant, i As Long, j As Long) 'modified version of the swap procedure from Dim t As Variant, k As Long For k = LBound(v, 2) To UBound(v, 2) t = v(i, k) v(i, k) = v(j, k) v(j, k) = t Next k End Sub Private Sub swapo(a As Object, b As Object) Dim t As Object Set t = a Set a = b Set b = t End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Regards

Error#9

I'm trying to solve a simple but tricky task. I have a 5X5 excel table filled with numbers and letters (I've made a screeshot just to give you an idea - click here).

I want to make a list of all the possible combination of these values with two conditions:The same cell should be used only once.Thevalues in the resulting strings have to be close one to another in the original table.

Just to give an idea: the combination 7K1TJ is ok. Combinations like G3ONG and MN7KJ are not ok, since in them there is either a value repeated or values not close to one other in the table.

I'm not interested in the lenght of the combination (but between 3 and 10 it's ok). I would like to have a dinamic model, so that i can use it even if I change the letters or the numbers.

Any tip is really, really welcome!

Marco

The expected output would be a number per cell but if they all have to be in a single cell that will be ok.

I am not VB savvy so I will need full guidance. I do not think this can be done with a formula but happy to use either solution

Thanks

Hguten

I would like to write a macro to pick the highest number from the first set of six numbers on the left column and pick the corresponding value in the right column and put it in a cell. I would like to repeat the same do loop for the following set of six number on the left column and pick the corresponding value in the right column and out it in another cell. If this is possible, I would like to repeat the same n times.

0.032034483327.80.0330355613150.02903125285.90.02903125306.50.034036638301.80.042045259303.30.055059268304.60.0390420263140.040043104303.70.0330355613100.041044182302.20.036038794282.9

PD91-A-1000-1_R1D

How can I find the first instance of a number after the "_"? I want Excel to find the "1" after the "_" and cut it to the end resulting in "1D".

I hope that makes sense.

Thanks!

postcodes that will obviously have different digits.

I want to go from using many FIND functions e.g.,

=FIND(0,A1), then =FIND(1,A2) etc...

to

=FIND(0-9,A1)

what text would I need to put in to replace the "0-9"?

Thanks

postcodes that will obviously have different digits.

I want to go from using many FIND functions e.g.,

=FIND(0,A1), then =FIND(1,A2) etc...

to

=FIND(0-9,A1)

what text would I need to put in to replace the "0-9"?

Thanks

I have following task to do on a huge scale on multiple rows and several columns. Please see the screenshot.

2dh68tu.jpg

Given in the pic, AAA has two entries in MO1 both equal to 1. Their sum is 2.

It also has a single value in MO2. I want to combine sum of the values and put it into a single row for each column.

I hope this is clear otherwise please don't hesitate to reply.

Thanks

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.

Could you please anyone give me some hints?

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!

Thanks do much!

Fei

I was wondering if you have any VBA code that will allow me to enter in a number and it will find all the combinations of those digits starting from the number 1.

For example I would be able to enter in the number 7 and the VBA code would generate all the possible 7 digit numbers using 1 through 7.

i.e. 1 2 3 4 5 6 7

1 2 3 4 5 7 6

1 2 3 4 7 5 6

etc...

I would need each number in its own cell. I would also need the ability to change the number I am looking to get all the combinations for. I believe the number 7 should have roughly 5040 different combinations.

Any help would be greatly appreciated.

Thanks

Right now I'm trying to get a list of all the possible combos of days off during a week. Currently I'm using a series of 1's and 0's to show what days a schedule has off.

Ex: 1011101 that series means Monday/Friday off.

So basically I'm trying to find a way to show all the possible combos of 1's and 0's in a series of 7. Both with two 0's and 3 0's, corresponding to two days off and 3 days off in a week.

I found this bit of code that sounded like it would do what I needed, however trying to execute it produces an overflow error and I'm not sure how to go about fixing that. Any help would be appreciated.

Thanks

Option Explicit Dim vAllItems As Variant Dim Buffer() As String Dim BufferPtr As Long Dim Results As Worksheet ' ' Myrna Larson, July 25, 2000, Microsoft.Public.Excel.Misc Sub ListPermutationsOrCombinations() Dim Rng As Range Dim PopSize As Integer Dim SetSize As Integer Dim Which As String Dim n As Double Const BufferSize As Long = 4096 Worksheets("Sheet1").Range("A1").Select Set Rng = Selection.Columns(1).Cells If Rng.Cells.Count = 1 Then Set Rng = Range(Rng, Rng.End(xlDown)) End If PopSize = Rng.Cells.Count - 2 If PopSize < 2 Then GoTo DataError SetSize = Rng.Cells(2).Value If SetSize > PopSize Then GoTo DataError Which = UCase$(Rng.Cells(1).Value) Select Case Which Case "C" n = Application.WorksheetFunction.Combin(PopSize, SetSize) Case "P" n = Application.WorksheetFunction.Permut(PopSize, SetSize) Case Else GoTo DataError End Select If n > Cells.Count Then GoTo DataError Application.ScreenUpdating = False Set Results = Worksheets.Add vAllItems = Rng.Offset(2, 0).Resize(PopSize).Value ReDim Buffer(1 To BufferSize) As String BufferPtr = 0 If Which = "C" Then AddCombination PopSize, SetSize Else AddPermutation PopSize, SetSize End If vAllItems = 0 Application.ScreenUpdating = True Exit Sub DataError: If n = 0 Then Which = "Enter your data in a vertical range of at least 4 cells." _ & String$(2, 10) _ & "Top cell must contain the letter C or P, 2nd cell is the Number" _ & "of items in a subset, the cells below are the values from Which" _ & "the subset is to be chosen." Else Which = "This requires " & Format$(n, "#,##0") & _ " cells, more than are available on the worksheet!" End If MsgBox Which, vbOKOnly, "DATA ERROR" Exit Sub End Sub Private Sub AddPermutation(Optional PopSize As Integer = 0, _ Optional SetSize As Integer = 0, _ Optional NextMember As Integer = 0) Static iPopSize As Integer Static iSetSize As Integer Static SetMembers() As Integer Static Used() As Integer Dim i As Integer If PopSize <> 0 Then iPopSize = PopSize iSetSize = SetSize ReDim SetMembers(1 To iSetSize) As Integer ReDim Used(1 To iPopSize) As Integer NextMember = 1 End If For i = 1 To iPopSize If Used(i) = 0 Then SetMembers(NextMember) = i If NextMember <> iSetSize Then Used(i) = True AddPermutation , , NextMember + 1 Used(i) = False Else SavePermutation SetMembers() End If End If Next i If NextMember = 1 Then SavePermutation SetMembers(), True Erase SetMembers Erase Used End If End Sub 'AddPermutation Private Sub AddCombination(Optional PopSize As Integer = 0, _ Optional SetSize As Integer = 0, _ Optional NextMember As Integer = 0, _ Optional NextItem As Integer = 0) Static iPopSize As Integer Static iSetSize As Integer Static SetMembers() As Integer Dim i As Integer If PopSize <> 0 Then iPopSize = PopSize iSetSize = SetSize ReDim SetMembers(1 To iSetSize) As Integer NextMember = 1 NextItem = 1 End If For i = NextItem To iPopSize SetMembers(NextMember) = i If NextMember <> iSetSize Then AddCombination , , NextMember + 1, i + 1 Else SavePermutation SetMembers() End If Next i If NextMember = 1 Then SavePermutation SetMembers(), True Erase SetMembers End If End Sub 'AddCombination Private Sub SavePermutation(ItemsChosen() As Integer, _ Optional FlushBuffer As Boolean = False) Dim i As Integer, sValue As String Static RowNum As Long, ColNum As Long If RowNum = 0 Then RowNum = 1 If ColNum = 0 Then ColNum = 1 If FlushBuffer = True Or BufferPtr = UBound(Buffer()) Then If BufferPtr > 0 Then If (RowNum + BufferPtr - 1) > Rows.Count Then RowNum = 1 ColNum = ColNum + 1 If ColNum > 256 Then Exit Sub End If Results.Cells(RowNum, ColNum).Resize(BufferPtr, 1).Value _ = Application.WorksheetFunction.Transpose(Buffer()) RowNum = RowNum + BufferPtr End If BufferPtr = 0 If FlushBuffer = True Then Erase Buffer RowNum = 0 ColNum = 0 Exit Sub Else ReDim Buffer(1 To UBound(Buffer)) End If End If 'construct the next set For i = 1 To UBound(ItemsChosen) sValue = sValue & ", " & vAllItems(ItemsChosen(i), 1) Next i 'and save it in the buffer BufferPtr = BufferPtr + 1 Buffer(BufferPtr) = Mid$(sValue, 3) End Sub 'SavePermutation

Let's say I have 6 sets 2 numbers:

1 2 3 17 18 19

7 8 9 10 11 12

What sort of function can I use to show all the possible combinations, where

each number stays in its own column?

For example, the first combination of 6 would be:

1 2 3 17 18 19

then

1 8 3 17 18 19

then

1 2 9 17 18 19

etc, where the 1 and the 7 stay in column 1, the 2 and the 8 stay in column

2, etc.

TIA!

I have a column where I have record numbers. The record number can end up duplicate in some cells.

PROBLEM_ID

IBM-02204732

IBM-03252238

IBM-03252238

IBM-03335648

IBM-03534918

IBM-03534918

IBM-03862015

IBM-03862015

IBM-12737629

IBM-12737629

IBM-17269762

I would like the get a formulla that can count the number of unique numbers in that column?

I would like to get if possible to get a formulla that summarize the number into a single cell.

This question has probably been asked many times before and I know that I have done it before but my mind is blank at the moment so pllllleeeeaaase help out here?

For each combination, there is a value associated with it. Combinations containing the same number has a value of 0. Therefore, combinations (1,1), (2,2), (33,33) have a value of 0. The other combinations have values corresponding to a table beginning in cell B89.

The numbers in column A and Row 88 are main group numbers. For instance, the combination (1,2) contains the numbers 1 and 2, which are both under the main group number 1. Therefore in the table, the correct value should correspond to the row containing 1 and the column containing 1 (the value is 0). In another example, the combination (2,33) contains the numbers 2 and 33, 2 is under main group 1 and 33 is under main group 15. Therefore in the table, the correct value should correspond to the row containing 1 and the column containing 15 (the value is 255.70).

Is there anyway to perform the action of displaying all the possible combinations of these series of numbers and placing the correct values under the respective combinations either using an Excel formula or VBA?

The file attached displays what I was trying to say in the above paragraphs.

Please Help!!

I need to add an extra four zeros to a number in a cell - in this case an ID number, so that i can do a lookup from another list. Basically what i have is two lists of ID numbers in a field of a database, in one i have the correct display/format, so that a number would look like 000054454545. In the second list however the number is only shown as 54454545, due to differences in the programs which imported them. I would like to know if its possible to use a function or macro in excel to basically insert the four zeros onto the number ie 0000 + 54454545 = 000054454545 so that i can do a lookup of one for the other.

Hope this makes sense.

Thanks,

Mike

I would like to be able to find all the possible combinations of numbers in a column that sum to a particular number.

for example I would like to know what combination of the numbers below add up to 606

253

263

851

90

521

85

in this case there are two possible answers 253 +263 +90, or 521+85

If anyone has any suggestions that would be great.

(also help with just finding the 1st possible answer would also be useful if finding both is too complicated, as there may only be one useful combination in my set of number anyway.

Cheers

The letters should be all capitalized and the combination should be letters and numbers alternately.

This are the examples:

C8L8J1A4

A7C8H2I4

Q8F9H6D9

Q1A9C8J9

Now, what I need, is a list of all 5 cell combinations in a given range (of 8 cells), that equal to (and if possible, also less than) a target SUM.

For example, my target SUM is 23 and I have 8 cells containing numbers ranging from 2 to 7.

I want Excel to give me all the possible combinations automatically, using 5 of the cells.

So let's say I have 8 cells, and they contain the numbers "7, 6, 6, 5, 4, 4, 3, 3". I would like another cell to tell me all the possible combinations of 5 of those numbers, to make 23, which would be "7, 6, 4, 3, 3" or "6, 6, 4, 4, 3" etc.

I have a simple question I think, just not sure how to go about it.

Let's say I have a row of numbers:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

and let's say I want to display on the spreadsheet all the possible combinations of 2 numbers:

1-2, 1-3, 1-4, etc

OR let's say I want to see all the possible combinations of 3 numbers:

1-2-3, 1-2-4, 1-2-5, etc

I have done this manually, but I am guessing there is a better way.

Also

Let's say I have 6 sets 5 numbers:

10 12 13 17 18 19

27 28 29 10 11 12

30 31 32 33 34 35

40 41 42 43 44 45

50 51 52 53 54 55

What sort of function can I use to show all the possible combinations, where

each number stays in its own column?

For example, the first combination of 6 would be:

10 12 13 17 18 19

then

10 28 13 17 18 19

then

10 31 13 17 18 19

etc, where the 10, 27, 30, 40, 50 stay in column 1, the 12, 28, 31, 41, 51 stay in column 2, etc.

TIA!

I need to know the formula (if there is one) for Excel to figure all the possible combinations of a given set of numbers that will add up to a specified sum.

In other words, if I give the following numbers 1, 2, 2, 3, 3, 4, 5, 5, 6, 7, 7 - I want Excel to figure all the possible combinations that will add up to 23, and also SHOW ME those combinations, and not just HOW MANY combinations there are.

i.e. one possible combination is 2, 2, 3, 4, 5, 7

Can this be done? What is the formula? I'm having a hard time figuring it out!!

Desperately,

Jenny

I have a little query which I present to you all for a possible solution. I have an excel workbook with two sheets. First sheet is named “Data” and second is named “Deposit Slip”.

I have a few columns in “Data” in which the details of various banking instruments are input. Column A in “Data” is of serial number whereby every new entry is given a numeric progressive serial number e.g. 5431, 5432, 5433 and so forth.

“Deposit Slip” consists of a bank’s replica of a pay-in slip in which the information from “Data” is gathered using various formulae including VLOOKUP etc. The focal point of all this is the serial number which allows for the data sorting, selection and displaying information from “Data” to “Deposit Slip”.

Problem:

Now the problem is that, everyday almost 50 new entries are made in “Data” and then all the serial numbers are needed to be typed in one be one in “Deposit Slip” and print out is taken after each and every input of serial number in cell 4 of column AC of “Deposit Slip”.

Example:

New entries made in “Data” from serial number 5450 to serial number 5500. Now, I need to input one by one the serial number in Cell 4 of Column AC of “Deposit Slip” so that the formulae will pick up and display information in “Deposit Slip” and then I will print out the sheet. It means that I have to input 50 serial numbers and press print 50 times.

Requirement:

I wish to define a range of serial numbers in “Deposit Slip” like FROM 5450 TO 5500 so that I had to input the range only and all 50 different pay in slips are printed out with a single command. I wish to have macro for that. Unfortunately, I have no expertise in recording macros. Any help will be genuinely appreciated.

No luck finding an answer? You could always try Google.