Hi all, I have been lurking around these forums to see how to properly ask a question... So here goes...
For example, I have this data set named below and I want to match some values to columns 'ID1' , 'ID2' and 'ID3'. These values are called 'Alarm' with values '2103, 2107, 2805, 2152, 2810...'. After matching 1 of the value, it will copy the data of the same rows of columns 'Data1.Direction[0]' , 'Data1.Count[0]' , 'Data1.Preiod[0]' , 'Data2.Direction[0]' , 'Data2.Count[0]' , 'Data2.Preiod[0]'. It should copy data from its row of the matched ID as well as 9 more rows above (so total 10 rows). It will then create a new sheet named 'Alarm' and paste the data there side by side without empty columns.
The end product should look like below,
And if possible, I hope that the code can in a way that I am able to change code for the searched values (2103 can be changed to 8181) and the name of the copied data (Data1.Direction[0] can be changed to something else like Port[1]. This is because I want to reuse the code for doing the same procedure but different searched values and copied data.
Hope this isnt too much too ask... thanks all.
For example, I have this data set named below and I want to match some values to columns 'ID1' , 'ID2' and 'ID3'. These values are called 'Alarm' with values '2103, 2107, 2805, 2152, 2810...'. After matching 1 of the value, it will copy the data of the same rows of columns 'Data1.Direction[0]' , 'Data1.Count[0]' , 'Data1.Preiod[0]' , 'Data2.Direction[0]' , 'Data2.Count[0]' , 'Data2.Preiod[0]'. It should copy data from its row of the matched ID as well as 9 more rows above (so total 10 rows). It will then create a new sheet named 'Alarm' and paste the data there side by side without empty columns.
| FYP.xlsx | |||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |||
| 2 | 0 | 0 | 2111 | 60 | 1 | 13649 | 5969 | 1 | 13198 | 5938 | |||||||||
| 3 | 0 | 0 | 0 | 59 | 1 | 13664 | 5966 | 1 | 13210 | 5944 | |||||||||
| 4 | 0 | 0 | 0 | 58 | 1 | 13680 | 5969 | 1 | 13222 | 5960 | |||||||||
| 5 | 5640 | 0 | 0 | 57 | 1 | 13695 | 5965 | 1 | 13234 | 5957 | |||||||||
| 6 | 0 | 0 | 0 | 56 | 1 | 13709 | 5964 | 1 | 13245 | 5954 | |||||||||
| 7 | 0 | 0 | 0 | 55 | 1 | 13724 | 5968 | 1 | 13257 | 5952 | |||||||||
| 8 | 0 | 0 | 0 | 54 | 1 | 13739 | 5969 | 1 | 13269 | 5957 | |||||||||
| 9 | 0 | 0 | 0 | 53 | 1 | 13753 | 5988 | 1 | 13281 | 5952 | |||||||||
| 10 | 0 | 0 | 0 | 52 | 1 | 13767 | 5980 | 1 | 13292 | 5951 | |||||||||
| 11 | 0 | 0 | 0 | 51 | 1 | 13781 | 5979 | 1 | 13304 | 5950 | |||||||||
| 12 | 0 | 0 | 0 | 50 | 1 | 13795 | 5977 | 1 | 13316 | 5953 | |||||||||
| 13 | 0 | 0 | 0 | 49 | 1 | 13809 | 5981 | 1 | 13328 | 5970 | |||||||||
| 14 | 0 | 0 | 0 | 48 | 1 | 13822 | 5985 | 1 | 13339 | 5968 | |||||||||
| 15 | 0 | 0 | 0 | 47 | 1 | 13836 | 5975 | 1 | 13351 | 5961 | |||||||||
| 16 | 0 | 0 | 0 | 46 | 1 | 13849 | 5978 | 1 | 13363 | 5969 | |||||||||
| 17 | 0 | 0 | 0 | 45 | 1 | 13862 | 5981 | 1 | 13374 | 5965 | |||||||||
| 18 | 0 | 2152 | 0 | 44 | 1 | 13875 | 5998 | 1 | 13386 | 5969 | |||||||||
| 19 | 0 | 0 | 0 | 43 | 1 | 13888 | 5998 | 1 | 13398 | 5965 | |||||||||
| 20 | 0 | 0 | 0 | 42 | 1 | 13900 | 5989 | 1 | 13410 | 5964 | |||||||||
| 21 | 8180 | 0 | 0 | 41 | 1 | 13913 | 5994 | 1 | 13421 | 5967 | |||||||||
| 22 | 0 | 0 | 0 | 40 | 1 | 13926 | 5994 | 1 | 13433 | 5970 | |||||||||
| 23 | 0 | 0 | 0 | 39 | 1 | 13937 | 5996 | 1 | 13445 | 5988 | |||||||||
| 24 | 0 | 0 | 0 | 38 | 1 | 13950 | 5990 | 1 | 13457 | 5980 | |||||||||
| 25 | 0 | 0 | 0 | 37 | 1 | 13961 | 5988 | 1 | 13468 | 5980 | |||||||||
| 26 | 0 | 0 | 0 | 36 | 1 | 13973 | 5991 | 1 | 13480 | 5977 | |||||||||
| 27 | 0 | 0 | 2810 | 35 | 1 | 13985 | 5999 | 1 | 13492 | 5981 | |||||||||
| 28 | 0 | 0 | 0 | 34 | 1 | 13996 | 6008 | 1 | 13503 | 5985 | |||||||||
| 29 | 0 | 0 | 0 | 33 | 1 | 14007 | 6003 | 1 | 13515 | 5975 | |||||||||
| 30 | 0 | 0 | 0 | 32 | 1 | 14018 | 6005 | 1 | 13527 | 5978 | |||||||||
| 31 | 0 | 0 | 0 | 31 | 1 | 14030 | 6002 | 1 | 13538 | 5981 | |||||||||
| 32 | 0 | 0 | 0 | 30 | 1 | 14040 | 6008 | 1 | 13550 | 5996 | |||||||||
| 33 | 0 | 0 | 0 | 29 | 1 | 14051 | 6003 | 1 | 13562 | 5998 | |||||||||
| 34 | 2103 | 0 | 0 | 28 | 1 | 14061 | 6004 | 1 | 13573 | 5987 | |||||||||
| 35 | 0 | 0 | 0 | 27 | 1 | 14072 | 6022 | 1 | 13585 | 5995 | |||||||||
| 36 | 0 | 0 | 0 | 26 | 1 | 14082 | 6014 | 1 | 13597 | 5994 | |||||||||
| 37 | 0 | 0 | 0 | 25 | 1 | 14092 | 6017 | 1 | 13609 | 5996 | |||||||||
| 38 | 0 | 0 | 4510 | 24 | 1 | 14102 | 6021 | 1 | 13620 | 5990 | |||||||||
| 39 | 0 | 0 | 0 | 23 | 1 | 14111 | 6028 | 1 | 13632 | 5988 | |||||||||
| 40 | 0 | 0 | 0 | 22 | 1 | 14121 | 6037 | 1 | 13644 | 5991 | |||||||||
| 41 | 0 | 0 | 0 | 21 | 1 | 14130 | 6035 | 1 | 13655 | 5999 | |||||||||
| 42 | 0 | 0 | 0 | 20 | 1 | 14139 | 6031 | 1 | 13667 | 6008 | |||||||||
| 43 | 0 | 0 | 0 | 19 | 1 | 14149 | 6025 | 1 | 13679 | 6003 | |||||||||
| 44 | 2107 | 0 | 0 | 18 | 1 | 14158 | 6029 | 1 | 13690 | 6005 | |||||||||
Sheet1 | |||||||||||||||||||
The end product should look like below,
| FYP.xlsx | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | ID 2 | Move | Data1.Direction[0] | Data1.Count[0] | Data1.Preiod[0] | Data2.Direction[0] | Data2.Count[0] | Data2.Preiod[0] | ||
| 2 | 53 | 1 | 13753 | 5988 | 1 | 13281 | 5952 | |||
| 3 | 52 | 1 | 13767 | 5980 | 1 | 13292 | 5951 | |||
| 4 | 51 | 1 | 13781 | 5979 | 1 | 13304 | 5950 | |||
| 5 | 50 | 1 | 13795 | 5977 | 1 | 13316 | 5953 | |||
| 6 | 49 | 1 | 13809 | 5981 | 1 | 13328 | 5970 | |||
| 7 | 48 | 1 | 13822 | 5985 | 1 | 13339 | 5968 | |||
| 8 | 47 | 1 | 13836 | 5975 | 1 | 13351 | 5961 | |||
| 9 | 46 | 1 | 13849 | 5978 | 1 | 13363 | 5969 | |||
| 10 | 45 | 1 | 13862 | 5981 | 1 | 13374 | 5965 | |||
| 11 | 2152 | 44 | 1 | 13875 | 5998 | 1 | 13386 | 5969 | ||
Alarm | ||||||||||
And if possible, I hope that the code can in a way that I am able to change code for the searched values (2103 can be changed to 8181) and the name of the copied data (Data1.Direction[0] can be changed to something else like Port[1]. This is because I want to reuse the code for doing the same procedure but different searched values and copied data.
Hope this isnt too much too ask... thanks all.
