Filter will not work --> Number Ends With

  • Thread starter Thread starter ATW2500
  • Start date Start date
A

ATW2500

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
 
Ends with, begins with, contains, does not contain, does not begin with etc.
only work with text



--


Regards,


Peo Sjoblom
 
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)????
 
You can use a help column, assume your numbers starts in A2 with a header in
A1, select column B, do insert>column, 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
 
Thanks that helped out a lot
exactly what does that command do to get a column that repeats 01234 over
and over???
 
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
 
Back
Top