Counting Pairs From 4 Digit Lottery
I am tring to list and count how many pairs in a 4 digit lottery?
no duplicates ...
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Counting Occurrences Of Particular Digit
I have a slight problem...I have a range of values.. 0113 1240 8430 0903 I need to count the occurance of a a particular no. from 09.. So i want to know how many times say 0 appears in that range of values etc so on till 9. I tried using CountIF but the problem i face is lets say in my cell 0903 there are 2 0s inside, it doesnt count this 2 zero..
View Replies!
View Related
Counting The Number Of Times A Multi Digit Number Appears
I've tried everything I know (which isnt that much to be honest. lol). Ive tried the frequency formula but that doesn't work the way I want it  I think its probably the wrong formula to use. I've also tried a pivot table but they always vex me. If a pivot table IS the way to go, could someone talk me through it step by step? (*the wizard is just as confusing as doing it yourself I find) ....
View Replies!
View Related
Seperate 2 Digit Number Into 2 Single Digit Numbers
I know you can take a number from one cell and combine it with number from another cell and make it one number. What I need to do is the reverse. Take a two digit number in a cell and separate it into single digits in two cells. If you have the number 50 in a cell, then is there a formula that will take the 5 and put it in cell and take the 0 and put it in the cell beside it?
View Replies!
View Related
Lottery Number Generator
Does anyone know of an Excel macro or function that can generate say 1000 strings of 6 numbers from 1 to 49, without repeating the same combinations? Kind of like the random picks that lottery machines generate.
View Replies!
View Related
Lottery Board Format
I run the company office pool / lottery. A person selects 6 numbers 149. Twice a week the provincial draw is held. if your number matches the drawn number i would highlight your number on the spreadsheet. When all of your numbers are highlighted, you win. This usually takes 34 draws to cover all your numbers. Ideally i would like to simply enter the drawn numbers & have excel automatically highlight the number next to each persons name if they have selected that number.
View Replies!
View Related
How To Highlight Lottery Numbers
I have 49 lottery numbers split in 3 segment and 5 groups separated by coma In cell Range from B2:F4, I enter 5 numbers from draw result in to cells B9:F9 What I want to highlight the ticket numbers that have been drawn a in lottery, In this example the ticket numbers are in cells B2:F4, and the drawn numbers are entered in cells B9:F9 Example is below A B C D E G1G2G3G4G5Seg12, 5, 6, 8, 915, 1921, 25, 2637, 38, 4042, 45, 48Seg23, 4, 711, 13, 1827, 3047, 50Seg31, 1012, 14, 16, 17, 2022, 23, 24, 28, 2931, 32, 33, 34, 35, 36, 3941, 43, 44, 46, 49Lottery Result Below:57142049
View Replies!
View Related
Conditional Formating On Track Winning Lottery Numbers
Tracking winning lottery numbers using conditional formating on a worksheet.CF only allows 3 formats I need 10 is there a way around this.In cell B2 I choose Conditional Formatting from the Format menu.I select Formula is from the drop down menu,and enter the formula =COUNTIF($N$2:$S$2,B2)>0 Then I hit OK.I then click on the Painter button on the toolbar and apply this formatting to the rest of the lotto numbers.I can do this 2 other times but i need it to work 10 more times,if there is no way around this, is this formula possible in VBA and how do I enter it.
View Replies!
View Related
Lottery Search Query: Correct Sequence Of Numbers
I am setting up a spreadsheet with two columns. The first column will contain a persons name the second column will contain four numbers. Name Numbers Joe 1,3,12,24 John 4,12,23,24 Jill 6,14,19,26 I need to be able to search to find the following: the correct sequence of numbers e.g. if 1,3,12,24 were pulled out then that search is easy. If I was looking for someone who had the numbers 1,12,24 then I have a problem. I was thinking about putting the four numbers in to four separate columns but my Excel knowledge is poor.
View Replies!
View Related
Lottery Number Generation: Create A Single Line Function
You must install "Analysis TookPakVBA" before doing this; check in your Excel's Tools/AddIns menu. Paste this long function (see below) in an Excel's worksheet (any cell) and press F9 (calculate) until you see at least 2 identical numbers. Real life's lottery does not have repeating numbers. I can disable this repetition by using cells relation but I want to use just one (1) cell. Any expert wants to challenge? If not, you can use VBA. But it still requires one (1) line of codes. =RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)
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
Fillin As Pairs
I'm looking for an easy way to fillin 1600 cells, in Col. "A", with pairs of sequential numbers [from 1 to 800]  for example: 1 1 2 2 3 3 4 4 5 5 6 6 I managed to achieve something close with: =INT(ROW()/2.05)+1 but this is not accurate enough.
View Replies!
View Related
Show 3digits Under Pairs
Need formula? We have a pair of numbers in D1,E1,and F1….. We want only the numbers that have 12 from column A and B 66 from column A and B 18 from column A and B only A B C D E F 1 12 66 18 456 123 129 667 481 667 234 123 366 881 .........
View Replies!
View Related
Extract Even/odd Pairs From A Number
I would like to know if its possible to extract just the even/odd pairs from a number. Exp. I have the number 123 in cell A1, and would like to extract the even/odd pair, which in this case would be 13, the cell A2. I would like to be able to do this without the use of "Isodd" or "iseven" functions.
View Replies!
View Related
Matching Pairs Of Data
I'm not sure if it's possible to do this, but I have three lists of data. One is a complete list (for example, the numbers 125). The next list is a subset of the complete list (e.g., 1,3,5,7,9). Attached to these (the subset list) is another list (let's say letters, so A goes with 1, B goes with 3, etc). I want to physically move the paired entries from Lists 2 & 3 so that List 2 matches up with List 1. Let's see if I can represent this visually: I have: 11A 23B 35C 47D 59E 6 7 8 ... 25 I want: 11A 2 33B 4 55C 6 77D 8 ... 25
View Replies!
View Related
Count Pairs In Range
I am trying to analyse a set of variables in my dataset. The variables I have are numbers which are organised into a single column. I want to count the number of times different variable pairings occur within my data. An example dataset of mine is: 1,2,4,2,1,3,5,3,2,1 As the dataset contains 5 variables (1,2,3,4,5) the potential combinations are: 1,1; 1,2; 1,3; 2,1; etc.. with 25 in total. I want Excel to move through this column starting with the first two cells and step down 1 cell each time and then tell me the number of times each of the combination occurs.
View Replies!
View Related
Check For Pairs Occurrences
I am looking for a code which will check and display the Maximum Occurrences of pairs of 2 Characters. In the attached file I tried to write some code but it seems to be wrong. For your convinience, I entered the reuslts in a table (hopefully not mistaken). The code should display a MsgBox saying that "The pair 'ab' was found 6 times in A1. The string, in A1, can be any string in any length.
View Replies!
View Related
Can Work Out Unique Pairs Of Numbers
I need to pair up 20 people in sequence for a workshop, so I number the people and can rotate people numbered 110 with 1120 easy enough, but when it gets to pairing 110 together in pairs and 1120 in pairs. Is there a formula I could use in Excel to work out the easiest pairing for me?
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
Formula Grouping Number Pairs
How do I express the following as a formula for range f2:h2: case 1 if any number is equal to 0 or 5, then 1 if any number is equal to 1 or 6, then 2 if any number is equal to 2 or 7, then 3 if any number is equal to 3 or 8, then 4 if any number is equal to 4 or 9, then 5 example: f2:h2=3,5,9 result 415 f2:h2=0,2,7 result 133
View Replies!
View Related
Matching And Sorting NonIdentical Row Pairs
I have a database with 6 columns in play (there are actually other columns but they are not relevant). I'll call the columns A through F. I would like to be able to match certain counterpart rows together, do a sort placing the counterpart rows adjacent to one another, and then count how many pairs I have. (Some rows will have no counterparts.) Here is a microillustration of the database: ______A______B______C______D________E_____F R1___0103___54____959____nsneakr___24____yes R2___0104___67____454____adidaht____53____yes R3___0110___42____344____calb3wd___11____no R4___0119___67____454____adidaht____53____no R5___0125___54____959____nsneakr___24____yes R6___0202___54____959____nsneakr___24____no R7___0214___54____959____nsneakr___24____no I basically need to devise a formula or script that pairs together two rows that fit the following criteria: 1) The rows are identical in Columns B, C, D, and E. 2) The rows are not identical in Column F (i.e., one half of the pair should have "yes" and the other half should have "no") 3) The rows are as close together as possible according to the date sequence in Column A. For example, Row 1 should pair with Row 6, and Row 5 should pair with Row 7. Row 1 should not pair with Row 7, and Row 5 should not pair with Row 6. **This criterion seems tricky because R5 and R6 would technically fit the requirement for pairing, were it not for the fact that R1 comes earlier in the sequence.**
View Replies!
View Related
Finding The Hypotenuse Of From Two Pairs Of X,Y Coordinates
I have a map with several waypoints and would like to find the distance between any of these two waypoints. Sounds simple, but I have having an issue with the later parts of the excel file. Let me explain further. There are over 100 waypoints on this map we have. Each waypoint has an, seemingly arbitrary, Xcoordinate and YCoordinate. However, if you divide the coordinates by [X], they turn into miles. It should go without saying that finding the distance between two waypoints is rather simple mathematically, but I having an issue figuring out how to pull two random waypoints and having the distance automatically calculated. So far, I just have column A with the waypoint name and with column B and C having the X and Y coordinates respectively. Is there anyway I can create some sort of call function that when two waypoint values are entered it would automatically calculate the distances?
View Replies!
View Related
Converting 2 Digit Year Into 4 Digit Year
I have 2 digit years (98, 99, 00, 01) that I need to convert to 4 digit years (1998, 1999, 2000, 2001). There is one year per cell. If it was simply a matter of adding 19 or 20 to the beginning of each, I could do that. But since there's a combination of both 19 and 20 that needs to be added and there all intermingled, I'm not sure how to do it. Can a rule be written to add 19 to the beginning except if the current cell starts with a 0, then add 20? The highest year is 2008 (no 2010 to deal with). Example: 98 > 1998 99 > 1999 00 > 2000 01 > 2001
View Replies!
View Related
Copying Cells To The Neighbouring Column In Pairs Along Row 1
So I have a spreadsheet that has a Title in Cell A1, then entries in B1, D1, F1, H1, J1, etc... with empty cells between. What I would like to do is copy those entries to the right, i.e. B1 into C1, D1 into E1, F1 into H1, but all the way along because in my master sheet there are a lot of columns.
View Replies!
View Related
Make A Mcaro Loop Through 5 Pairs Of Columns
I've managed to get my macro to work ok  but what I want to do now is to get it to loop through more columns. Currently it looks for email addresses in column AH (which will always be the same) then it looks for a "yes" in columnAB and if there is no "SENT" in column T then it sends an email,after which it adds the word "SENT" to column T. What I want t do is for the macro to then run on columns AC+U, AD+V, AE+W and AF+X. So basically it will loop through one column to the right each time for a total of 5 pairs of columns
View Replies!
View Related
Find Pairs Of Records And Strip Out Remainder
I have one hundred rows of data and within that there are some records which are the same and they will always be in pairs  identifiable by a cell with the same ref common to both records. how to strip out the records that are not part of a pair? I would like remove all of the non pairs and move them to another sheet.
View Replies!
View Related
Estimate Linear Formula From Multiple Coordinate Pairs
I have 32 pairs of coordinates (X,Y). If I graph them using a scatter chart in Excel, I can see that it looks like they suggest a linear relationship between the points. Other than printing the chart and trying to draw a "best fit" line manually, is there some sort of way I can get Excel to come up with the "best" linear formula (y=mx+b format)?
View Replies!
View Related
How To Use One Cell To Match Pairs Of Cells For Missing Items
We use a program at work to check items out to individual people but sometimes an item is missed and it takes forever to manually look for who it is. In the attached example, (keep in mind there might be more than 100 people on this list...this is just two people), the first 2 items are a pair (radio and radio case) and the second 2 items are a pair (laptop and laptop case). Id like a way (condition formatting) to flag a person who has one item from the pair checked out and not the other. (Ive manually highlighted one person who only has one of the paired items...id like this to happen automatically) EDIT:: If the first person were missing the radio in this example, his name would be highlighted on the line with the matching pair...cell D1 (radio case) Other information: Radios will always be RFAMRC*** Radio cases will always be RFAMF**** Laptops will always be RFAMTD*** Laptop cases will always be RFAMTE***
View Replies!
View Related
Nested AutoshapesL: Display Several Pairs Of Boxes, Which Consist Of A Descriptor Box And A Value Box
I have a problem when using autoshapes. Above a display of columns of data, which are of different widths, I need to display several pairs of boxes, which consist of a descriptor box and a value box. If I use formatting in cells, I am constrained to use the width of the columns below. So I will be using autoshape rectangles. No limit on box widths and I can link each one to a cell to send text to them. So far so good. However, I would like to contain a group of these box pairs within another autoshape. When I do this, if I fill the outer autoshape with colour, I have the choice of making the colour solid, which means you can't see the inside box pairs at all, or semitransparent, which allows the boxes to loom through a fog of the outer colour. What I would like is for the inner boxes to show properly, and fill only the space between the inner boxes and the rim of the outer autoshape
View Replies!
View Related
15 Digit Sequence
My question is, a formula was provided to me that add's a comma and a space after a 15 digit sequence of numbers, that formula is: =IF(MID(C378,LEN(C378)1,1)="W",C378&" ,",C378) And what that does is it would change data in a cell that looks like this 010203405W2 TO 020304506W2 To this: 010203405W2 TO 020304506W2 , The formula works great, thanks to the help of the posters! However I just ran into an issue, what if I have a set of data in a cell that looks like this: 010203405W2 TO 020304507W2 020203405W2 TO 020304507W2 030203405W2 TO 020304507W2 And I need a space and a comma at the end of each sequence, so the above would change to this after the formula is applied: 010203405W2 TO 020304507W2 , 020203405W2 TO 020304507W2 , 030203405W2 TO 020304507W2 , Now the data in the cell is wrapped so it lists one set of numbers, then the next set, then the next set as seen above.
View Replies!
View Related
Is A Number Or A Digit
how you can determine if a cel value is a number or a digit It is actually for a column to determine if it has a heading or not and acordingly y have to count the rows off that "current region" and put some values with a formula next to this column. Y was testing an expression with this msg = IIf(target.End(xlUp).Value = "X", "is this a LETTER", "is this a DIGIT")
View Replies!
View Related
Sorting By First Digit Only
I am trying to sort a spreadsheet in descending order of a certain column. I have been able to do so quite easily in the past. However, for some reason, when I am trying to sort it now, it is sorting based only on the first digit of the number in the cell. For example, the order after sorting may end up as 90, 9, 9, 82, 8, 8, 8, 74, etc. I don't know what happened to cause the change, or how to fix it.
View Replies!
View Related
Count How Many Times A Given Date Falls Between Date Pairs
I'm trying to use countif or similar to show how many times a given date is between 2 dates (inclusive). Each row of the spreadsheet contains amongst other info, a Start Date and an End Date. If i've got 2 rows like this: StartDate EndDate 01/01/2009 03/01/2009 02/01/2009 04/01/2009 I'd want to lookup 02/01/2009 and have it return 2 (02/01/09 falls between the dates on row 1 and the dates on row 2). To give some context, this is an export from a database containing staff holiday dates, and I'd like to be able to see how many people will be on holiday on any given date, given the start and end date of all holiday occurances throughout the year.
View Replies!
View Related
Change Last Digit Of A Number To 5 Or 9
The last digit of a number (can be any demonination but always an integer). I know that you can check the last digit of a number with =RIGHT(A1,1) but I would like to check whether the last digit in number is 1,2,3,4,5 and if so change the whole number to a number ending in 5. If the original number ends in 0,9,8,7,6 then I want it to be changed to end in 9. Is this possible and if so is there an excel function that I could use?? e.g. 143 > 145 e.g. 1037 > 1039
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
How To Calculate Check Digit (MOD 10)
How do I create a formula that will calculate a check digit for a 9digit number using MOD 10? The weighting is 212121212. The problem I am having is that if the sum of the product is in double digits, they must be treated as individual digits. Example: Number To Be Calculated: 0 0 4 4 5 5 5 2 5 Weighting: 2 1 2 1 2 1 2 1 2 Sum: 0+0+8+4+1+0+5+1+0+2+1+0 22/10 = 2 r2 102 = 8 (Check Digit) Is it possible to create such a formula? I need to calculate several hundred and do not want to do this manually!
View Replies!
View Related
Get One Cell To Increase By Digit One
How can I get one cell to increase by 1 after I change the date in another cell. The spreadsheet is used to keep track of Oxygen Bottles at the local Fire Dept. and I want the " Times Filled" cell to increase by 1 after the " Date Filled" cell is changed. I'm not sure if they key the date in or if they use a calender addin pop up.
View Replies!
View Related
6 Digit Code To Date.
I have many dates in a column in a 6 digit format such as: 950223 950224 950225 These dates are Feb 23, 1995; Feb 24, 1995 etc. How can I convert these dates in the column to 2/23/1995, 2/24/1995 etc?
View Replies!
View Related
Converting Digit To Hour
I have simple (or not) question: how can I convert a digit that I input into a cell to an hour format ? I want to achieve something like this:  when I input a digit into a cell , for example: 9 a want to convert it to 9:00 (9 hours, 0 minutes). How can I do it ?
View Replies!
View Related
Add/Sum Each Digit In Long Number
I have a cell with a number which is a result of several previous sums. It's a number with 8 to 11 digits. For example: 878567663. I need to add each digit of the number, and then add again the result until I end up with a single digit. In the above example it'd be (8+7+8+5+6+7+6+6+3=56), then (5+6=11), then (1+1=2) and put the end result (2) in a cell My main doubt is that I don't know how to use a simple "do while" type structure in Excel to achieve the above. Questions: 1.) How do I create a formula to do that?. I have seen solutions in this board that let me add each digit but that's it... I need to "reduce" it until only one digit is left... 2.) What about if the lenght of the number is not always the same (fe. in one cell is 8, in another is 11, the next one is again 8...)?
View Replies!
View Related
Two Digit Year Changes On Import
I am importing data from a .csv file. The file contains dates like 02/15/40. The year is 2040, not 1940. Excel doesn't seem to use the Control Panel > Regional Settings change when I open this file. I have my Regional Settings set to 2000 => 2099 to change to a two digit year. If I open a blank worksheet and type in 2/15/40 in a cell...Excel does the correct conversion to 2040. However, when I open a .csv file it considers it 1940.
View Replies!
View Related
12 Digit Number Into Date/time
20081110120000[0:GMT] is there a way to convert many entries of dates and time entered in the above way into actual dates and times? is it as simple as making a custom format? if so, whats the syntax? the entries appear to be the year(4 characters), the month (2 characters), the day of month (2 characters), and the time of day (6 characters, AM/PM) i dont need the time, so the last part ("120000[0:GMT]") isn't necessary.
View Replies!
View Related
