4
  • Column A has a sorted-descending list of some bum's Top-250 movies, in the following format: Apocalypse Now (1979)
  • Column B has a sorted list of My Top-100, in the same format.
  • Both lists have been copied and pasted into a Notepad text doc to confirm they are similar simple ASCI text – no extra spaces at the end – etc. - and then pasted back into LibreofficeCalc.

I need a function for Column C that shows any of MY movies (B) that he has NOT listed in (A).

Psudo code:

  • C1 = The cell value in B1 – is it anywhere in A1:A8000? If not – put B1 value into C1, otherwise leave blank.
  • C2 = The cell value in B2 – is it anywhere in A1:A8000? If not – put B2 value into C2, otherwise leave blank.
  • Etc.

I have searched and found these functions – none of which work, for whatever reason. I've modified them to 8000 as the upper range which I don't think I'll ever approach.

=IF(ISERROR(MATCH(B1,$A$1:$A$8000,0))=1,B1,"")
=IFERROR(MATCH(B1;$A$1:$A$8000;0);"")
=IFNA(VLOOKUP($B1;$A$1:$A$8000;1;0);"")
=IF(ISNA(VLOOKUP($B1;$A$1:$A$8000;1;0));"";VLOOKUP($B1;$A$1:$A$8000;1;0))
=IF(ISNA(VLOOKUP($B1,$A$1:$A$8000,1,0)),"",VLOOKUP($B1,$A$1:$A$8000,1,0))
=VLOOKUP(B1,$A$1:$A$8000,1,) 
=MATCH($B1;$A$1:$A$999;0)

I'd prefer it to be a single cell function, and not VBA.

I actually solved this back in like 2001 using Excel. The trick then was I had to edit the cell and use Ctrl-Shift-Enter to create a “dynamic array”, so the function was bracketed in {} curly brackets. But now I'm using the latest LibreOffice Calc and can't get the @#$# syntax correct.

Thank you!!

Edit NOTE: testing with "A" and "00001" numbers produces very different results. Values have to look like this in both columns:

  • Alice (1988)
  • Barfly (1987)
  • Clueless (1995)
  • etc.
Larry
  • 165
  • 1
  • 8
  • Go to Tools→Options→LibreOffice Calc→Calculate and UNcheck "Enable regular expressions in formulas" and that will fix the problem with the functions interpreting the parenthesis as part of a regular expression. – Lyrl Apr 06 '16 at 18:32
  • Holy cow-moly! THIS^ !! You just solved it cold. Thank you so much. I'm sure all three answers below deserve more than the one up-tick I gave each, but I'll accept our man Tom (and obviously you deserve more than my thanks, but thanks anyway.. : ) – Larry Apr 07 '16 at 16:25

3 Answers3

4

OK I've tested these in Open Office with the following results:-

=IF(ISERROR(MATCH(B1,$A$1:$A$8000,0))=1,B1,"")

Gives Error 508 because the commas need changing to semicolons. 

**=IF(ISERROR(MATCH(B1;$A$1:$A$8000;0))=1;B1;"")**

is fine.

=IFERROR(MATCH(B1;$A$1:$A$8000;0);"")

Gives #Name? because IFERROR isn't recognised.

=IFNA(VLOOKUP($B1;$A$1:$A$8000;1;0);"")

Gives #Name? because IFNA isn't recognised.

=IF(ISNA(VLOOKUP($B1;$A$1:$A$8000;1;0));"";VLOOKUP($B1;$A$1:$A$8000;1;0))

Works but gives the opposite result.

**=IF(ISNA(VLOOKUP($B1;$A$1:$A$8000;1;0));B1;"")**

would be fine.

=IF(ISNA(VLOOKUP($B1,$A$1:$A$8000,1,0)),"",VLOOKUP($B1,$A$1:$A$8000,1,0))

Commas

=VLOOKUP(B1,$A$1:$A$8000,1,)

Commas

=MATCH($B1;$A$1:$A$999;0)

Works but just gives the position of the match.

Probably the easiest way of doing it is:-

**=IF(COUNTIF(A$1:A$8000;B1);"";B1)**

Unfortunately it does seem that strings with brackets in are giving spurious matches in Libre/Open Office. You could get round it by a substitution I guess

=IF(COUNTIF(SUBSTITUTE(SUBSTITUTE(A$1:A$10;"(";"<");")";">");SUBSTITUTE(SUBSTITUTE(B1;"(";"<");")";">"));"";B1)

