1

I have a list of numbers that are in numerical order and it repeats starting from 1. The last number in each series varies. Example: there'll be sequence of 1-7, 1-12, 1-24, etc.

I need a formula that finds missing numbers within these series. Some of these series has just one number. This was taken from my music collection sorted by albums and the purpose of this is to find albums with missing tracks.

I hope the screenshot is clear and explained well enough.

screenshot

EDIT: I added a better screenshot for clarification.

screenshot 2

Improve this question
4
  • 1
    What do you want to achieve? What do you mean by missing nos. ? I could find none in the screenshot. Clarify.
    – user871532
    Commented May 19, 2020 at 7:27
  • Please also add desired output to your screenshot. How do you want to "identify"?
    – Máté Juhász
    Commented May 19, 2020 at 7:51
  • Hi @Beast Wolf , you mean to say that U wanna to print 2, 3, 4, 5, 6 for (1 to 7) ?
    – Rajesh Sinha
    Commented May 19, 2020 at 9:30
  • I added another screenshot. I hope that is more clear. Sorry this is difficult to explain.
    – Beast Wolf
    Commented May 19, 2020 at 10:44

3 Answers 3

Reset to default
0

This solution looks for gaps and shows the first missing item in the gap.

Each item in the list must be either:

  • one plus the previous item
  • one itself

With data in column A, in B1 enter:

=IF(OR(A2=A1+1,A2=1),"",A2-1)

and copy downward:

enter image description here

The weakness of this simple approach is that it shows only the first missing item if there are several missing items in sequence.

NOTE:

If you are using Excel 365, a more complete output is possible. For Excel 365, the formula would be:

=IF(OR(A2=A1+1,A2=1),"",SEQUENCE(1,A2-A1-1,A1+1))

enter image description here

In the illustration above, there are two missing items between 6 and 9. Both missing items are displayed in the output.

Improve this answer
4
  • Yes! This is exactly what I needed. I discovered a few albums that had a missing track and also tracks that were in the wrong order. I ran it again and now there are no more errors. Thank you so much!
    – Beast Wolf
    Commented May 19, 2020 at 12:29
  • Hi @Gary's Student,, how one could ignore/skip blanks with 1st method,, like 5 should at B2 !!
    – Rajesh Sinha
    Commented May 20, 2020 at 10:38
  • @RajeshS I will have to think about it.
    – Gary's Student
    Commented May 20, 2020 at 10:50
  • Thanks @Gary'sStudent,, I've tried this sequence 1 3 5 8 10 1 5 8 10 11 1 2 5 6 9 and formula unable to generate correct sequence !
    – Rajesh Sinha
    Commented May 20, 2020 at 11:03
1

:Caveat:

My solution is based on comment above responded by OP to @Bingo, " formula that can find sequence gaps within one column that would be great ".

enter image description here

  • An array (CSE) Formula in Cell AI2:

    {=IFERROR(SMALL(IF(COUNTIF($AH$2:$AH$5,ROW($1:$24))=0,ROW($1:$24),""),ROW(AH1)),"")}

N.B.

  • Finish formula with Ctrl+Shift+Enter and fill down.

  • ROW($1:$24) is editable it should start and end Number of missing sequence (1 to 24).

Adjust cell references in the formula as needed.

Improve this answer
4
  • This only works for one sequence. In your example, it's a sequence of 1 to 24. But in my sheet I have multiple sequences all with different totals (some 1-10, some 1-24, some 1-7, etc). This was taken from my music album collection and each album has a different number of tracks within.
    – Beast Wolf
    Commented May 19, 2020 at 10:37
  • @BeastWolf,, U have given twist to the question after I've posted my answer,,, and above the suggested method works with office 365,,, if U r using office 365 edit your post and mention which version you are using also add new TAG to the question !!
    – Rajesh Sinha
    Commented May 20, 2020 at 10:34
  • The question clearly says MULTIPLE sequences so there is no "twist".
    – Beast Wolf
    Commented May 31, 2020 at 2:08
  • @BeastWolf,, initial I found was different,,, any ways it's up to U since U are the OP,,, but read comment above with #Gary'sStudent post ,,, I've raised few crucial issues!!
    – Rajesh Sinha
    Commented May 31, 2020 at 4:54
0

If I understand correctly, your screenshot may just show series has just one number.

I suggest you add a secondary column to fill all the numbers you should have.

Then enter the following formula in another column:

=VLOOKUP(A1,B:B,1,0)

In my screenshot below, column A is filled with all the numbers that should be numbered, and column B is actually the number that is now available:

For example, C11 shows "N/A", so the number missing from this cell is "11" in column A

enter image description here

Improve this answer
1
  • Unfortunately it's not feasible to separate each series of sequential numbers into separate columns because I'm dealing with a collection of over 3,800 albums. I used an MP3 software to sort all the tracks by albums and in sequential order then extracted the track numbers into text. That's how I ended up with over 3800 series of sequential numbers in one column. So if there's any formula that can find sequence gaps within one column that would be great.
    – Beast Wolf
    Commented May 19, 2020 at 9:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .