How to identify duplicate values in two columns in Openoffice Calc

openoffice-calc
mmmajuri picture mmmajuri · Nov 9, 2012 · Viewed 27.9k times · Source

I have two columns that have numbers in them. The other has the numbers only once, when the other has duplicates. The numbers in these columns don't match. I need to find all the numbers in column B that have a match in column A.

This probably explains it better:
A B
1 2
2 2
4 5
6 5
7 6
8 6

I want to get a result like:
A B C
1 2 1
2 2 1
4 5 0
6 5 0
7 6 1
8 6 1

or any other way for me to identify the cells in column B which don't have a match in column A

I've spent hours googling and trying different solutions but no go, so a nudge to the right direction would be appreciated

Thank you

-M

Answer

Robert Ilbrink picture Robert Ilbrink · Nov 10, 2012

In cell C1 place:

=COUNTIF(A$1:A$6;B1)

And copy down.

Related questions

How to quote a quote with the CONCATENATE function in OOCalc

In OOCalc I want to use the CONCATENATE function to add quotes to each string in column A. So in cell B1 I want to do: =CONCATENATE("\"",A1,"\"") OOCalc does not like this, or without the escaping backslash. Does anyone …

Read More
How to add number of days to a date, consider only business days (i.e. ignore weekends)?

I'm trying to create a formula to calculate the n-th Business Date (only Monday to Friday are business days). For simplicity's sake, holidays are not important; only weekends should be ignored. For example: +------------------------------------------------------------------ | A B C +------------------------------------------------------------------ 1 | Starting Date …

Read More
Join two spreadsheets on a common column in Excel or OpenOffice

I have two CSV files with a common column and I want to "Join" the tables together on the common column. For example: Join 'A' with 'B' equals 'Result'. If a one table has a key value that does not …

Read More