entered as an array formula and copied (rather than pulled) down or of course global edit all the brackets :-(.

enter image description here

Now that I know the root cause of this thanks to @Lyrl, there is a further option of turning off the regular expressions as suggested or you could escape the brackets:-

=IF(COUNTIF(A$2:A$11;SUBSTITUTE(SUBSTITUTE(B2;"(";"\(");")";"\)"));"";B2)

See documentation on Regex in Open Office here

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • None of those work for me. There is no differentiation between the B value being in the A column or not. All functions above just copy down the same. (Those were the functions that DID NOT work for me. I'm looking for one that does.) – Larry Apr 05 '16 at 12:28
  • I thought it might be useful to say exactly why the various formulae didn't work. Anyway, have starred three alternatives that definitely work for me in Open Office - hope that helps. Don't include the stars of course. – Tom Sharpe Apr 05 '16 at 13:14
  • Your ** answer works in Excel – but not in my LibreOffice-Calc. Odd. I have completely standard installs on three different machines (Win10, Win7, Unix) - none work for LibreOffice. – Larry Apr 05 '16 at 13:44
  • I don't know to be honest. One thing that can mess up is double quotes - worth trying say IF(COUNTIF(A$1:A$8000;B1);0;B1) just to see if it works – Tom Sharpe Apr 05 '16 at 14:41
  • Same result. But wait, “Countif” will only work for numeric fields, right? So it's not going to work no matter what, as I'm talking about text values... : / – Larry Apr 05 '16 at 14:58
  • Countif does work on text fields so it can't be that. How about putting some Mickey Mouse data in to test it like just a b c in col A and c d e in col B? – Tom Sharpe Apr 05 '16 at 15:23
  • Okay. Simple numbers work. Simple letters work. But combining them “A (24)” has mixed results. “A 24” in (A) will kick “A 24” in (B), BUT, it will also kick “A (24)” value - so the parens are messing it up somehow..... Unfortunately, per the OP, I have parens in every line-item in both columns. I suspect I have to force-qualify every variable as “text(A1)” kind of thing – and embed that in the function. – Larry Apr 05 '16 at 16:04
  • You're right - brackets are somehow getting parsed out in Open Office but not in Excel. Could use a messy substitution - will put it in my answer. – Tom Sharpe Apr 05 '16 at 18:12
  • Copy-paste regular = “Err:504”. Ctrl-Shit-Enter does the dynamic array (old-school, like I mentioned in OP) so that last function in your answer does bring back a result, but it's the same problem: all of (C) is just a dupe of (B) without any differentiation if the value is in (A) or not. Bummer.... – Larry Apr 05 '16 at 18:31
  • Open Office wants to copy the whole thing down as an array formula. You can go along with it as =IF(COUNTIF(SUBSTITUTE(SUBSTITUTE(A$2:A$11;"(";"<");")";">");SUBSTITUTE(SUBSTITUTE(B$2:B$10;"(";"<");")";">"));"";B$2:B$10) – Tom Sharpe Apr 05 '16 at 19:01
  • 1
    @TomSharpe - try disabling regular expressions (in the "Calculate" part of the Tools→Options→Calc menu) and I think your formulas will work much better, not displaying any parenthesis weirdness – Lyrl Apr 06 '16 at 18:34
  • Sounds a great idea - I will try it tonight when I have access to a copy of Open Office – Tom Sharpe Apr 07 '16 at 08:17
  • Of course this works perfectly - happy to give credit if you would like to post it as an answer. – Tom Sharpe Apr 07 '16 at 17:38
1

This should do it,

=IF(ISNUMBER(MATCH(B1,$A$1:$A$8000,0)),"",B1)
Abhishek Ashish
  • 46
  • 1
  • I copy and past that into C1, and copy it down, and ALL FIELDS are copies from B. Does not work. On this particular machine I have Windows 7 with all updates, and the latest LibreOffice. US resident, nothing funky setup wise. Not working. – Larry Apr 05 '16 at 12:26
  • Check advanced settings of your office, automatic calculation of formulas is disabled. Enable it and it should work. – Abhishek Ashish Apr 05 '16 at 15:18
  • Tools – Cell Contents – AutoCalculate: is checked. That is exactly the kind of thing in Excel. You'd have to hit F5 or F9 or whatever it was to recalc a sheet if that was turned off. Thank you for idea. But (per below comments) the problem is probably in the parens. The data (per OP) all has parens surrounding the release dates, and that is messing up the calc somhow. – Larry Apr 05 '16 at 16:09
1

Tested formula

=IF(ISNA(MATCH(B1,$A$1:$A$8000,0))=TRUE(),B1,"")
Karthick Gunasekaran
  • 2,697
  • 1
  • 15
  • 25
  • What I'm getting is a direct copy of B values into C for every cell. No blanks (where there is a dupe.) I'm even copying and pasting a specific A cell value into B – so I know the values are exactly the same. I'm also highlighting all three columns and formatting them at “text”. I've never modified anything in Calc, but maybe it's not refreshing correctly or something??? – Larry Apr 05 '16 at 12:45