Hi, I'm something of a script kiddy and my attempts to find a macro that fits my needs have failed.
The files look like this:
<tbody>
</tbody>
Though a lot larger (~50,000 - ~100,000 rows)
I need to go through them and split off sequences of the same TrackName into separate files (worksheet or workbook, it doesn't matter which) with the same header row. So the first 7 rows, then the 8th row, then the next 5 and so on. The trick is that it needs to keep the sequences separate, rather than just grouping all the "p"s together, all the "TB"s together and so on. I've found a couple macros that will split it according to the TrackName column, but nothing that can split it into each sequence of rows with the same TrackName.
Ideally I also need the macro to delete the Time and TrackName columns from the created worksheets/books and save them as Tab-delimited txt files, but that step is at least somewhat possible to do manually, whereas splitting the files into each sequence manually would take an infeasibly long time.
The macro from http://www.mrexcel.com/forum/excel-questions/396069-copy-new-worksheets.html is the closest I've gotten so far, but it gives me this kind of output:
<tbody>
</tbody>
And for the full size files having to go through and manually separate the files wherever there's a break in the time sequence is still not feasible.
The files look like this:
Time | T2R | T2L | T3R | T3L | A1R | A1L | A2R | A2L | A3R | A3L | A4R | A4L | A5R | A5L | A6R | A6L | A7R | A7L | A8R | A8L | TrackName |
0 | 0.090924 | 0.123839 | 0.151038 | 0.150756 | 0.178118 | 0.178547 | 0.205324 | 0.216712 | 0.223721 | 0.257117 | 0.25941 | 0.226461 | 0.318388 | 0.261929 | 0.309183 | 0.366577 | 0.25772 | 0.291869 | 0.158394 | 0.12345 | p |
0.2 | 0.067486 | 0.096805 | 0.139145 | 0.122591 | 0.15435 | 0.134305 | 0.175838 | 0.16767 | 0.190453 | 0.225337 | 0.213936 | 0.186507 | 0.2608 | 0.217103 | 0.257989 | 0.329131 | 0.302762 | 0.292193 | 0.173279 | 0.141501 | p |
0.4 | 0.062525 | 0.070561 | 0.120879 | 0.097968 | 0.143202 | 0.119893 | 0.150851 | 0.142618 | 0.165171 | 0.181165 | 0.182129 | 0.149184 | 0.226056 | 0.183769 | 0.214367 | 0.275224 | 0.269077 | 0.284095 | 0.172211 | 0.136847 | p |
0.6 | 0.07751 | 0.055893 | 0.138236 | 0.074305 | 0.149475 | 0.092797 | 0.142262 | 0.113404 | 0.147218 | 0.153459 | 0.16371 | 0.126134 | 0.184842 | 0.152593 | 0.175587 | 0.228591 | 0.230476 | 0.241943 | 0.150618 | 0.124349 | p |
0.8 | 0.109674 | 0.058506 | 0.175293 | 0.077767 | 0.178336 | 0.08741 | 0.169353 | 0.122791 | 0.153006 | 0.147446 | 0.140879 | 0.109745 | 0.18049 | 0.124904 | 0.151185 | 0.195672 | 0.177895 | 0.190695 | 0.135247 | 0.099613 | p |
1 | 0.672306 | 0.706944 | 0.748782 | 0.665014 | 0.724259 | 0.651651 | 0.650378 | 0.646102 | 0.618494 | 0.62172 | 0.585196 | 0.529146 | 0.576621 | 0.529624 | 0.519548 | 0.587789 | 0.564807 | 0.591167 | 0.762729 | 0.715483 | p |
1.2 | 0.172474 | 0.078745 | 0.29141 | 0.070021 | 0.273052 | 0.096794 | 0.25017 | 0.126699 | 0.19686 | 0.13037 | 0.159294 | 0.066924 | 0.161558 | 0.084613 | 0.106214 | 0.120056 | 0.121246 | 0.103519 | 0.102416 | 0.041954 | p |
1.4 | 0.216902 | 0.07131 | 0.350609 | 0.072791 | 0.319896 | 0.07994 | 0.280976 | 0.121483 | 0.212934 | 0.108087 | 0.156692 | 0.051117 | 0.156248 | 0.051551 | 0.076282 | 0.092587 | 0.069731 | 0.050775 | 0.077721 | 0.014561 | right turn |
1.6 | 0.254596 | 0.093165 | 0.410966 | 0.076955 | 0.354453 | 0.084433 | 0.318903 | 0.127538 | 0.239741 | 0.091007 | 0.165614 | 0.039899 | 0.155916 | 0.039695 | 0.061716 | 0.071879 | 0.038358 | 0.019979 | 0.055968 | -0.01739 | TB |
1.8 | 0.240433 | 0.085955 | 0.425152 | 0.059535 | 0.360318 | 0.068018 | 0.341963 | 0.119606 | 0.287933 | 0.128773 | 0.216263 | 0.05756 | 0.178617 | 0.039676 | 0.066158 | 0.064124 | 0.020834 | -0.00128 | 0.033785 | -0.03775 | TB |
2 | 0.227386 | 0.066297 | 0.396166 | 0.031321 | 0.335793 | 0.050881 | 0.323931 | 0.113415 | 0.296301 | 0.17851 | 0.27745 | 0.122477 | 0.243569 | 0.103446 | 0.08174 | 0.088319 | 0.017908 | -0.00978 | 0.036473 | -0.0299 | TB |
2.2 | 0.183819 | 0.046684 | 0.364566 | 0.015399 | 0.299248 | 0.033832 | 0.282808 | 0.099432 | 0.270901 | 0.152662 | 0.274167 | 0.147237 | 0.301625 | 0.167436 | 0.152507 | 0.160959 | 0.049863 | 0.024751 | 0.036133 | -0.02581 | TB |
2.4 | 0.148302 | 0.027892 | 0.326335 | 0.004001 | 0.271028 | 0.028152 | 0.250121 | 0.083351 | 0.23139 | 0.136192 | 0.2422 | 0.120988 | 0.292992 | 0.18164 | 0.206333 | 0.228929 | 0.117744 | 0.097926 | 0.05382 | 0.000703 | TB |
2.6 | 0.110797 | 0.023959 | 0.28736 | -0.00272 | 0.232669 | 0.01093 | 0.222252 | 0.05522 | 0.201628 | 0.108286 | 0.211411 | 0.106546 | 0.258353 | 0.154662 | 0.229013 | 0.26328 | 0.18534 | 0.169168 | 0.095097 | 0.052153 | p |
2.8 | 0.099264 | 0.028422 | 0.255029 | -0.00669 | 0.210645 | 0.003953 | 0.201595 | 0.0515 | 0.195596 | 0.100692 | 0.190067 | 0.076828 | 0.228446 | 0.135547 | 0.210985 | 0.252558 | 0.226976 | 0.219891 | 0.137307 | 0.089219 | p |
3 | 0.071213 | 0.019751 | 0.227323 | -0.02343 | 0.191725 | 0.01842 | 0.182162 | 0.036542 | 0.162775 | 0.081739 | 0.1595 | 0.079074 | 0.201206 | 0.101815 | 0.195205 | 0.216822 | 0.211662 | 0.224134 | 0.135775 | 0.106554 | p |
3.2 | 0.064361 | 0.014324 | 0.212866 | -0.01288 | 0.179912 | 0.021263 | 0.152768 | 0.033323 | 0.149319 | 0.074638 | 0.147328 | 0.063313 | 0.167621 | 0.096551 | 0.163981 | 0.190907 | 0.182661 | 0.197451 | 0.135196 | 0.103357 | p |
3.4 | 0.079435 | 0.036145 | 0.199246 | 0.013001 | 0.180701 | 0.03921 | 0.148356 | 0.054732 | 0.133733 | 0.076212 | 0.122617 | 0.054074 | 0.140881 | 0.081178 | 0.118904 | 0.161323 | 0.148622 | 0.156922 | 0.118427 | 0.076915 | p |
3.6 | 0.091826 | 0.089774 | 0.219235 | 0.068517 | 0.190657 | 0.075212 | 0.159602 | 0.097242 | 0.130308 | 0.091112 | 0.095676 | 0.050656 | 0.127665 | 0.074106 | 0.093882 | 0.13303 | 0.104617 | 0.12496 | 0.094431 | 0.060119 | p |
3.8 | 0.113236 | 0.1218 | 0.210623 | 0.122096 | 0.174953 | 0.133605 | 0.15013 | 0.140904 | 0.117402 | 0.124441 | 0.079041 | 0.065875 | 0.10165 | 0.067134 | 0.064993 | 0.108434 | 0.07369 | 0.090662 | 0.077235 | 0.033351 | left turn |
4 | 0.122359 | 0.181113 | 0.197178 | 0.180545 | 0.165376 | 0.168625 | 0.134865 | 0.183534 | 0.097764 | 0.154288 | 0.061258 | 0.089651 | 0.076329 | 0.069816 | 0.047961 | 0.099075 | 0.035438 | 0.060017 | 0.055939 | 0.019039 | left turn |
4.2 | 0.157709 | 0.223413 | 0.209386 | 0.231728 | 0.153052 | 0.215731 | 0.12914 | 0.215824 | 0.082069 | 0.182515 | 0.051482 | 0.088879 | 0.06475 | 0.057892 | 0.01409 | 0.082486 | 0.00643 | 0.025113 | 0.031239 | -0.01256 | left turn |
4.4 | 0.166721 | 0.261007 | 0.20748 | 0.302113 | 0.14146 | 0.262628 | 0.122768 | 0.273696 | 0.062981 | 0.2119 | 0.03616 | 0.106158 | 0.0407 | 0.053773 | -0.0083 | 0.06526 | -0.01927 | 0.000133 | 0.025646 | -0.03279 | left turn |
4.6 | 0.183047 | 0.293442 | 0.199177 | 0.336323 | 0.129905 | 0.29298 | 0.114937 | 0.295616 | 0.055061 | 0.232636 | 0.018107 | 0.107896 | 0.033048 | 0.045569 | -0.02326 | 0.047701 | -0.04928 | -0.02457 | 0.019616 | -0.04695 | TB |
4.8 | 0.164267 | 0.283444 | 0.178466 | 0.350132 | 0.132721 | 0.30002 | 0.128947 | 0.318504 | 0.085657 | 0.269686 | 0.046559 | 0.128478 | 0.045707 | 0.056857 | -0.03066 | 0.038699 | -0.05635 | -0.04157 | -0.00216 | -0.04739 | TB |
5 | 0.114712 | 0.237296 | 0.148309 | 0.306958 | 0.120084 | 0.281882 | 0.151586 | 0.302698 | 0.146301 | 0.302998 | 0.117905 | 0.176982 | 0.093924 | 0.091124 | -0.01287 | 0.063889 | -0.05574 | -0.0512 | -0.00138 | -0.0647 | TB |
5.2 | 0.087319 | 0.19775 | 0.10984 | 0.263834 | 0.080774 | 0.244145 | 0.124288 | 0.275437 | 0.165935 | 0.285204 | 0.167607 | 0.20604 | 0.186898 | 0.146492 | 0.036837 | 0.117452 | -0.04796 | -0.03768 | 0.003126 | -0.06285 | TB |
5.4 | 0.067618 | 0.15215 | 0.0866 | 0.228884 | 0.080694 | 0.200072 | 0.103514 | 0.228122 | 0.136211 | 0.254932 | 0.173951 | 0.191154 | 0.236668 | 0.189316 | 0.122561 | 0.192155 | 0.011395 | 0.010875 | 0.026924 | -0.04172 | TB |
5.6 | 0.05873 | 0.139375 | 0.064475 | 0.19291 | 0.067792 | 0.186444 | 0.087226 | 0.195731 | 0.111938 | 0.231495 | 0.135839 | 0.170029 | 0.21851 | 0.174841 | 0.180693 | 0.251747 | 0.109437 | 0.101875 | 0.05494 | -0.00504 | TB |
<tbody>
</tbody>
Though a lot larger (~50,000 - ~100,000 rows)
I need to go through them and split off sequences of the same TrackName into separate files (worksheet or workbook, it doesn't matter which) with the same header row. So the first 7 rows, then the 8th row, then the next 5 and so on. The trick is that it needs to keep the sequences separate, rather than just grouping all the "p"s together, all the "TB"s together and so on. I've found a couple macros that will split it according to the TrackName column, but nothing that can split it into each sequence of rows with the same TrackName.
Ideally I also need the macro to delete the Time and TrackName columns from the created worksheets/books and save them as Tab-delimited txt files, but that step is at least somewhat possible to do manually, whereas splitting the files into each sequence manually would take an infeasibly long time.
The macro from http://www.mrexcel.com/forum/excel-questions/396069-copy-new-worksheets.html is the closest I've gotten so far, but it gives me this kind of output:
Time | T2R | T2L | T3R | T3L | A1R | A1L | A2R | A2L | A3R | A3L | A4R | A4L | A5R | A5L | A6R | A6L | A7R | A7L | A8R | A8L | TrackName |
10.6 | 0.217975 | 0.055962 | 0.279961 | 0.08888 | 0.247724 | 0.095954 | 0.234625 | 0.107772 | 0.162886 | 0.088578 | 0.108038 | 0.014682 | 0.128019 | 0.001972 | 0.020367 | 0.045757 | -0.00496 | -0.01554 | 0.041308 | -0.03127 | b |
10.8 | 0.198445 | 0.040474 | 0.26478 | 0.0549 | 0.236733 | 0.075198 | 0.253562 | 0.1349 | 0.216836 | 0.155899 | 0.162583 | 0.069643 | 0.140112 | 0.029576 | 0.022752 | 0.030432 | -0.01633 | -0.0351 | 0.037202 | -0.05016 | b |
11 | 0.162707 | 0.035617 | 0.235147 | 0.031678 | 0.207756 | 0.054839 | 0.226958 | 0.126638 | 0.236282 | 0.180356 | 0.21922 | 0.128754 | 0.20083 | 0.086011 | 0.049752 | 0.063374 | -0.02519 | -0.04019 | 0.030119 | -0.0544 | b |
11.2 | 0.132503 | 0.021913 | 0.208226 | 0.019308 | 0.185041 | 0.037295 | 0.198208 | 0.097239 | 0.196314 | 0.162 | 0.229407 | 0.152661 | 0.25941 | 0.125067 | 0.113606 | 0.132106 | 0.008907 | -0.00734 | 0.022419 | -0.03618 | b |
11.4 | 0.093744 | 0.006747 | 0.187288 | 0.017655 | 0.15786 | 0.032248 | 0.172779 | 0.07481 | 0.177793 | 0.130525 | 0.197489 | 0.126468 | 0.259421 | 0.150861 | 0.168932 | 0.203376 | 0.078344 | 0.062885 | 0.055963 | -0.01486 | b |
11.6 | 0.071035 | -0.00082 | 0.155297 | -0.00403 | 0.146532 | 0.020282 | 0.149547 | 0.065712 | 0.154113 | 0.123093 | 0.183244 | 0.111447 | 0.236977 | 0.134686 | 0.193921 | 0.231111 | 0.153154 | 0.146984 | 0.082798 | 0.035427 | b |
11.8 | 0.056772 | -0.01022 | 0.133204 | -0.01072 | 0.130242 | 0.011792 | 0.132473 | 0.047595 | 0.141099 | 0.104186 | 0.162006 | 0.098141 | 0.208011 | 0.113122 | 0.180511 | 0.215915 | 0.197482 | 0.202722 | 0.124058 | 0.07239 | b |
12 | 0.055788 | -0.00454 | 0.130163 | -0.01325 | 0.121275 | 0.017523 | 0.125497 | 0.044143 | 0.119379 | 0.086676 | 0.137689 | 0.084208 | 0.172907 | 0.093723 | 0.155621 | 0.196943 | 0.200698 | 0.202493 | 0.124982 | 0.09462 | b |
12.2 | 0.053227 | -0.01377 | 0.123428 | -0.01087 | 0.128814 | 0.013719 | 0.117726 | 0.033672 | 0.112206 | 0.082978 | 0.118196 | 0.064204 | 0.161561 | 0.080869 | 0.129938 | 0.160393 | 0.182497 | 0.174934 | 0.122718 | 0.08431 | b |
12.4 | 0.089812 | 0.000112 | 0.156157 | -0.00663 | 0.147416 | 0.02262 | 0.123173 | 0.033184 | 0.102866 | 0.067991 | 0.09472 | 0.058683 | 0.132638 | 0.06646 | 0.10345 | 0.140968 | 0.134052 | 0.145379 | 0.107775 | 0.059909 | b |
12.6 | 0.120187 | 0.020802 | 0.186923 | 0.007217 | 0.181433 | 0.042765 | 0.135126 | 0.04677 | 0.106446 | 0.055101 | 0.099266 | 0.043743 | 0.115183 | 0.04464 | 0.08465 | 0.114301 | 0.104881 | 0.103725 | 0.094998 | 0.051358 | b |
15 | 0.119803 | 0.048772 | 0.322071 | 0.005317 | 0.290721 | 0.016964 | 0.215367 | 0.025892 | 0.141896 | 0.014525 | 0.080998 | -0.02084 | 0.043994 | -0.04685 | -0.05326 | -0.04465 | -0.09187 | -0.09341 | -0.02574 | -0.08869 | b |
15.2 | 0.09874 | 0.031004 | 0.284842 | 0.006475 | 0.274684 | 0.026695 | 0.228255 | 0.061535 | 0.183927 | 0.056634 | 0.09993 | 0.009586 | 0.054931 | -0.03316 | -0.05446 | -0.05103 | -0.09503 | -0.11651 | -0.03603 | -0.08883 | b |
15.4 | 0.081421 | 0.011976 | 0.248103 | -0.01542 | 0.23723 | 0.01028 | 0.207274 | 0.066002 | 0.196152 | 0.097158 | 0.158947 | 0.082142 | 0.111282 | 0.032079 | -0.027 | -0.01392 | -0.09109 | -0.12113 | -0.04332 | -0.10222 | b |
15.6 | 0.063779 | 0.006321 | 0.200885 | -0.01682 | 0.183674 | -0.00345 | 0.168069 | 0.044166 | 0.172365 | 0.088328 | 0.187047 | 0.117808 | 0.187305 | 0.107361 | 0.027478 | 0.058289 | -0.07617 | -0.08888 | -0.03508 | -0.0859 | b |
15.8 | 0.040494 | 0.00153 | 0.167639 | -0.03606 | 0.152338 | -0.01986 | 0.133461 | 0.020486 | 0.142642 | 0.046571 | 0.17429 | 0.111764 | 0.210491 | 0.151705 | 0.11297 | 0.160595 | -0.01898 | -0.02582 | -0.01324 | -0.06371 | b |
16 | 0.031055 | -0.00924 | 0.153814 | -0.04768 | 0.144072 | -0.04273 | 0.124742 | -0.00543 | 0.122905 | 0.020651 | 0.150208 | 0.078973 | 0.199428 | 0.143222 | 0.152561 | 0.196948 | 0.070305 | 0.059468 | 0.024602 | -0.0274 | b |
16.2 | 0.022942 | -0.00442 | 0.136653 | -0.05252 | 0.135352 | -0.03899 | 0.116681 | -0.01908 | 0.122297 | 0.005833 | 0.125237 | 0.044204 | 0.161597 | 0.116248 | 0.150356 | 0.200009 | 0.129959 | 0.121621 | 0.067365 | 0.02572 | b |
16.4 | 0.025125 | 0.019323 | 0.122356 | -0.03428 | 0.13034 | -0.02657 | 0.107256 | -0.01691 | 0.113839 | 0.020372 | 0.106488 | 0.046205 | 0.14286 | 0.090035 | 0.129108 | 0.180102 | 0.1229 | 0.131331 | 0.077418 | 0.035033 | b |
16.6 | 0.039753 | 0.049378 | 0.110926 | 0.000715 | 0.117577 | -0.00226 | 0.091529 | 0.014563 | 0.099996 | 0.026791 | 0.087503 | 0.040677 | 0.122476 | 0.062677 | 0.09944 | 0.151493 | 0.107805 | 0.106694 | 0.081133 | 0.024343 | b |
17.6 | 0.067585 | 0.243585 | 0.056828 | 0.253042 | 0.054246 | 0.212211 | 0.05205 | 0.185057 | 0.028429 | 0.13535 | 0.008384 | 0.077574 | 0.046345 | 0.038782 | -0.00092 | 0.05451 | -0.02459 | -0.02295 | 0.012996 | -0.04391 | b |
17.8 | 0.044668 | 0.237916 | 0.0475 | 0.278677 | 0.046056 | 0.223599 | 0.050267 | 0.232169 | 0.039224 | 0.159476 | 0.020279 | 0.091728 | 0.039909 | 0.034883 | -0.01807 | 0.04297 | -0.0482 | -0.04984 | 0.0008 | -0.04526 | b |
18 | 0.032984 | 0.213373 | 0.02797 | 0.269723 | 0.044066 | 0.231051 | 0.081191 | 0.247155 | 0.080994 | 0.197945 | 0.054046 | 0.133323 | 0.050634 | 0.058568 | -0.02741 | 0.046677 | -0.05261 | -0.06616 | 0.001192 | -0.05352 | b |
18.2 | 0.007896 | 0.185023 | 0.00541 | 0.250739 | 0.02389 | 0.210266 | 0.079767 | 0.225572 | 0.112679 | 0.222722 | 0.109574 | 0.1755 | 0.106952 | 0.099682 | -0.00036 | 0.073685 | -0.05957 | -0.06809 | -0.00406 | -0.06262 | b |
18.4 | 0.000237 | 0.162273 | -0.01673 | 0.212991 | 0.012393 | 0.173895 | 0.059131 | 0.193284 | 0.100834 | 0.206622 | 0.132641 | 0.183571 | 0.168022 | 0.142979 | 0.05568 | 0.13196 | -0.03258 | -0.04493 | 0.005786 | -0.0565 | b |
18.6 | -0.00188 | 0.132993 | -0.02115 | 0.187822 | 0.009686 | 0.151429 | 0.045823 | 0.153259 | 0.077356 | 0.169117 | 0.118984 | 0.155367 | 0.18346 | 0.150773 | 0.114055 | 0.195944 | 0.022434 | 0.012183 | 0.017141 | -0.03556 | b |
18.8 | -0.00967 | 0.112522 | -0.01967 | 0.151115 | 0.004619 | 0.129054 | 0.037045 | 0.134322 | 0.064901 | 0.143406 | 0.091418 | 0.13278 | 0.158616 | 0.135755 | 0.139194 | 0.224478 | 0.089173 | 0.087251 | 0.054629 | -0.00538 | b |
19 | -0.00328 | 0.080878 | -0.02035 | 0.134095 | 0.010359 | 0.113745 | 0.033616 | 0.110655 | 0.060195 | 0.117216 | 0.071867 | 0.105224 | 0.139865 | 0.110711 | 0.126498 | 0.208275 | 0.115085 | 0.122725 | 0.071749 | 0.020974 | b |
19.2 | 0.003033 | 0.074836 | -0.00538 | 0.108002 | 0.012305 | 0.096558 | 0.033521 | 0.098677 | 0.051998 | 0.099196 | 0.057145 | 0.071869 | 0.119139 | 0.08806 | 0.099615 | 0.174099 | 0.108273 | 0.109594 | 0.063348 | 0.021973 | b |
<tbody>
</tbody>
And for the full size files having to go through and manually separate the files wherever there's a break in the time sequence is still not feasible.