Extracting rows with cell values separated by a number

[Elee101]

Hi everyone

I have an spreadsheet in which one of the columns is time (in seconds). I would like to spit this list into as many others as needed, given that all values in one list should be no less than half a second apart. Ideally, I would like the new lists to contain all the columns in the original.

Here is an example:

A time Value 1
168.8361359 29.757 83685.80391
165.8364324 30.36125 25324.31974
286.9120403 31.9223 14003.81585
232.9247355 32.4458 18134.69367
172.9578616 32.4458 54129.30859
218.9634665 32.4458 37958.49144
282.1572176 32.4458 37573.45107
400.0495911 32.9052 70255.47007
202.1198241 32.93715 18100.78577
186.9294181 33.4925 25927.35314
421.0283041 33.51035 272928.2871
417.0480302 33.5921 55145.51809
164.9473326 33.72235 32684.52852
426.012421 33.9522 10661.28413
112.9529448 33.9522 17796.22585
333.9253002 33.9522 34534.15795
182.9772572 33.98415 21946.6964
318.951973 33.98415 48142.16859
385.9388654 33.98415 104292.1537
594.8435263 34.0161 53106.94819
113.9882014 34.0161 182509.3443
112.9848478 34.0161 8661391.508
114.9891036 34.0161 66507.39859
254.917 34.0161 36304.30477
250.964737 34.0161 43072.8588
386.9396115 34.0161 33543.95898
248.9604286 34.0161 3228285.992
332.9219953 34.0161 750739.3852
316.9478308 34.0161 2596902.687
714.8430319 34.0161 47189.19202
334.9201777 34.0161 46566.53289
317.9512352 34.0161 141462.1741
452.9228331 34.0161 1047457.531
453.9262244 34.0161 70513.53914
199.0700924 34.03395 27744.77272



Thanks in advance!

[FDibbins]

Hi, welcome to the forum

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[CAABYYC]

C2=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",200)),(COLUMN(A1)-1)*200+1,200))-0 copy to E2 then copy down

[jason.b75]

The loss of formatting in your sample makes it hard to follow.

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[jason.b75]

@CAABYYC, I don't think that will work, the sample appears to conatin 3 columns of data. The questions implies sorting the rows into multiple tables.

I can't see anything logical from the copy / paste sample, there appear to be 3 columns, but only 2 headers. If the middle column is the one with the times, then this requirement,

given that all values in one list should be no less than half a second apart.

could result in a lot of tables with very few rows of data.

[Elee101]

Hi everyone,

II had some trouble with attachments but here is the dummy spreadsheet .

I could change the position of the columns if that makes it any easier. Ideally, I would like to retrieve as few list as possible, in which the times no less than 0.5s apart. I would also like to retrieve all row information, even if it results in a sheet with few (or a single) row.

I would just like to say that although this is my first post, this forum has helped me many times in the past - you guys are awesome!

Thanks everyone for the help!

[Glenn Kennedy]

You were asked to shaow some manually calculated results, so that we can understand what you are trying to do. Please do so. On the Excel sheet.

[Elee101]

Thanks Glenn,

here is the requested list. I want to convert the "Original List" into Lists A-K. Where in each list, the "time" values are separated by >0.5s.

Thanks for your help!

[jason.b75]

Right, so just to be sure we're on the same page.

The original list will always be in chronological order?

List A should take the first entry from the original list, then each subsequent entry in order as long as the time difference is a minimum of 0.5

List B should take the first entry from the original list that is not in list A, then each subsequent entry in the original list that is not in list A as long as the time difference is at least 0.5

Repeat pattern until the original list is exhaused.

Looking at your example, the values in columns A and C appear to be unique decimal values. Is there any possibility that 2 or more rows could contain identical values in all 3 columns?

Can we add an extra column to each list? It will be much easier to solve with a single unique key for each row.

[Elee101]

Hi Jason,

That is all correct.

The list will always be in chronological order.

There will be no rows that have the same value on all 3 columns.

Adding an extra column is not a problem.

Thank you!

[Glenn Kennedy]

Some of your expected answers are incorrect, as the ime differences exceed 0.5 sec in the one column.

D2 (helper column) 1. D3, copied down:
=IF(ABS(B3)-ABS(B2)>0.5,D2+1,D2)

Formulae, all variants of this one in F3, copied down:
=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($D$2:$D$36)/($D$2:$D$36=H$1),ROWS($1:1))),"")

[jason.b75]

Have a look at the format in here, see if it is something you can use.

The original list is sorted into an order suitable for the smaller list, using this Array formula in F3, filled down.

=IFERROR(SMALL(IF($B$3:$B$24>=F2+0.5,IF(ISERROR(MATCH(ROW($B$3:$B$24)-ROW($B$3)+1,$H$2:$H2,0)),$B$3:$B$24)),1),SMALL(IF(ISERROR(MATCH(ROW($B$3:$B$24)-ROW($B$3)+1,$H$2:$H2,0)),$B$3:$B$24),1))

Along with this regular formula in H3, filled down.

=MATCH(F3,$B$3:$B$24,0)+COUNTIF(F$2:F2,F3)

A and Value 1 are then matched to the sorted list using =INDEX($A$3:$A$24,H3) in E3 and =INDEX($C$3:$C$24,H3) in G3, both filled down.

Finally, this formula in I3 and filled down allocates the table letter to each row ("List" refers to the column heading in I2).

=IF(I2="List","A",CHAR(CODE(I2)+((F3-F2)<0.5)))

With all that in place, the lists can be generated by a pivottable as in the attached file.

[Elee101]

Hey Jason,

Works great!! thank you very much for the help!

[Glenn Kennedy]

As a matter of interest, did my effort work or not?

[Elee101]

Hi Glenn,

Thank you for helping, I greatly appreciate it. I think I didn't explain it well enough. I wanted the lists to have time values that are no less than 0.5s apart. If I'm not wrong, your table had lists that always had the values less than 0.5s apart. (might be an easy fix on the formulas?)

Thank you for the interest.