#### travellingred

##### New Member
Hi all

I've got a spreadsheet which contains, in column L, a field entitled Unique Reference Number (URN). The data is over 9000 rows long.

This URN field contains an integer. Each unique integer appears between 1 and X times within the dataset. I'm trying to extract any integers in a sequence that are missing.

I thought about creating a cell (Q1) with the first URN in it. Then in a separate cell (Q2) nesting a =COUNTIF(L1:L9000,Q1) which would return the number of times the first URN appears in the list. I then thought that if I could do a loop such that if the output from Q2 is greater than or equal to 1 then Q1 is incremented by 1 and the Q2 calculation runs again.

This loop would then either break each time a URN is missing from the list, or (preferably) could output into a separate column a list of each value of Q1 which generates a zero response to the COUNTIF command in Q2.

Is this possible? And if so, how do I do it?

Many thanks!!

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### texasalynn

##### Well-known Member
are you just wanting a list of missing sequential numbers

1230
1231
1235
1236

so from this list the missing ones
1232
1233
1234

is that what you mean? If not please post a small sample of your data and what you are expecting

#### hkaplan2

##### Active Member
You could do something like this:

Place the list of valid values in column B. Assume your original list is in column A.

Highlight all cells in column C down to the last value in column B and enter this formula, then confirm with Ctrl + Shift+Enter.

{=IF(B1:B16<>A1:A23,B1:B16,"")}

Every valid number that is not in column A will appear in column C.

#### tusharm

##### MrExcel MVP
Couple of Excel-native ways to do this...

Suppose your data are in column B (adapt to your own setup where the data are in col. L) starting with B2. In my test the data extended to B8. My URN's were 1, 2, ..., 9 with 4 and 6 (i.e., 2 values) missing.

In D2 enter the smallest URN of interest. In my case since all were of interest, I entered 1. Create a sequential list of all URNs of interest in col. D. Since I was interested in all values between 1 and 9, I entered 1, 2,..., 8,9 in D2:D10.

In E2 enter =COUNTIF(\$B\$2:\$B\$8,D2) and copy E2 as far down E as is data in D.

Now, option 1 is to set an autofilter on D:E and filter E so that only the zeros are visible. This is by far the easiest and the fastest.

Option 2 is to go with a more complex formula approach -- and I imagine there are others here who might be able to 'improve' on the below.

In F2 enter =IF(ROW()-ROW(\$F\$2) < COUNTIF(\$E\$2:\$E\$10,0),0,"") and copy down F until you get a cell that looks empty.

In G2 enter the array formula =IF(F2<>"",INDEX(\$D\$2:\$D\$10,SMALL(IF(\$E\$2:\$E\$10=0,ROW(\$E\$2:\$E\$10)-ROW(\$E\$2)+1),COUNTIF(\$F\$2:F2,0))),"")

Copy G2 down col. G until you get a cell that looks empty.

To enter an array formula complete data entry with the CTRL+SHIFT+ENTER combination rather than just the ENTER or TAB key. If done correctly, *Excel* will show the formula with curly brackets { and }

Hi all

I've got a spreadsheet which contains, in column L, a field entitled Unique Reference Number (URN). The data is over 9000 rows long.

This URN field contains an integer. Each unique integer appears between 1 and X times within the dataset. I'm trying to extract any integers in a sequence that are missing.

I thought about creating a cell (Q1) with the first URN in it. Then in a separate cell (Q2) nesting a =COUNTIF(L1:L9000,Q1) which would return the number of times the first URN appears in the list. I then thought that if I could do a loop such that if the output from Q2 is greater than or equal to 1 then Q1 is incremented by 1 and the Q2 calculation runs again.

This loop would then either break each time a URN is missing from the list, or (preferably) could output into a separate column a list of each value of Q1 which generates a zero response to the COUNTIF command in Q2.

Is this possible? And if so, how do I do it?

Many thanks!!

Last edited:

Replies
18
Views
501
Replies
2
Views
116
Replies
2
Views
337
Replies
3
Views
235
Replies
2
Views
240

1,190,836
Messages
5,983,166
Members
439,824
Latest member
jr599

### 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.

### Which adblocker are you using?

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

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