count unique values in large range while excluding previous results

[excelNewbie22]

hi!
i want to count unique values from range of four cells, which sometimes will include duplicates, c2:f2
against a range of 8 cells, c3:f4,
for the example below, in c2:f2 there's two 2's, one 3, one 4,
in c3:f4 there's two 2's, three 3's, two 4's,
the wanted result is 4

uniques.xlsb
	C	D	E	F	G	H	I	J	K
1	1	2	3	4		f1	f2		wanted result
2	2	2	3	4		3	0		4
3	2	3	2	4		3	0		3
4	3	1	3	4		3	0		3
5	2	1	7	4
6	2	5	8	2
test

Cell Formulas
Range	Formula
H2:H4	H2	=CountUnique($C$2:$F$2,C3:F4)
I2:I4	I2	=SUM(IF(FREQUENCY(IFERROR(MATCH(C2:F2,C3:F4,0),""),COLUMN(C2:F2)-COLUMN(C2)+1)>0,1))

tried the udf countunique, the original, and it doesn't work with two ranges if the second one is consist of two rows,

Count unique distinct values in a large dataset [UDF]

This article describes how to count unique distinct values in list. What is a unique distinct list? Merge all duplicates to one distinct value and you hav

www.get-digital-help.com

tried a modified one, and thought it worked, until tested with 4 values and still return only 3

UDF for Counting Unique Text Within Date Range

Hi all, I have this wonderful User Defined Function below that counts unique text within a range. The problem is you cant specify a date range as as in COUNTIFS functions. Can this code be modified to specify date ranges? For example =CountUnique(Date_rng,">=01/09/2012",Date_rng,"

www.mrexcel.com

tried:
=SUM(IF(FREQUENCY(IFERROR(MATCH(C2:F2,C3:F4,0),""),COLUMN(C2:F2)-COLUMN(C2)+1)>0,1))
but it doesn't work with range of two rows

nothing seems to work
any help?

 

[jasonb75]

I think we are going to need a clearer explanation of how you're getting your results.

How do you get 4 from the example that you described?

How do you get 3 from the other rows? Trying to figure out what you actually want, I think that the last one should be 2 instead of 3.

 

[excelNewbie22]

i will try,
i want to count how many repeats of c2:f2 = 2-2-3-4 is there in c3:f4,
meaning count how many 2's there is in c3:f4 and if there's more then 2 exclude the rest,
and same for how many 3's and 4's

I think that the last one should be 2 instead of 3

you refers to k4? there's 3 because in c5:f5 = 2-1-7-4 ---> one 2 and one 4
and in c6:f6 there's 2-5-8-2 ----> two 2's
therefore 3 out of c2:f2 = 2-2-3-4

 

[jasonb75]

I can see what you're asking for now, I'm going to see if I can do anything with native excel functions before putting it into a UDF.

 

[jasonb75]

This looks like it works as needed but I've not tested beyond the sample that you provided.

data bar formatting.xlsm
	C	D	E	F	G	H	I	J
1	1	2	3	4		f1		wanted result
2	2	2	3	4		4		4
3	2	3	2	4		3		3
4	3	1	3	4		3		3
5	2	1	7	4
6	2	5	8	2
Sheet3

Cell Formulas
Range	Formula
H2:H4	H2	=LET(c,COUNT($C$2:$F$2),arr,INDEX(FREQUENCY(IFERROR(MATCH(C3:F4,$C$2:$F$2,0),c+1),SEQUENCE(c+1)),SEQUENCE(c)),ref,TRANSPOSE(COUNTIF($C$2:$F$2,$C$2:$F$2)),SUM(IF(arr<ref,arr,ref)))

 

[Peter_SSs]

I'm wondering if it could be this? I have assumed all 1-digit numbers like the samples.

excelNewbie22.xlsm
	C	D	E	F	G
1	1	2	3	4	Result
2	2	2	3	4	4
3	2	3	2	4	3
4	3	1	3	4	2
5	2	1	7	4
6	2	5	8	2
Sheet3

Cell Formulas
Range	Formula
G2:G4	G2	=8-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCAT(C3:F4),C2,"",1),D2,"",1),E2,"",1),F2,"",1))

 

[jasonb75]

Trust me to over complicate things

Although, the mention of a "large range" in the thread title does have me wondering how close the sample is to the actual problem.

 

[Peter_SSs]

Trust me to over complicate things

I don't know for sure what the actual requirement is but I thought that our two suggestions actually do different things.

Mine compares
C2:F2 with C3:F4, then
C3:F3 with C4:F5, then
C4:F4 with C5:F6
I was swayed this way by ..

I think that the last one should be 2 instead of 3

Doesn't yours compare the following?
C2:F2 with C3:F4, then
C2:F2 with C4:F5, then
C2:F2 with C5:F6

 

[jasonb75]

The line that you quoted from the OP is actually them quoting my reply above, Peter. In post 1 the UDF had absolute references for C2:F2 while the FREQUENCY formula had relative references.
I started off using relative (which is why I thought that the last one should have been 2) but then looked at it again using absolute and found that I had the same results as the OP so went with that.

I think that both formulas will actually do the same thing if the data is consistent with the example, depending on whether absolute or relative refs are used.

Naturally, I'm expecting that both will require some adjustments when the OP tries them with their actual data.

 

[excelNewbie22]

jason thank you very much!
peter, much appreciated too!
both your solutions works!