# how to remove non duplicates in libreoffice calc

How do I remove non-duplicate cells in calc? the purpose is to delete all the rows that contain a value that exists only in one collumn. ie:

``````A     B
1     1
2     3
3     5
5     10
7     12
8
9
10
12
``````

here the rows that contain 2,7,8,9 would be deleted

edit: I've been looking at function wizard, the answer may lie in the database category, but I don't understand most of it.

## 1 Answer

To do this for the first 127 (for 500 you get Err:512) rows, move to cell C1 and paste the stuff in the bottom. Then copy C1 down to the bottom of what you want to sort.

You will then get the value `TRUE` in cell Cx if the cell Ax has a value that occurs in one or more of B1,...,B127, else `FALSE`.

Next, go to the `Data` menu, and choose `Sort...` Under `Sort key 1`, choose column `C`. This will sort your table so all `FALSE` entries will be on top.

Then select A1 and B1, hold shift and scroll down until you reach the first `TRUE` field. Then hit delete.

You will be left with only the rows where the value in cell Ax occurs in one or more of the cells in column B.

`=OR(A1=B\$1,A1=B\$2,A1=B\$3,A1=B\$4,A1=B\$5,A1=B\$6,A1=B\$7,A1=B\$8,A1=B\$9,A1=B\$10,A1=B\$11,A1=B\$12,A1=B\$13,A1=B\$14,A1=B\$15,A1=B\$16,A1=B\$17,A1=B\$18,A1=B\$19,A1=B\$20,A1=B\$21,A1=B\$22,A1=B\$23,A1=B\$24,A1=B\$25,A1=B\$26,A1=B\$27,A1=B\$28,A1=B\$29,A1=B\$30,A1=B\$31,A1=B\$32,A1=B\$33,A1=B\$34,A1=B\$35,A1=B\$36,A1=B\$37,A1=B\$38,A1=B\$39,A1=B\$40,A1=B\$41,A1=B\$42,A1=B\$43,A1=B\$44,A1=B\$45,A1=B\$46,A1=B\$47,A1=B\$48,A1=B\$49,A1=B\$50,A1=B\$51,A1=B\$52,A1=B\$53,A1=B\$54,A1=B\$55,A1=B\$56,A1=B\$57,A1=B\$58,A1=B\$59,A1=B\$60,A1=B\$61,A1=B\$62,A1=B\$63,A1=B\$64,A1=B\$65,A1=B\$66,A1=B\$67,A1=B\$68,A1=B\$69,A1=B\$70,A1=B\$71,A1=B\$72,A1=B\$73,A1=B\$74,A1=B\$75,A1=B\$76,A1=B\$77,A1=B\$78,A1=B\$79,A1=B\$80,A1=B\$81,A1=B\$82,A1=B\$83,A1=B\$84,A1=B\$85,A1=B\$86,A1=B\$87,A1=B\$88,A1=B\$89,A1=B\$90,A1=B\$91,A1=B\$92,A1=B\$93,A1=B\$94,A1=B\$95,A1=B\$96,A1=B\$97,A1=B\$98,A1=B\$99,A1=B\$100,A1=B\$101,A1=B\$102,A1=B\$103,A1=B\$104,A1=B\$105,A1=B\$106,A1=B\$107,A1=B\$108,A1=B\$109,A1=B\$110,A1=B\$111,A1=B\$112,A1=B\$113,A1=B\$114,A1=B\$115,A1=B\$116,A1=B\$117,A1=B\$118,A1=B\$119,A1=B\$120,A1=B\$121,A1=B\$122,A1=B\$123,A1=B\$124,A1=B\$125,A1=B\$126,A1=B\$127)`

• I've edited the question again, hope is clear now. @Rasmus – Jo Rijo Oct 31 '12 at 17:37
• How many rows do you have? – Rasmus Oct 31 '12 at 17:52
• How is that relevant? sometimes 10 sometimes 500. – Jo Rijo Oct 31 '12 at 18:03
• Because I don't know how to make Calc quantify over the cells, testing if there exists one with same value, but the same can be obtained by a long OR statement: `=OR(A1=B\$1,A1=B\$2,A1=B\$3,A1=B\$4,...)` but that get tedious for 500 rows... With appropriate length, this would give you what you are looking for. – Rasmus Oct 31 '12 at 18:18
• Alright, that should do it, up to 500. Hooray for `=A1+1` and text editors with for multi-line editing! – Rasmus Oct 31 '12 at 18:40