Ask Your Question

How to aggregate one column with respect to another [closed]

asked 2014-04-01 19:53:25 +0200

Cerin gravatar image

I have a large spreadsheet with two columns, "ID", and "Name". Is there a function in LibreCalc to tell me which values of ID correspond to more than one unique name?

In SQL, this would be SELECT name, COUNT(DISTINCT name) AS name_count FROM table GROUP BY name HAVING name_count > 1;, but I'd like to avoid having to setup a SQL database just to perform this check.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-20 16:01:52.728110

1 Answer

Sort by » oldest newest most voted

answered 2014-04-01 21:16:33 +0200

mahfiaz gravatar image

If I get you right you just want to make sure all the names are unique. In a third column do =COUNTIF($B$1:$B$1000; B1), every row where count is > 1 has a duplicate.

So on which row the duplicate is? In the fourth column do something like this: =IF(C1>1;MATCH(B1;$B2:$B$1000;0)+ROW(B2)-1;"")

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2014-04-01 19:53:25 +0200

Seen: 424 times

Last updated: Apr 01 '14