Advanced - Search and Update
Maxi
worksheet because it keeps on telling me that the generated html code
size is over the maximum characters size of MrExcel message board.
Please use Data->Text to Colums to seperate it. After seperating, set
the column B format to "m/d/yyyy h:mm"
1,38718.5486111111,14,15,16,22,29,31,38,39,40,41,44,51,54,58,60,63,65,69,73,76
2,38718.5694444444,1,4,8,10,11,14,18,28,32,37,38,46,48,50,52,56,60,63,67,73
3,38719.5694444444,1,4,8,24,26,35,37,38,40,42,45,47,49,52,56,59,67,70,71,75
4,38718.5902777778,1,14,18,20,22,25,26,29,31,32,39,49,50,52,54,58,66,69,70,74
5,38719.5902777778,2,4,14,26,29,32,36,38,41,42,43,45,46,59,66,72,73,74,77,78
6,38720.5902777778,3,7,15,17,19,28,29,33,34,45,46,48,57,61,66,68,70,72,73,74
7,38718.6111111111,3,12,15,16,19,22,23,26,29,31,34,35,49,56,61,62,67,68,72,80
8,38719.6111111111,5,7,15,21,24,25,35,40,42,43,52,56,59,61,62,65,67,72,74,75
9,38718.6319444444,6,8,9,10,11,25,34,44,48,56,57,64,69,72,73,74,76,78,79,80
10,38719.6319444444,2,4,7,10,13,16,17,21,27,28,37,52,63,66,67,71,76,77,78,79
11,38720.6319444444,12,14,15,19,20,27,30,34,42,43,46,52,55,59,60,66,68,70,73,77
12,38721.6319444444,3,8,9,16,17,20,21,23,30,33,34,35,36,46,59,64,65,66,75,78
13,38722.6319444444,7,12,15,25,26,35,38,39,47,49,53,54,57,58,60,62,66,75,78,79
14,38723.6319444444,1,13,19,21,22,23,31,36,39,42,44,50,52,54,55,63,65,70,71,76
15,38718.6527777778,2,15,20,26,27,28,36,42,46,47,50,62,66,68,70,71,72,74,75,76
16,38719.6527777778,2,15,20,26,27,28,36,42,46,47,50,62,66,68,70,71,72,74,75,76
17,38720.6527777778,7,16,17,19,24,25,27,28,37,41,48,49,52,55,57,58,60,61,63,77
18,38721.6527777778,1,2,7,8,11,26,28,39,41,43,45,46,53,58,60,66,72,75,76,78
Following data in the range X2:AG7
44,51,58,60,63,69,73,76
4,8,10,37,38,52,56,60,67
4,15,21,28,29,52,61,66,73,74
61,68
11,13,44,63,76
2,20,26,36,45,46,56,62,75,76
Following data in the range AI2:AI7
13:10
13:40
14:10
14:40
15:10
15:40
Following data in the range AK1:AU7. Here value in range AK1:AU1 is the
heading (0,1,2,3,4,5,6,7,8,9,10)
0,1,2,3,4,5,6,7,8,9,10
-,-,-,-,-,-,-,-,1,X,X
-,-,-,-,-,-,-,1,-,1,X
-,-,-,-,1,1,-,1,-,-,-
-,1,1,X,X,X,X,X,X,X,X
3,-,-,2,1,-,X,X,X,X,X
1,-,-,-,-,-,1,-,1,-,1
I want to calculate values in the range AK2:AU7 automatically through
vba.
For instance:
Lets take the first set (Range X2:AE2). The corresponding time given on
the right side is 13:10 (cell AI2). What I want to do now is, scan
these 8 numbers in the first set in all the data in the range B2:V19
where the time is 13:10. The data includes only one row of the time
13:10 and all 8 numbers matched once, hence the value "1" in the cell
AS2.
Lets take the fifth set (Range X6:AB6). The corresponding time given on
the right side is 15:10 (cell AI6). What I want to do now is, scan
these 5 numbers in the fifth set in all the data in the range B2:V19
where the time is 15:10. There are 6 rows in the data where the time is
15:10. If I compare these 5 numbers in all the 6 rows, I will come to
know that three rows does not match a single number, hence the value
"3" in cell "AK6". There were two rows where 3 numbers matched, hence 2
in "AN6" and one row matched 4 numbers, hence the value 1 in cell
"AO6".
This is a sample data, my real data includes 2000+ rows in the columns
A:V. There will be a maximum of 1500 records in the columns X:AG with
times ranging from 13:10 to 20:40.
Thanks
Maxi