Filter will not work -- Number Ends With
 

When using Excel I have a data set that contains three large columns of cells
that contain only numbers ..The first column is a time stamp that increments
by 1 (50001,50002,50003, etc.) -- I want to return only the data that
correspond to every fifth time stamp. I set up an auto filter that filters
based on the first column (Time Stamps) using a custom filter that filters
the cells based on "ends with - 4" or "ends with - 9" but the filter returns
nothing

Interesting ...If I put a letter in front of the time stamp (ie a50001,
a50002, etc.) it will filter those data that contain a letter using the
filter described above

Any Ideas????????

Here is a slice of my spreadsheet:

Time Stamp Northing Easting
150155 804916.851 388281.598
150156 804916.852 388281.598
150157 804916.852 388281.597
150158 804916.847 388281.6
150159 804916.851 388281.594
150160 804916.848 388281.602
150161 804916.849 388281.599
150162 804916.852 388281.595
150163 804916.852 388281.596
150164 804916.85 388281.597

Filter will not work -- Number Ends With
 

Ends with, begins with, contains, does not contain, does not begin with etc.
only work with text



--


Regards,


Peo Sjoblom



"ATW2500" wrote in message
...
When using Excel I have a data set that contains three large columns of
cells
that contain only numbers ..The first column is a time stamp that
increments
by 1 (50001,50002,50003, etc.) -- I want to return only the data that
correspond to every fifth time stamp. I set up an auto filter that
filters
based on the first column (Time Stamps) using a custom filter that filters
the cells based on "ends with - 4" or "ends with - 9" but the filter
returns
nothing

Interesting ...If I put a letter in front of the time stamp (ie a50001,
a50002, etc.) it will filter those data that contain a letter using the
filter described above

Any Ideas????????

Here is a slice of my spreadsheet:

Time Stamp Northing Easting
150155 804916.851 388281.598
150156 804916.852 388281.598
150157 804916.852 388281.597
150158 804916.847 388281.6
150159 804916.851 388281.594
150160 804916.848 388281.602
150161 804916.849 388281.599
150162 804916.852 388281.595
150163 804916.852 388281.596
150164 804916.85 388281.597

Filter will not work -- Number Ends With
 

Any one have any ideas how I can filter out 4 time stamps, keep one, filter
out 4, keep 1, filter out 4, keep 1, etc (I want to keep only every fifth
data record)????

"Peo Sjoblom" wrote:

Ends with, begins with, contains, does not contain, does not begin with etc.
only work with text



--


Regards,


Peo Sjoblom



"ATW2500" wrote in message
...
When using Excel I have a data set that contains three large columns of
cells
that contain only numbers ..The first column is a time stamp that
increments
by 1 (50001,50002,50003, etc.) -- I want to return only the data that
correspond to every fifth time stamp. I set up an auto filter that
filters
based on the first column (Time Stamps) using a custom filter that filters
the cells based on "ends with - 4" or "ends with - 9" but the filter
returns
nothing

Interesting ...If I put a letter in front of the time stamp (ie a50001,
a50002, etc.) it will filter those data that contain a letter using the
filter described above

Any Ideas????????

Here is a slice of my spreadsheet:

Time Stamp Northing Easting
150155 804916.851 388281.598
150156 804916.852 388281.598
150157 804916.852 388281.597
150158 804916.847 388281.6
150159 804916.851 388281.594
150160 804916.848 388281.602
150161 804916.849 388281.599
150162 804916.852 388281.595
150163 804916.852 388281.596
150164 804916.85 388281.597

Filter will not work -- Number Ends With
 

You can use a help column, assume your numbers starts in A2 with a header in
A1, select column B, do insertcolumn, now in B2 put

=MOD(ROW(1:1),5)


copy down by double clicking the lower right corner of B2 when the cursor
changes from a thick cross to a thin

then filter on zero on the help column


--


Regards,


Peo Sjoblom


