Formula to add missing numbers in a given sequence.

[MarkPD]

Hi everyone,

I'm just a beginner Excel user and spend time reading and searching Excel formulas available online, so please bear my naivety. I'm creating a table in which it can list the missing numbers in a column. I'm using a series of numbers found in the official receipt for example, for my data. I've found an array that made that possible =SMALL(IF(ISERROR(MATCH($T$3+ROW(OFFSET($T$3, 0,0, $T$4-$T$3-1))-2, $T$3:$T$4,0)), $T$3+ROW(OFFSET($T$3,0, 0, $T$4-$T$3-1))-2), ROW(A1)) ,though I can't locate the website where I got the array so I do apologize. Now I want to extract the non-duplicate numbers in a different column for tracking purposes in which I can't figure out a way to do it. I am humbly asking for assistance from the expert here to help me complete this. Thank you in advance.

 

[Fluff]

Hi & welcome to MrExcel.
Not sure what you mean by "extract the non-duplicate numbers", but you can simplify the formula in T6 down like

=IF(T$3+ROWS(T$6:T6)>=T$4,"",T$3+ROWS(T$6:T6))

 

[MarkPD]

Hi & welcome to MrExcel.
Not sure what you mean by "extract the non-duplicate numbers", but you can simplify the formula in T6 down like

=IF(T$3+ROWS(T$6:T6)>=T$4,"",T$3+ROWS(T$6:T6))

Thank you for your response Fluff, I updated my table with the formula you provided me.

 

[MarkPD]

About extracting the non-duplicate numbers or the unique values. I have attached a screenshot of the table (11 columns, Column T to AD) I want to extract the unique numbers that has no duplicate to B6 cell for example.

 

[MarkPD]

I'm sorry, forgot to insert the screenshot. Here it is.

 

[Fluff]

As the only unique numbers will be those between AD4 & T4 you can use this in B6 dragged down

=IF(AD$4+ROWS(B$6:B6)-1>=T$4,"",AD$4+ROWS(B$6:B6)-1)

 

[MarkPD]

As the only unique numbers will be those between AD4 & T4 you can use this in B6 dragged down

=IF(AD$4+ROWS(B$6:B6)-1>=T$4,"",AD$4+ROWS(B$6:B6)-1)

This brings me closer to completion, thanks. How can I expand the selection from AD4 & T4 to T6 up to AD56?. Is it possible to extract non duplicate number from multiple columns and rows?

 

[Fluff]

I know of no way of doing that with your version of Xl, although with the layout you have shown it will be every value in cols V:AD

 

[Fluff]

Ignore the previous post, how about

=AGGREGATE(15,6,$T$6:$AD$56/(COUNTIFS($T$6:$AD$56,$T$6:$AD$56)=1),ROWS(B$6:B6))

 

[MarkPD]

Ignore the previous post, how about

=AGGREGATE(15,6,$T$6:$AD$56/(COUNTIFS($T$6:$AD$56,$T$6:$AD$56)=1),ROWS(B$6:B6))

Thank you so much! It works now. I deeply appreciate your help on this.