Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced - Search and Update
I have the below data in the range A2:V19. I could not post the
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,4 4,51,54,58,60,63,65,69,73,76 2,38718.5694444444,1,4,8,10,11,14,18,28,32,37,38,4 6,48,50,52,56,60,63,67,73 3,38719.5694444444,1,4,8,24,26,35,37,38,40,42,45,4 7,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,6 4,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,5 3,54,57,58,60,62,66,75,78,79 14,38723.6319444444,1,13,19,21,22,23,31,36,39,42,4 4,50,52,54,55,63,65,70,71,76 15,38718.6527777778,2,15,20,26,27,28,36,42,46,47,5 0,62,66,68,70,71,72,74,75,76 16,38719.6527777778,2,15,20,26,27,28,36,42,46,47,5 0,62,66,68,70,71,72,74,75,76 17,38720.6527777778,7,16,17,19,24,25,27,28,37,41,4 8,49,52,55,57,58,60,61,63,77 18,38721.6527777778,1,2,7,8,11,26,28,39,41,43,45,4 6,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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Search Capability | Excel Discussion (Misc queries) | |||
Advanced search | Excel Discussion (Misc queries) | |||
Advanced filter search | Links and Linking in Excel | |||
advanced search | Excel Worksheet Functions | |||
Advanced Search | Excel Programming |