Advanced Find - loop

[travellingred]

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!!

 

[texasalynn]

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]

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]

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!!