"ATW2500" wrote in message
...
Any one have any ideas how I can filter out 4 time stamps, keep one,
filter
out 4, keep 1, filter out 4, keep 1, etc (I want to keep only every
fifth
data record)????

"Peo Sjoblom" wrote:

Ends with, begins with, contains, does not contain, does not begin with
etc.
only work with text



--


Regards,


Peo Sjoblom



"ATW2500" wrote in message
...
When using Excel I have a data set that contains three large columns of
cells
that contain only numbers ..The first column is a time stamp that
increments
by 1 (50001,50002,50003, etc.) -- I want to return only the data that
correspond to every fifth time stamp. I set up an auto filter that
filters
based on the first column (Time Stamps) using a custom filter that
filters
the cells based on "ends with - 4" or "ends with - 9" but the filter
returns
nothing

Interesting ...If I put a letter in front of the time stamp (ie
a50001,
a50002, etc.) it will filter those data that contain a letter using the
filter described above

Any Ideas????????

Here is a slice of my spreadsheet:

Time Stamp Northing Easting
150155 804916.851 388281.598
150156 804916.852 388281.598
150157 804916.852 388281.597
150158 804916.847 388281.6
150159 804916.851 388281.594
150160 804916.848 388281.602
150161 804916.849 388281.599
150162 804916.852 388281.595
150163 804916.852 388281.596
150164 804916.85 388281.597

Filter will not work -- Number Ends With
 

Thanks that helped out a lot
exactly what does that command do to get a column that repeats 01234 over
and over???

"Peo Sjoblom" wrote:

You can use a help column, assume your numbers starts in A2 with a header in
A1, select column B, do insertcolumn, now in B2 put

=MOD(ROW(1:1),5)


copy down by double clicking the lower right corner of B2 when the cursor
changes from a thick cross to a thin

then filter on zero on the help column


--


Regards,


Peo Sjoblom


"ATW2500" wrote in message
...
Any one have any ideas how I can filter out 4 time stamps, keep one,
filter
out 4, keep 1, filter out 4, keep 1, etc (I want to keep only every
fifth
data record)????

"Peo Sjoblom" wrote:

Ends with, begins with, contains, does not contain, does not begin with
etc.
only work with text



--


Regards,


Peo Sjoblom



"ATW2500" wrote in message
...
When using Excel I have a data set that contains three large columns of
cells
that contain only numbers ..The first column is a time stamp that
increments
by 1 (50001,50002,50003, etc.) -- I want to return only the data that
correspond to every fifth time stamp. I set up an auto filter that
filters
based on the first column (Time Stamps) using a custom filter that
filters
the cells based on "ends with - 4" or "ends with - 9" but the filter
returns
nothing

Interesting ...If I put a letter in front of the time stamp (ie
a50001,
a50002, etc.) it will filter those data that contain a letter using the
filter described above

Any Ideas????????

Here is a slice of my spreadsheet:

Time Stamp Northing Easting
150155 804916.851 388281.598
150156 804916.852 388281.598
150157 804916.852 388281.597
150158 804916.847 388281.6
150159 804916.851 388281.594
150160 804916.848 388281.602
150161 804916.849 388281.599
150162 804916.852 388281.595
150163 804916.852 388281.596
150164 804916.85 388281.597

Filter will not work -- Number Ends With
 

As it has been some hours since you asked, I'll chip in here. ROW(1:1)
will change to ROW(2:2) and ROW(3:3) etc as the formula is copied down
- this will return 1, 2, 3, 4, 5, 6, 7, ... and so on, and as such it
is a convenient way to generate an incrementing number.

The MOD function returns the remainder after division - in this case
the divisor is 5, so it will return 1, 2, 3, 4, 0, 1, 2, 3, 4, 0, 1,
2, 3 and so on.

Hope this helps.

Pete

On May 5, 7:28*pm, ATW2500 wrote:
Thanks that helped out a lot
exactly what does that command do to get a column that repeats 01234 over
and over???