Missing number in a a row function

[Karnik]

Hi Experts,
Request please guide for a function to find missing number in a range:
If Row fix numbers A1 to I1 has following number
1,2,3,4,5,6,7,8,9

A2 to I2 has 1,2,2,3,4,5,6,6,8 then
Function to give results 7, 9 in j3 cell
Thanks in advance
Karnik

[avk]

Try In "J1"

Please Login or Register  to view this content.

copy down.

[BoredWorker]

if you have excel 2016 is better as there have a text join function....

original array formula is

=concatenate(if(COUNTIF(A2:I2,A3:I3)=0,A3:I3&",","")

The result is like 3,4, have a comma is last figure.....
to throw it have to use left and len

=left(A1,len(A1)-1)

So the FINAL ARRAY FORMULA is

Please Login or Register  to view this content.

I believe in real case, it may not a number,
hence, I use the range of instead of type one by one,
(ie =concatenate(if(COUNTIF(A2:I2,{1,2,3,4,5,6,7,8,9})=0,{1,2,3,4,5,6,7,8,9}&",","")

to use the formula, it have to have a list of number or text you want to lookup with, then replace the range A3:I3,
Horizontal table also can. (A3:A13)

Array formula is need to put it by below step,
1. paste the formula in
2. double click on cell
3. Press Ctrl + Shift + Enter

[sandy666]

additional info:

if you have excel 2016 is better as there have a text join function....

with subscription to 365

2016 desktop with no subscription doesn't have this function

[Karnik]

Hi,Thnaks AVK, Boeredworker, & Sandy666
Addressing my issue, it worked!
Cheers
Karnik