How to filter Excel list at 5metre intervals?

**scotsteve** · 08-21-2011, 03:59 AM

i have a spreadsheet in excel with columns of data, and the first column is a km value of length or distance, starting at 000.000 and rising 0.001 eg 0.000,0.001, 0.002 etc..

I want to keep the rows at 5m intervals thats 0.005, so, 0.000, 0.005, 0.010 etc.. How would i do this??

i will try to give an example of the spreadsheet ....

distance (km) longtitude (km) latitude (km) distance offcourse (m)
x 0.000 1000.00 2500.00 3.50
0.001 1250.00 2250.00 1.25
0.002 900.00 2000.00 2.50
0.003 1000.00 2400.00 3.00
0.004 1500.00 2200.00 1.50
x 0.005 1300.00 1900.00 2.25

... in my final desired spreadsheet, i want to see only rows marked with 'x' effectively making the listing filrered at 5 metre intervals!
Any ideas??

**TMS** · 08-21-2011, 05:26 AM

Maybe a Helper column?

in row 2: =IF(OR(A2=0,INT(N(A2)/0.005)<>INT(N(OFFSET(A2,-1,0))/0.005)),"keep","")

and copy down.

Then filter the Helper column for "keep"

Regards

**scotsteve** · 08-21-2011, 05:46 AM

This does sound like a solution but i have no idea.
Row 2 is already populated with other data so i dont understand, but thanks anyway.
I hate excel with a passion lol

**TMS** · 08-21-2011, 05:57 AM

Row 2 is populated with other data? All the columns in row 2 are populated?

It doesn't matter which column you use, you just put the formula in row 2 of that column and then copy it down to the end of your data.

Could be column AA, column AZ, column IV, whatever

So, for example:

Cell AA2: =IF(OR(A2=0,INT(N(A2)/0.005)<>INT(N(OFFSET(A2,-1,0))/0.005)),"keep","")

and copy down.

Regards

**scotsteve** · 08-21-2011, 06:21 AM

ok, i now have a column with the word 'keep' in all the right places but cant figure out how to filter those rows!

Did i mention how much i hate Excel lol, i only have 3 degrees, seems not enough to get my head round excels black arts lol

Thanks.

**Marcol** · 08-21-2011, 06:36 AM

...i only have 3 degrees, ...

Aye, they made some braw records in thier day! ...

Filter on that column for "keep"

**TMS** · 08-21-2011, 06:42 AM

Ideally, put a header in row 1, for example, "required?", makes no odds what it is.

Then select that header ... though usually not essential.

Then choose Data, Filter, Autofilter and, in the criteria, choose "keep"

Regards

**scotsteve** · 08-21-2011, 07:10 AM

thanks very much

**TMS** · 08-21-2011, 10:30 AM

You're welcome.

If this has answered your question, please mark your thread as solved. See my signature for details or the FAQ.

Hope you're beginning to love Excel ;-)

Regards