[Solved] Select Unique Values and count

Discuss the spreadsheet application

[Solved] Select Unique Values and count

Postby Ganit » Sat Apr 25, 2020 4:33 pm

Hi,
I've got 4 columns and about 30 rows with numbers i want to get list of them without duplicate and count how many duplicates there was. I need to do it with function because numbers often change so it has to update when source change. Any ideas how to do it?
Last edited by MrProgrammer on Fri May 08, 2020 7:09 pm, edited 2 times in total.
Reason: Removed "Issue" icon since no open issue is identified; Tagged ✓ [Solved]
OpenOffice 4.1.7 on Windows 10
Ganit
 
Posts: 1
Joined: Sat Apr 25, 2020 4:27 pm

Re: Select Unique Values and count

Postby MrProgrammer » Sat Apr 25, 2020 6:57 pm

Hi, and welcome to the forum.

Ganit wrote:I've got 4 columns and about 30 rows with numbers i want to get list of them without duplicate and count how many duplicates there was. I need to do it with function because numbers often change so it has to update when source change. Any ideas how to do it?
I do not understand your goal. Do these formulas help? They assume none of the cells are empty.
=SUMPRODUCT(SIGN(FREQUENCY(A1:D30;A1:D30)))   Count of unique values in A1:D30
=COUNT(A1:D30)-SUMPRODUCT(SIGN(FREQUENCY(A1:D30;A1:D30)))   Count of values which have appeared previously
=SUMPRODUCT(COUNTIF(A1:D30;A1:D30)=1)   Count of values which appear only only once
=SUMPRODUCT(COUNTIF(A1:D30;A1:D30)>1)   Count of values which appear multiple times

In the set {3,1,4,1,5,1,4} there are 4 unique values {3,1,4,5}, 3 values which have appeared previously {1,1,4}, 2 values which appear only once {3,5}, and 5 which appear multiple times {1,4,1,1,4}. Your term "duplicate" might refer to the three values {1,1,4} or to the five values {1,4,1,1,4}, thus you need to indicate which of these you mean. Both meanings are used when people ask about "duplicates" on this forum. With U=Unique P=Previous O=Once M=Multiple:
 U U U P U P P
{3,1,4,1,5,1,4}
O M M M O M M
Potentially "duplicates" could also mean the 2 distinct values {1,4} from the M list. This count is |M|-|P| = 5-3 = 2 shown in pink on the bottom line.

If you need additional assistance attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). Explain if you need one count, four counts (one per column), the list of unique values, the list of "duplicates" (whatever that means), or something else. Specify what the correct answer is for your attachment. I will not help further without an attachment or without the correct answer. Empty cells complicate the problem, so unless your attachment shows an empty cell, I will assume that no cells in the range are empty.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3961
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: No registered users and 16 guests