finding differences between two rows of numbers

[excelNewbie22]

hi, how can i know how many differences of 1 or -1 there is between two set of numbers in two lines (no matter the order) with one formula?
like in the example, between c2:h2 and c3:h3 (aka lines 2 and 3)



couldn't copy from xl2bb, don't know why
there is no formulas in the example pic



p.s
maybe someone can help me with that too?

prime numbers and repeats

hi. is it possible for a formula to write down the actual prime numbers in certain range? and same thing for repeating numbers out of previous rows? and in third column to compare the last 2 and write down the same one's? this is what u help me out with: CDEFGHIJK1123456primefrom last 2compare...

www.mrexcel.com

 

[Peter_SSs]

Try this. In your Excel version it may need entry confirmation with Ctrl+Shift+Enter, not just Enter.

21 09 05.xlsm
	C	D	E	F	G	H	I
2	2	4	6	8	10	12	5
3	1	5	11	20	30	35	3
4	4	7	10	22	29	30
Sheet2 (4)

Cell Formulas
Range	Formula
I2:I3	I2	=SUMPRODUCT(--(ABS(C2:H2-TRANSPOSE(C3:H3))=1))

 

[excelNewbie22]

excellent!
thank you!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.