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
>Solution :
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.