Separate data by sequential order and find missing count

[Kiran1811]

Hello,

I need help on converting below table (by VBA code or a Formula)

XX YY
1000 4401
1000 4402
1000 4403
1000 4404
2000 4401
2000 4403
2000 4404

Into:

XX Starting no Ending No Count Missing count
1000 4401 4404 3
2000 4401 4404 3 1

Thanks in advance for the help.

Regards,
Kiran.

[pjwhitfield]

The attached should do what you're after, please note the Starting and Ending values are using Array Formulas, which should be entered and then confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.