Find range in excel between different rows

Question

I'm looking for a nice formula to create some "ranges" for me. The way I'm doing this today is like 30 IFs, and I think there's a much better way to do it.

Column A is data I've produced. Column B is where I want the formula to be.

Check if row below is row above + 1.

I hope you're understanding what I want. If not, I can produce some more examples.

What I want is something like following result:

Column A            Column B
1                   1-3
2                   FALSE
3                   FALSE
6                   6-10
7                   FALSE
8                   FALSE
9                   FALSE
10                  FALSE
13                  13
15                  15

Answer 1

You can do this with an array formula.

=IF(OR(B6=1,C6>1),IF(C7<>1,B6,B6&"-"&(INDEX(B7:$B$16,MATCH(TRUE,C8:$C$16<>1,0)))),"FALSE")

EDIT

To do this without helper column you could use this array formula:

=IF(OR(B6=1,(B6-B5)>1),IF((B7-B6)<>1,B6,B6&"-"&(INDEX(B6:$B$15,MATCH(TRUE,(B7:B15-B6:B14)<>1,0)))),"FALSE")

END EDIT

Use Ctrl+Shift+Enter instead of Enter to apply the array formula.