Calculating Missing Values in Number Series

Hi Guys,

I've got an excel problem which I hope someone here can help me resolve.

I have a list of ranges :-

90885 – 90887
90889 - 90890
90894 - 90898
90901 - 90910
90912 - 90921
90923 - 90932
90934 - 90943

The actual list is somewhat longer, but you get the idea.

What I need is way of getting excel to work out and display all the numbers that fall between the ranges.

Lets take the first row as an example. The begining number is 90885, whereas the final number is 90887. Therefore the missing value is 90886.
If we then take the 5th row (as it a better representation of what I would like) the start number is 90912 and the final number is 90921. So the missing values are 90913, 90914, 90915, 90916, 909017, 90918, 90919 and 90920.

I'd like advice on a solution that would look at the first row, work out the missing number and put this in the top cell of a different column labelled Result. Then look the next row of data in the example, find the missing values and append them to the Results column in the next cell below where the first result was stored, and so on. Hope I've explained this well enough to understand. An example of the results of the querying the first three rows of data is represented below (the start and final value in each row can be in different cells if that makes it easier):-

DATA
90885 – 90887
90889 - 90890
90894 - 90898


RESULTS
90886
90895
90896
90897


Any help that any of you expert can offer would be very much appreciated.

Thank you in anticipation of youR offers of help

Mike

I use formula to phrase out the beginning value and ending value for each row then I wrote a macro to get the list.

Please Login or Register  to view this content.

Can you attach the file you were using?

You need to parse out the beginning and ending sequence using formula, then you run the macro.

just an FYI. The macro looks the raw data starting from row #2 not row1. So you might want to shift the table by one row.

You change cells(i,2) to cells(i,3) to cells(i,1) to cells(i,2) Here is the code

Please Login or Register  to view this content.