Help Please
- Thread starter bloubul
- Start date
Frank
Member
It would help, Bloubul if you would tell us what you are trying to achieve, and specifically what is in cell A2 and cell B1. I know you are adding 1 to a counter (draw number ??) when the date and time NOW is more than a previously stored value. Isn't Now always going to be greater than the last time you checked anyway ? 
.
What is significant about 1258? The formula you supplied has no upper limit, it only depends on what is in cell B1.
.What is significant about 1258? The formula you supplied has no upper limit, it only depends on what is in cell B1.
bloubul
Member
I'm looking for a macro to compare two sets of numbers. Here are the info.
Columns B1:Y1 are headings. The results must be placed in column S2:Y2 in the same position as in the sets. e.g. Both sets have #3 in second place than it must be placed second.. etc..etc
1 Set. Columns B2:G2
4 9 10 17 30 40
3 13 32 42 45 48
8 11 14 26 39 46
5 22 27 35 37 49
7 11 12 26 41 48
3 22 33 40 42 45
14 22 25 27 31 46
1 23 31 36 44 46
3 20 38 40 44 49
6 13 17 19 22 31
4 7 9 22 42 44
9 30 32 33 41 43
6 7 27 28 46 47
2 6 12 40 43 49
19 26 28 32 41 45
3 4 32 35 37 41
11 16 28 35 44 48
16 21 22 34 40 49
13 21 25 33 34 35
2nd Set Columns J22
14 17 22 39 42 43
12 24 26 27 31 41
3 5 13 19 34 42
3 34 37 43 46 48
7 14 17 22 35 39
7 14 16 30 31 42
5 11 17 24 35 44
1 6 30 32 37 39
2 10 19 22 38 43
3 11 15 23 33 43
4 5 18 34 39 43
14 16 17 21 43 48
2 13 14 27 34 39
6 13 17 22 26 32
9 21 22 31 40 43
4 11 12 13 26 46
1 6 9 19 25 30
8 14 21 24 41 48
14 15 18 24 31 34
Thank
BlouBul
Columns B1:Y1 are headings. The results must be placed in column S2:Y2 in the same position as in the sets. e.g. Both sets have #3 in second place than it must be placed second.. etc..etc
1 Set. Columns B2:G2
4 9 10 17 30 40
3 13 32 42 45 48
8 11 14 26 39 46
5 22 27 35 37 49
7 11 12 26 41 48
3 22 33 40 42 45
14 22 25 27 31 46
1 23 31 36 44 46
3 20 38 40 44 49
6 13 17 19 22 31
4 7 9 22 42 44
9 30 32 33 41 43
6 7 27 28 46 47
2 6 12 40 43 49
19 26 28 32 41 45
3 4 32 35 37 41
11 16 28 35 44 48
16 21 22 34 40 49
13 21 25 33 34 35
2nd Set Columns J2
214 17 22 39 42 43
12 24 26 27 31 41
3 5 13 19 34 42
3 34 37 43 46 48
7 14 17 22 35 39
7 14 16 30 31 42
5 11 17 24 35 44
1 6 30 32 37 39
2 10 19 22 38 43
3 11 15 23 33 43
4 5 18 34 39 43
14 16 17 21 43 48
2 13 14 27 34 39
6 13 17 22 26 32
9 21 22 31 40 43
4 11 12 13 26 46
1 6 9 19 25 30
8 14 21 24 41 48
14 15 18 24 31 34
Thank
BlouBul
Frank
Member
bloubul said:I'm looking for a macro to compare two sets of numbers. Here are the info.
Columns B1:Y1 are headings. The results must be placed in column S2:Y2 in the same position as in the sets. e.g. Both sets have #3 in second place than it must be placed second.. etc..etc
1 Set. Columns B2:G2
4 9 10 17 30 40
3 13 32 42 45 48
8 11 14 26 39 46
5 22 27 35 37 49
7 11 12 26 41 48
3 22 33 40 42 45
14 22 25 27 31 46
1 23 31 36 44 46
3 20 38 40 44 49
6 13 17 19 22 31
4 7 9 22 42 44
9 30 32 33 41 43
6 7 27 28 46 47
2 6 12 40 43 49
19 26 28 32 41 45
3 4 32 35 37 41
11 16 28 35 44 48
16 21 22 34 40 49
13 21 25 33 34 35
2nd Set Columns J2
14 17 22 39 42 43
12 24 26 27 31 41
3 5 13 19 34 42
3 34 37 43 46 48
7 14 17 22 35 39
7 14 16 30 31 42
5 11 17 24 35 44
1 6 30 32 37 39
2 10 19 22 38 43
3 11 15 23 33 43
4 5 18 34 39 43
14 16 17 21 43 48
2 13 14 27 34 39
6 13 17 22 26 32
9 21 22 31 40 43
4 11 12 13 26 46
1 6 9 19 25 30
8 14 21 24 41 48
14 15 18 24 31 34
Thank
BlouBul
You don't need a macro, just formulas, Firstly your column structure is impossible, Six numbers starting In B2 does fit cols B:G, but six numbers starting in J2 can only go in cols J:O. If you are comparing number for number, position for position (you don't say) then the results need six columns. Six columns starting with S2 can only go in cols S:X.
Try this. In cell S2 enter the formula =IF(J2=B2,B2,""), copy the formula across to X2. Now copy the whole range S2:X2 down to row 20.
Job done, if that's what you wanted.
bloubul
Member
Frank.
Once again I tip my hat to you, thank for pointing out my typo. The formula does exactly what I want. Here comes the BIG part. Now I going to put the date in column A2 till where it ends, now in column X2 till where it goes, I want to now the number of days in between dates when there was similarities
BlouBul
Once again I tip my hat to you, thank for pointing out my typo. The formula does exactly what I want. Here comes the BIG part. Now I going to put the date in column A2 till where it ends, now in column X2 till where it goes, I want to now the number of days in between dates when there was similarities
BlouBul
Frank
Member
Bloubul I worry about your logic in using the function NOW() in your spreadsheet. Surely, the two sets of results we are comparing happenned in the past on fixed dates. If we are looking for the number of days between when 2 numbers matched between 2 sets of past results, please explain to me what NOW has to do with it ? If you compare them today, or in a month, or in ten years the days between the fixed dates will always be the same. I feel that there is something you are not telling me. What is it ? As I have got older, I have found my powers of mind reading over thousands of miles have declined somewhat, so you are actually going to have to tell us what you are trying to achieve, and this is the second time I have asked. 
bloubul
Member
Frank
I trust that this will help you understand what I'm looking for.
https://www.mediafire.com/?lsnx6c11jxonf26
BlouBul
I trust that this will help you understand what I'm looking for.
https://www.mediafire.com/?lsnx6c11jxonf26
BlouBul
Frank
Member
Bloubul, Having seen your spreadsheet, I conclude that dates are irrelevant because its a daily draw and therefore the skip between matches (draw against draw) is numerically the same (in days) as it is in skips. Why you kept saying "now" escapes me, as now is irrelevant from what I've seen.
There may be a more elegant way of doing this in a single column, but I'm using two columns.
To replicate your example using formulas, (and correct your mistakes)
try this:- In cells Y3 and Z3 enter zero .
In cell Z4 enter =IF(COUNTIF(S3:X3,">0"),0,Z3+1)
copy this down to the bottom of the range, to cell Z51.
In cell Y4 enter =IF(COUNTIF(S4:X4,">0")=0,"",Z4+1)
copy this formula down to the bottom of the range to cell Y51
The column Z is just a counter column which you can format in pale grey or hide the column if you don't wish to see it.
This replicates your example, I hope its doing what you asked.
There may be a more elegant way of doing this in a single column, but I'm using two columns.
To replicate your example using formulas, (and correct your mistakes)
try this:- In cells Y3 and Z3 enter zero .
In cell Z4 enter =IF(COUNTIF(S3:X3,">0"),0,Z3+1)
copy this down to the bottom of the range, to cell Z51.
In cell Y4 enter =IF(COUNTIF(S4:X4,">0")=0,"",Z4+1)
copy this formula down to the bottom of the range to cell Y51
The column Z is just a counter column which you can format in pale grey or hide the column if you don't wish to see it.
This replicates your example, I hope its doing what you asked.
bloubul
Member
I need help again.
Here are the original file from GillesD, but for some reason the macro's won't work any more, will some one please fix it for me.
https://www.mediafire.com/?6y4rypicb3dfb3m
BlouBul
Here are the original file from GillesD, but for some reason the macro's won't work any more, will some one please fix it for me.
https://www.mediafire.com/?6y4rypicb3dfb3m
BlouBul
Frank
Member
Bloubul, there is nothing wrong with the macros. When a macro which has worked perfectly for years gives an error message of the type "subscript out of range" (meaning too big a number or too small a number is being processed), it is telling you that there is a problem with your DATA.
In particular I suggest you look at the folllowing Draw numbers :-
1933
2299
2664
3029
3393
Also when the error appeared, the row number it was working on when it encountered the error was shown on the NAME BOX top left hand side of the spreadsheet. It showed the row numbers of those draws each time it failed. That's how I found them. I think you will find an obvious problem when you check those draw numbers. Let me know what you find.
In particular I suggest you look at the folllowing Draw numbers :-
1933
2299
2664
3029
3393
Also when the error appeared, the row number it was working on when it encountered the error was shown on the NAME BOX top left hand side of the spreadsheet. It showed the row numbers of those draws each time it failed. That's how I found them. I think you will find an obvious problem when you check those draw numbers. Let me know what you find.
Frank
Member
One way this WILL fail is when you have a large number of draws, and it tries to write up to 211,876 quads to an OLD Excel 97, Excel 2000, Excel 2002 and Excel 2003 spreadsheet which only supports 65536 rows. It runs out of rows.
Also the counters in formulas on the pairs, triples, quads and pentas sheets in cells on row 1 only count up to row 65536. It wont matter for the others but the quads and pentas sheet counters should have 1048575 in their formulas instead of 65536. e.g cell G1 =COUNT(A$1:A$1048575) if you want to count all the quads correctly. Even better, and simpler =COUNT(A:A) etc, will count them all.
Also the counters in formulas on the pairs, triples, quads and pentas sheets in cells on row 1 only count up to row 65536. It wont matter for the others but the quads and pentas sheet counters should have 1048575 in their formulas instead of 65536. e.g cell G1 =COUNT(A$1:A$1048575) if you want to count all the quads correctly. Even better, and simpler =COUNT(A:A) etc, will count them all.
bloubul
Member
Hi All
I have the following numbers in colonm A1. 1 2 3 4, I want to create
16 rows with number 1 as the key number with numbers 2 3 and 4 in
random order than number 2 as the key number than 3 and 4, in groups
of 4 than 6 than 8 than 10 than 12 and 14.
Will some one please help me.
BlouBul
I have the following numbers in colonm A1. 1 2 3 4, I want to create
16 rows with number 1 as the key number with numbers 2 3 and 4 in
random order than number 2 as the key number than 3 and 4, in groups
of 4 than 6 than 8 than 10 than 12 and 14.
Will some one please help me.
BlouBul
Frank
Member
Hi All
I have the following numbers in colonm A1. 1 2 3 4, I want to create
16 rows with number 1 as the key number with numbers 2 3 and 4 in
random order than number 2 as the key number than 3 and 4, in groups
of 4 than 6 than 8 than 10 than 12 and 14.
Will some one please help me.
BlouBul
i am struggling to understand what you are asking for, there are so many questions I dont know where to start.
Firstly there's is no such column as A1 . That is a cell.
So where is number 1, where is number 2, where is number 3 etc??
16 rows starting with what number? If it's number 1 are the others 2,3,4 in random order ? There are bound to be repeats of the same combination in 16 rows. Is this what you wanted? Do you want FIXED NUMBERS or numbers that Change when you press F9 or run a macro to change them?
What do you mean by a group of 4 ?? .......4 what?
what do you mean by a group of 10??? ..... 10 what ?
Etc.
etc,.
etc.
in short you need to start again from the beginning being much more explicit as to what you want, where it is, what you mean by a group, giving examples of how the layout might look. It's impossible to help you at the moment.