Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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)

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.