Ask Your Question
0

I am trying to filter out "ending with" values, but the data is not displayed post applying the filter. What am I doing wrong?

asked 2017-05-27 19:28:38 +0200

MsAmen gravatar image

updated 2017-05-27 20:19:54 +0200

Hi! I am attempting to filter out and delete zip codes ending with 3,5,7 from column D. (file type .ods _colmns A-AI_100k + rows). I select the column, select standard filter, enter in the field name Column D- condition Ends With-value 3

*Edit removed operator sequence

The data will not display. Im unsure what I am doing wrong. I can reverse the filter and have successfully filtered out and deleted unrelated data. Is there a solution to my delima? Thanks for your help! :)

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-05-28 13:42:01 +0200

pierre-yves samyn gravatar image

updated 2017-05-29 18:22:00 +0200

Hi

The data is probably numeric (this should work directly with strings data). The solution is:

  • Columns Ends with
  • Value: 2$
  • tick OptionsRegular expressions

[EDIT]

Thank you for the link. This works in my environment (windows & 5.3) with copy of your data. What is your platform?

screenshot filter

Regards

edit flag offensive delete link more

Comments

Thank you for your response I had high hopes for this solution but alas no.... No data...

I think you are on to something being that the data is numerical as I've had no issue filtering out subtitles for deletion. I fear that without finding a solution to this madness I will litterally have to print out the sheets, red out the data by hand and equate sums manually, I would filter them out one by one but I'm concerned I would miss one and throw off the results.

Any other ideas :)

MsAmen gravatar imageMsAmen ( 2017-05-28 18:58:59 +0200 )edit

Well... I tried Contains2$Regular expressions - and it works...

Mike Kaganski gravatar imageMike Kaganski ( 2017-05-28 22:06:04 +0200 )edit

I subbed 3$ for 2$ regular expressions checked... Nothing no data .... I don't get it ....

MsAmen gravatar imageMsAmen ( 2017-05-28 22:10:11 +0200 )edit

with Contains?

Mike Kaganski gravatar imageMike Kaganski ( 2017-05-28 22:12:46 +0200 )edit

@MsAmen - Could you provide a link to a file (redacted from personal data) to reproduce the problem?

pierre-yves samyn gravatar imagepierre-yves samyn ( 2017-05-29 08:41:11 +0200 )edit

Yes. Thank you so much for your assistance. I was able to publish a portion of the spreadsheet here...

https://docs.google.com/spreadsheets/...

MsAmen gravatar imageMsAmen ( 2017-05-29 14:05:51 +0200 )edit

Windows 8 .... The filter does not filter out all the results, before I filter out the foreign institutions the foreign zip codes are filtered but even then only a handful are presented vs all of them. Once I filter out the foreign institutions no results are presented. In your picture only three results are presented I assure you there are far more zip codes ending in 2 than three. I do appreciate your assistance. I'm running short on time to finish the calculations.

MsAmen gravatar imageMsAmen ( 2017-05-29 18:32:28 +0200 )edit

Unfortunately, you published not the file itself, but its online data (so I e.g. cannot download it to my system to test; can only copy and paste visible data with risk of getting different results).

Could you please provide a link to the file itself?

Mike Kaganski gravatar imageMike Kaganski ( 2017-05-29 18:38:59 +0200 )edit

This is the link from which I downloaded the document directly ....

https://www.dropbox.com/sh/7ufcp56o1w...

MsAmen gravatar imageMsAmen ( 2017-05-29 18:52:13 +0200 )edit

I tried to import the file, and this is what I get - works for me.

this is the 2010-2011 file.

Mike Kaganski gravatar imageMike Kaganski ( 2017-05-29 19:04:03 +0200 )edit

It appears it has worked for you Mike. I'm genuinely confused as I have tried this numerous time's at this point ( standard filter, column d, ends with 3$, regular expression, ) I've run a compatibility test, uninstalled, reinstalled, nothing... no data. I am unsure where the problem lies.... Do either of you have any further suggestions?

MsAmen gravatar imageMsAmen ( 2017-05-29 19:26:25 +0200 )edit

You meant that my files don't work for you, either? What is your LO version?

You could create two additional columns: AJ and AK.

Let's put =MOD(D2,10) into AJ2, and 1 into AK2. Then drag-copy both down, so in AJ, you have last digit of ZIP, and in AK, you have sequential number. Then, select AJ and use Data-Calculate-Formula to Value. After that, you sort everything by AJ, clear 3,5,7, and sort back by AK.

Mike Kaganski gravatar imageMike Kaganski ( 2017-05-30 08:00:47 +0200 )edit

@MsAmen - Does the filter work with the Mike spreadsheet (or with any new spreadsheet)? If so, the problem is with your spreadsheet. If not, it's because it comes from your environment ...

pierre-yves samyn gravatar imagepierre-yves samyn ( 2017-05-30 08:13:33 +0200 )edit

Anyway, we were concentrated on the specific task that you articulated in your question: problems with filtering. But I suppose that filtering is not your final goal, just the means that you decided to use to help you make real end goal. So, while the filtering is interesting problem indeed, we may spend much time on it, while you run out of time. So, in time-critical cases, it's best not only to state the problematic step, but also end result so we can suggest a workaround.

Mike Kaganski gravatar imageMike Kaganski ( 2017-05-30 08:13:48 +0200 )edit

Thank you both so much your help. LibreOffice_5.3.3_Win_x64.msi. No Mike your files would not filter back to the main data but even so I really must be able to complete this task understanding the steps taken to get there so I can duplicate the results.I think must be an issue with my environment. My end goal is to analyze the data recorded for both 2010-2011 and 2015-2016 (minus foreign institutions and those whose zip codes end in 3,5. or 7) to calculate a set of three differential equations.

MsAmen gravatar imageMsAmen ( 2017-05-30 18:09:24 +0200 )edit

I would like to try your solution @MikeKaganski however it is a bit over my head ( I'm a newb with functions- :)

MsAmen gravatar imageMsAmen ( 2017-05-30 18:16:26 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-05-27 19:28:38 +0200

Seen: 153 times

Last updated: May 29 '17