Extract data to multiple worksheets/books based on Column Value

[Anzer_ke]

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:

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.

 

[Anzer_ke]

Figured it out.

If anyone else ever has the same problem, this is the code I cobbled together.

Public Sub SplitToFiles()

' MACRO SplitToFiles
' Last update: 2012-03-04
' Author: mtone
' Version 1.1
' Description:
' Loops through a specified column, and split each distinct values into a separate file by making a copy and deleting rows below and above
'
' Note: Values in the column should be unique or sorted.
'
' The following cells are ignored when delimiting sections:
' - blank cells, or containing spaces only
' - same value repeated
' - cells containing "total"
'
' Files are saved in a "Split" subfolder from the location of the source workbook, and named after the section name.

Dim osh As Worksheet ' Original sheet
Dim iRow As Long ' Cursors
Dim iCol As Long
Dim iFirstRow As Long ' Constant
Dim iTotalRows As Long ' Constant
Dim iStartRow As Long ' Section delimiters
Dim iStopRow As Long
Dim sSectionName As String ' Section name (and filename)
Dim sFileNames As String ' filename
Dim sFinalName As String ' filename
Dim rCell As Range ' current cell
Dim owb As Workbook ' Original workbook
Dim sFilePath As String ' Constant
Dim iCount As Integer ' # of documents created

iCol = Application.InputBox("Enter the column number used for splitting", "Select column", 2, , , , , 1)
iRow = Application.InputBox("Enter the starting row number (to skip header)", "Select row", 5, , , , , 1)
iFirstRow = iRow

Set osh = Application.ActiveSheet
Set owb = Application.ActiveWorkbook
iTotalRows = osh.UsedRange.Rows.Count
sFilePath = Application.ActiveWorkbook.Path

If Dir(sFilePath + "\Split", vbDirectory) = "" Then
MkDir sFilePath + "\Split"
End If

'Turn Off Screen Updating Events
Application.EnableEvents = False
Application.ScreenUpdating = False

Do
' Get cell at cursor
Set rCell = osh.Cells(iRow, iCol)
sCell = Replace(rCell.Text, " ", "")

If sCell = "" Or (rCell.Text = sSectionName And iStartRow <> 0) Or InStr(1, rCell.Text, "total", vbTextCompare) <> 0 Then
' Skip condition met
Else
' Found new section
If iStartRow = 0 Then
' StartRow delimiter not set, meaning beginning a new section
sSectionName = rCell.Text
iStartRow = iRow
Else
' StartRow delimiter set, meaning we reached the end of a section
iStopRow = iRow - 1

' file name as count
sFileNames = iCount

' Pass variables to a separate sub to create and save the new worksheet
CopySheet osh, iFirstRow, iStartRow, iStopRow, iTotalRows, sFilePath, sSectionName, sFileNames, sFinalName, owb.fileFormat
iCount = iCount + 1

' Reset section delimiters
iStartRow = 0
iStopRow = 0

' Ready to continue loop
iRow = iRow - 1
End If
End If

' Continue until last row is reached
If iRow < iTotalRows Then
iRow = iRow + 1
Else
' Finished. Save the last section
iStopRow = iRow
CopySheet osh, iFirstRow, iStartRow, iStopRow, iTotalRows, sFilePath, sSectionName, sFileNames, sFinalName, owb.fileFormat
iCount = iCount + 1

' Exit
Exit Do
End If
Loop

'Turn On Screen Updating Events
Application.ScreenUpdating = True
Application.EnableEvents = True

MsgBox Str(iCount) + " documents saved in " + sFilePath


End Sub

Public Sub DeleteRows(targetSheet As Worksheet, RowFrom As Long, RowTo As Long)

Dim rngRange As Range
Set rngRange = Range(targetSheet.Cells(RowFrom, 1), targetSheet.Cells(RowTo, 1)).EntireRow
rngRange.Select
rngRange.Delete

End Sub


Public Sub CopySheet(osh As Worksheet, iFirstRow As Long, iStartRow As Long, iStopRow As Long, iTotalRows As Long, sFilePath As String, sSectionName As String, sFileNames As String, sFinalName As String, fileFormat As XlFileFormat)
Dim ash As Worksheet ' Copied sheet
Dim awb As Workbook ' New workbook

' Copy book
osh.Copy
Set ash = Application.ActiveSheet

' Delete Rows after section
If iTotalRows > iStopRow Then
DeleteRows ash, iStopRow + 1, iTotalRows
End If

' Delete Rows before section
If iStartRow > iFirstRow Then
DeleteRows ash, iFirstRow, iStartRow - 1
End If

' Select left-topmost cell
ash.Cells(1, 1).Select

' Clean up a few characters to prevent invalid filename
sSectionName = Replace(sSectionName, "/", " ")
sSectionName = Replace(sSectionName, "\", " ")
sSectionName = Replace(sSectionName, ":", " ")
sSectionName = Replace(sSectionName, "=", " ")
sSectionName = Replace(sSectionName, "*", " ")
sSectionName = Replace(sSectionName, ".", " ")
sSectionName = Replace(sSectionName, "?", ".q.")

' Delete Columns
Columns([22]).EntireColumn.Delete
Columns([1]).EntireColumn.Delete

sFinalName = sSectionName & "_" & sFileNames

' Save in same format as original workbook, need to get it to save section name before file count
ash.SaveAs sFilePath + "\Split\" + sSectionName & "_" & sFileNames, xlTextWindows

' Close
Set awb = ash.Parent
awb.Close SaveChanges:=False
End Sub

Based on what I found here: Can I split a spreadsheet into multiple files based on a column in Excel 2007? - Super User