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 MPotentially "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