List Missing Numbers in a Sequence Using New Formulas?

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
686
Office Version
  1. 365
Platform
  1. Windows
Is there a way to extract numbers in a sequence? For example, in the Sequence 1-10 in column A, 7 and 9 are missing. That is what my answer would be.

43EB1ACC-669F-4820-98B0-E4C390199165.jpeg
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
Option

you can Expand Your Range ROW(INDIRECT("1:10")) 'ROW(INDIRECT("1:250"))
Book1
AB
1NumMis
212
356
448
5310
67 
7 
8 
99 
10 
Sheet1
Cell Formulas
RangeFormula
B2:B10B2=IFERROR(AGGREGATE(15,6,(ROW(INDIRECT("1:10")))/(COUNTIFS($A$2:$A$751,(ROW(INDIRECT("1:10"))))=0),ROWS($A$2:B2)),"")
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,698
Office Version
  1. 365
Platform
  1. Windows
Hi Stephen
One issue I notice with your suggestion in the other thread is that the spill range will always be N rows which could be a negative in some circumstances I would have thought.

Another option with minimal spill range here would be

21 02 10.xlsm
AB
117
229
33
44
55
66
78
810
9
10
Missing
Cell Formulas
RangeFormula
B1:B2B1=FILTER(SEQUENCE(10),ISNA(MATCH(SEQUENCE(10),A1:A10,0)),"")
Dynamic array formulas.
 
Solution

Forum statistics

Threads
1,147,482
Messages
5,741,406
Members
423,657
Latest member
Medrok2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top