Counting Pairs From 4 Digit LotteryAug 2, 2008
I am tring to list and count how many pairs in a 4 digit lottery?
no duplicates ...
I am tring to list and count how many pairs in a 4 digit lottery?
no duplicates ...
This is what I need:
Columns B, C, D & E are all populated with 3 digit numbers.
I would like column F to automatically populate with any of the 3 digit numbers that share two numbers, i.e.
F2 might look like this (using 00 as the pair):
F3 might look like this (using 01 as the pair):
701, 051, 110, 001, 120
F4 might look like this (using 12 as the pair):
123, 721, 281, 912, 112, 120
I have a slight problem...I have a range of values..
I need to count the occurance of a a particular no. from 0-9.. 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..
The values in columns b:d range from 000-999. I need a formula that will count the number of digits in B:D that match the 3 values in H1 without counting a digit twice. The existing formula counts a digit twice, like the value in H4.
Excel 2007Worksheet FormulasCellFormulaE2=B2&C2&D2F2=SUM(B2:D2)G2=MOD(F2,10)H2=SUMPRODUCT(--ISNUMBER(FIND(","&$B2:$D2&",",","&H$1&",")))E3=B3&C3&D3F3=SUM(B3:D3)G3=MOD(F3,10)H3=SUMPRODUCT(--ISNUMBER(FIND(","&$B3:$D3&",",","&H$1&",")))E4=B4&
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 9 Replies View Related
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 4 Replies View Related
How to calculate a check digit in excel. The details to calculate this are as follows:
All variables in the calculation are positive integers.
We take each integer of the pro number and multiply it by a value and sum them to get a total.
An Example: 8 Digit Pro Number: 66988757
Pro Number: 6 6 9 8 8 7 5 7
Position in the Pro Number: 1 2 3 4 5 6 7 8
Multiply each digit in the pro number by (10-position) in the number, to achieve a sum.
Using our example pro again:
6*(10-1) + 6*(10-2) + 9*(10-3) + 8*(10-4) + 8*(10-5) + 7*(10-6) + 5*(10-7) + 7*(10-8)
6*9 + 6*8 + 9*7 + 8*6 + 8*5 + 7*4 + 5*3 + 7*2
54+ 48+ 63+48+40+28+15+14 = 310
Take the Sum of the previous calculation and divide it by 11
310/11 = 28
(Actually, it's 28.181818, but since we're working with integers, we truncate everything behind the decimal).
Figure a remainder by multiplying the quotient by 11 and subtracting from the sum.
Remainder = 310 - (28*11) ---> Remainder = 310 - 308 ----> Remainder = 2
Check Digit = 11- Remainder
Check Digit = 11 - 2 ---> Check Digit = 9
Note: if the check digit is 10 or 11, need to subtract 10 from the Check Digit.
I run the company office pool / lottery. A person selects 6 numbers 1-49. 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 3-4 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 9 Replies View Related
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.
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
I need a vba that can generate all set of 6 number combinations with the total sum of 100.
For example: 3, 6, 4, 15, 35, 37 = 100.
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 9 Replies View Related
I am trying to set up a lottery spreadsheet, where I enter the results and matching numbers would be highlightedView 9 Replies View Related
I am setting up a spreadsheet with two columns. The first column will contain a persons name the second column will contain four numbers.
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.
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-
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.
You must install "Analysis TookPak-VBA" before doing this; check in your Excel's Tools/Add-Ins 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)
I have a column of 6 digit numbers in excel, and I need to remove the last digit from each number, turning it into a 5 digit number. No rounding, just simply remove the last digit. Each number is different. Does anyone know how to quickly and efficiently remove the last digit from each number? I can convert to alphanumeric string if need be...View 4 Replies View Related
I need a macro where I can highlight a column and change all of the 2 digit years to a 4 digit year (actually, some of the 2 digit years are only 1 digit, e.g. "9" instead of "09").
I have an if then statement that I can use in the column after it, but I'd rather change the actual numbers in the original column, rather than adding another column (and having to keep the original, too).
The statement I had was:
I just copy dragged down to get the cell numbers for the rest of the column... but using A1 was just for an example, here, it's not necessarily going to be in that column. It needs to be just whichever cells I select.
It seems like it should be pretty simple, but I don't know how to word it in a macro.
Starting Column Example:
For some reason I can't get rid of the borders...
I'm looking for an easy way to fill-in 1600 cells, in Col. "A", with pairs of sequential numbers [from 1 to 800] - for example:
I managed to achieve something close with: =INT(ROW()/2.05)+1 but this is not accurate enough.
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 1-25).
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 want to store a list of strings in an array with their position so that I can sort them alphabetically and then be able to put them back in their original order again e.g.
So when they are alphabetized (is that a word?) they become
My plan was to store them in an array, but I can't think how to do it. In python, I would just make a list of tuples, what is the best way to do this in VBA?
I need to complete what would seem a fairly simple incrementing task but I'm not getting the results that I'm looking for. I don't know much about macros so would prefer not having to use one but I'm willing to try if it's not too complicated.
I'm using Excel 2010 on a PC. For several projects, I need to increment cells that contain text and numbers. The column contains data in a cell (A1), followed by a blank cell (A2), then cell A3 contains new data followed by a blank in A4, etc. What I need to do is 1) copy the exact data in A1 to A2 and then 2) increment the number used in A1/A2 by 1 in A3, copy that into A4, and the pattern repeats down the column.
Here is an example of what I have in column A:
This is what I want as a result.
Do I need to save the data in the column as text, general, or numbers? Is there some function that would make incrementing the data possible as I've outlined above?
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.
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
12 66 18
129 667 481
123 366 881 .........
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.
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.
I am trying to figure out a function that creates currency pairs, for example EUR/USD etc based on a limitation.
I attach you a workbook with the details. The first step I have accomplished, I am having trouble to the second step as you will see in the sheet.
I have a long master list of registered members, column C has last name, column D has join date.
Now I have a short list of last names with join dates.
I want to compare the short list with the master list to find names that are already there, by comparing the last name and join date.
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 micro-illustration of the database:
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.**
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, X-coordinate and Y-Coordinate. 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?
How do I express the following as a formula for range f2:h2:
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
I need to pair up 20 people in sequence for a workshop, so I number the people and can rotate people numbered 1-10 with 11-20 easy enough, but when it gets to pairing 1-10 together in pairs and 11-20 in pairs.
Is there a formula I could use in Excel to work out the easiest pairing for me?
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
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 ????
I have a spreadsheet with 14, or maybe 16, or maybe 40 names in column B.
A1 = 1 B1 = JOE CITIZEN
A2 = 2 B2 = JANE PERSON
A17 = 3 B17 = JACK DOE
and so on...
I want to randomly assign these names to pairs using a macro button, and basing it on the number of players (C1)
So if C1 is 12, I'd like the function behind the macro button to come up with 6 * 2 numbers (2 and 11, or 4 and 7,
all completely random, but within the 12 specified in C1) and write these random values in Range D1:E6
(if C1 is 18, then the range to write in would expand to D1:E9).
I don't know enough about writing code to be able to pull this off .
I have created a table that has working hours of staff members over many weeks. Week number as column headings (1 to 52) and staff name as Row headings. E.g a row may be
John Smith, 37, 37, 37, 37, 64 (commas to show seperate cells)
How would I go about using conditional formatting so that the formatting changes according to the sum of the values in each pair of cells?
I need to add the total hours of every two weeks for some staff and change the fill colour of both cells accordingly to highlight which weeks staff have worked too many/few hours.
So (B1+C1) would be a pair, the total would decide which fill colour is used on both B1 and C1, and then (D1+E1) would be the next pair and so on.
I have tried using 'a formula to determine which cells to format' and placing =(B1 + C1) = 74 and making it fill the cells green but this appears to be doing (B1+C1) as the first pair and then (C1+D1) as the second and changing the format for the first cell only.
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 columnsView 2 Replies View Related
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.
Is there any way to find pairs of numbers from lotto data ,starting from the highest to the lowest for the full 24 pairs? It does not matter if excel formula or Vb code.T
here small example;
DATARESULT20 27 28 34 43 44PairsCount13 14 18 32 34 4927, 34207 12 27 34 41 465, 47213 24 25 26 37 40 29, 38206 16 22 26 30 3426,37209 15 17 29 30 3915,33104 15 17 26 37 419,22115 19 25 36 44 4510 16 29 34 37 4711 15 32 33 37 3902 03 06 41 44 45 23 28 29 37 38 4205 07 09 10 22 4704 29 31 36 38 4804 17 20 26 29 3605 26 43 46 47 4906 07 29 32 41 46 11 27 46 47 48 4902 03 18 30 34 3503 19 32 40 47 4805 30 31 33 35 45
A:2007 Email Addresses
>6000 more email address9000 more email addresses)
I need to match up & clearly highlight the pairs (duplicates) in each coloumn.
What's the trick for this?
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 2 Replies View Related
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)
Radios will always be RFAMRC***
Radio cases will always be RFAMF****
Laptops will always be RFAMTD***
Laptop cases will always be RFAMTE***