populate repeating new numbers only?

[excelNewbie22]

hi!

how can i populate repeated unique numbers to the next cell?
like in row c3:h3 there's 2 repeating numbers,
and in c4:h4 there's also 2 but one of them already appeared in c3:h3 so i need a way to populate only the new one's,
way maybe to use the formula in j col but with subtract/minus the previous result?
and also, formula the only count the new one's?

test.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	1	2	3	4	5	6			wanted result	for $C$2:$H$2,C3:H3	for $C$2:$H$2,C4:H4	$C$2:$H$2,C5:H5	$C$2:$H$2,C6:H6
2	1	2	3	4	5	6
3	5	6	7	8	9	10		2	5	6	2	3	-
4	2	5	11	12	13	14		2
5	3	15	16	17	18	19		1
6	7	8	9	10	11	12		0
test

Cell Formulas
Range	Formula
J3:J6	J3	=SUM(--(COUNTIF($C$2:$H$2,C3:H3)>0))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2040:J1048576,J3:J2038	Cell Value	=3	text	NO

 

[jasonb75]

Is this something that you can work with?

Note that TEXTSPLIT is quite new so you may not have it yet.

Book1
	C	D	E	F	G	H	I	J	K	L	M
1	1	2	3	4	5	6
2	1	2	3	4	5	6
3	5	6	7	8	9	10		5	6
4	2	5	11	12	13	14		2	5
5	3	15	16	17	18	19		3
6	7	8	9	10	11	12
7
8								5	6	2	3
Sheet1

Cell Formulas
Range	Formula
J5:J6,J3:K4	J3	=IFERROR(TRANSPOSE(MODE.MULT($C$2:$H$2,C3:H3)),"")
J8:M8	J8	=TRANSPOSE(UNIQUE(TEXTSPLIT(TEXTJOIN(",",1,J3:K6),,",")))
Dynamic array formulas.

 

[excelNewbie22]

thanks jason,
though i need without the k4, because it's already in j3,
i need to know per row how many new one's there is

any way to "subtract" j3 from j4?

 

[jasonb75]

I was actually using J3:K6 as a helper for the unique list in J8:M8 rather than it being the final result but I can see why you would want it removed from K4 as well.

This one appears to work correctly but I have only done one very quick test on it.

Note that J2:O2 must be empty. If your actual data contains more than 6 columns then you will need the equivalent number of empty columns for the formula to work with.
Likewise J3:O6 can not contain anything other that the dynamic array formula populated from column J, as with the point made above, the number of columns reserved for this needs to be equal to the number of columns in the data table.

tourless.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O
1	1	2	3	4	5	6
2	1	2	3	4	5	6
3	5	6	7	8	9	10		5	6
4	2	5	11	12	13	14		2
5	3	15	16	17	18	19		3
6	7	8	9	10	11	12
7
8								5	6	2	3
Sheet1

Cell Formulas
Range	Formula
J3:K3,J4:J6	J3	=IFERROR(TRANSPOSE(MODE.MULT($C$2:$H$2,IF(COUNTIF(J$2:O2,C3:H3)=0,C3:H3))),"")
J8:M8	J8	=TRANSPOSE(UNIQUE(TEXTSPLIT(TEXTJOIN(",",1,J3:K6),,",")))
Dynamic array formulas.

 

[excelNewbie22]

many thanks jason!