extract ranges from column values

Question

I've column of values 1-10 missing 4 and 7 can I extract 1-3,5-6,7-10. Currently I'm using this formula =IF(A3=A2+1,C2,C2+1) which gives me helper column sort of help but my list is long If I could extract ranges that would be helpful. There are no duplicates

Answer 1

I'm not sure if I understand exactly what you mean, but this is what I have done until now: I have copied the same columns A and B, and I have added following columns:

• Column C : =COUNTIF(B$2:B$16,B2)
• Column D : =IF(AND(C2=C3,C3<>C4),"End",IF(AND(C2<>C3,C3=C4),"Begin"))

The result looks as follows:

As you can see:

• The number 1 from column B ends at row 6, and D6 indeed indicates "End".
• The number 2 from B starts at row 7 (D7="Begin") and ends at row 8 (D8="End").
• The numbers 3 and 4 are not correctly handled but:
• As far as 5 is concerned: it starts at row 11 (D11="Begin") and ends at row 15 (D15="End").

There still is some finetuning to do but I guess you see how the ranges start being unfold.