Filter deleting #N/D

Discuss the spreadsheet application

Filter deleting #N/D

Postby dawidek990 » Wed Jan 16, 2019 3:23 pm

What to do if I have a formula on the list that sometimes shows me #N/D ... to show me the items that do not contain this #N/D?

for example:
asd
ert
fgh
#N/D
#N/D
#N/D
ashdg

and it should be:
asd
ert
fgh
ashdg
OpenOffice 4.1.6 Windows 10
dawidek990
 
Posts: 21
Joined: Thu Dec 13, 2018 10:27 am

Re: Filter deleting #N/D

Postby RusselB » Wed Jan 16, 2019 5:29 pm

I'm going to suggest using Data -> Filter -> Standard Filter
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
RusselB
Volunteer
 
Posts: 4928
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Filter deleting #N/D

Postby dawidek990 » Wed Jan 16, 2019 5:31 pm

is not working, or i dont know how to set this ;]
OpenOffice 4.1.6 Windows 10
dawidek990
 
Posts: 21
Joined: Thu Dec 13, 2018 10:27 am

Re: Filter deleting #N/D

Postby Lupp » Wed Jan 16, 2019 5:38 pm

(Let me replace the "#N/D" ith "#N/A" which is the English / international variant.)

Input =NOT(ISNA(A2)) (or respectively adapted formula) into the second row (or adapted again) of a spare column and fill it down as far as needed. Then apply the Autofilter to the helper column and choose 1 instead of 'All'.

Please note: A filter applied in situ doesn't delete anything. It only (sort of) hides the rows not meeting the filter criteria. Internally Calc also knows if the rows made invisible are 'filtered' or directly hidden by the user.

There is an option to not filter in situ, but to copy the filtered results to a different range. In this case the data not matchingt the criteria are actually not contained in the copied result.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2369
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Filter deleting #N/D

Postby dawidek990 » Fri Jan 18, 2019 1:03 pm

ok, but is not wokring with #ARG!
OpenOffice 4.1.6 Windows 10
dawidek990
 
Posts: 21
Joined: Thu Dec 13, 2018 10:27 am

Re: Filter deleting #N/D

Postby Lupp » Fri Jan 18, 2019 1:56 pm

(I don't know the error "#ARG!" May it be the Polish version of either "#REF!" or "#VALUE!" ?)

The ISNA() function only detects the "#NA" error which may pass relevant information. If you want to detect any error, use ISERROR() instead. If you want to filter out errors of one specific types only, use the ERRORTYPE() function together with a comparator .

(To ease international cooperation in forums everybody can choose an English UserInterface under Tools > Options > Language Settings independent of the locale.)
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2369
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Calc

Who is online

Users browsing this forum: MrProgrammer and 19 guests