Macro required to split the data on specific criteria

[arihanth]

Hi,
I need a macro which can help me split a set of data on a particular criteria. The file is actually very large with 399327 entries of which the sample data is given below.


Time (milliseconds) Length of packet
0 ,,,,,,, 60
0.435 ,,,, 1506
1.88 ,,,, 60
2.337 ,,,, 1514
2.461 ,,,, 1514
5.428 ,,,, 60
5.828 ,,,, 1506
9.393 ,,,, 60
9.754 ,,,,, 1112
13.89 ,,,,, 60
14.309 ,,,, 1506
14.431 ,,,, 1506
14.554 ,,,, 1506
19.368 ,,,, 60
19.448 ,,,, 60
19.768 ,,,,, 1414
19.886 ,,,, 1414
20.018 ,,,, 1506
The data runs till 9000ms (900seconds).I need to split this data in the intervals of every 100 ms and create separate worksheet/workbook. Can somebody please help me with the coding for the same.
Thanks for your help in advance.
PS: Commas added in between the readings to distinguish the columns.

Regards,
Arihanth

 

[Peter_SSs]

Welcome to the MrExcel board!

Not quite clear.

1. How does 9000 milliseconds = 900 seconds? (I would have thought 9000ms = 9s)

2. Is it correct that you have different numbers of commas separating your data. That is, can the second number in each row occur in different columns?

3. Does your sample data include a point where a break to a new sheet would be required? If so, where and why? If not, can you show us some sample data that spans a break point and explain where the breakpoint is, and why?

 

[arihanth]

Thank you for your reply.

Sorry for the typing error its 900000 milliseconds
All the data separated by comma is in the second column.
There is no brakepoint in the data. Its a continuous data which needs to be broken down into intervals of 100ms for further processing.
Thanks

 

[Peter_SSs]

If the data in column A goes from 0 to 900,000ms and you want to split it into worksheets every 100ms, that would result in 9,000 worksheets. Is that really what you meant?!

 

[arihanth]

Hi,
Yes that's what I need to do with the data. So is there a possible solution for this. Please help.

Thanks.

 

[Weaver]

Are the 9000 sheets the end result or do you need to do further work?

Theoretically it's possible, but I think it might put a strain on system resources.

 

[arihanth]

Hi,

I will be working further on these files for my experiments as a part of my project. Thanks for the links. I will check them.

 

[Weaver]

Seriously, I'd only resort to dumping that data to 9000 separate sheets if there is no other way of doing what you're trying to do. There may be some process or function that can be performed on the data without splitting it and still give the results you're after.

BTW, the links are generic.

 

[arihanth]

Well the original thing which I sought to do is to find the throughput of the packets in time interval 100ms and then find different values like mean and variance for individual 100ms sets of data and then cumulatively for 15secs as well. So I thought it would be a better option to segregate the data and continue these processes. It would be helpful for me if you could suggest some other workaround to obtain these targets.

 

[Peter_SSs]

Not sure how it would go with so much data (it may overload Excel), but one option might be to use a Pivot Table. Below I have created a new column with formula in C2 copied down then used 'Interval' as row labels and 'Length' in the data area of my Pivot Table created in columns E:F with averages shown in column F. Is that something to work with?

I would start testing with smaller data sets and see how it performs as the amount of data increases.

Excel Workbook
	A	B	C	D	E	F	G
1	Time	Length	Interval		Average of Length
2	0	60	0		Interval	Total
3	0.435	1506	0		0	1041.047619
4	1.88	60	0		100	2150
5	2.337	1514	0		200	2656
6	2.461	1514	0		300	3162
7	5.428	60	0		400	3576
8	5.828	1506	0		Grand Total	1918.585366
9	9.393	60	0
10	9.754		0
11	13.89	1112	0
12	14.309	60	0
13	14.431	1506	0
14	14.554	1506	0
15	19.368	60	0
16	19.448	60	0
17	19.768	1414	0
18	19.886	1414	0
19	20.018	1506	0
20	38.3	1598	0
21	56.582	1690	0
22	74.864	1782	0
23	93.146	1874	0
24	111.428	1966	100
25	129.71	2058	100
26	147.992	2150	100
27	166.274	2242	100
28	184.556	2334	100
29	202.838	2426	200
30	221.12	2518	200
31	239.402	2610	200
32	257.684	2702	200
33	275.966	2794	200
34	294.248	2886	200
35	312.53	2978	300
36	330.812	3070	300
37	349.094	3162	300
38	367.376	3254	300
39	385.658	3346	300
40	403.94	3438	400
41	422.222	3530	400
42	440.504	3622	400
43	458.786	3714	400
split to columns