Formula to determine range between two columns of numbers (by row)

[mike.greene]

Dear Excel Forum users/Experts,

I have a problem, which I have spent many hours trying to resolve. Not being particularly MS Excel 'savvy', I'm getting nowhere fast! I'm therefore attempting to swallow my pride and ask for help!

I have two columns (A and B) each contain thousands of rows of figures. Sample extract shown below:-

Column A Column B
49270000 49289999
49350000 49409999
49450000 49499999
49690000 49809999
49840000 49859999
50260000 50299999
50350000 50649999

How do I get MS Excel to work out the variation between the numbers on each row (placing the results in another column), but to the minumum number of digits to satisfy all numbers within the 'range'?

Using the first row of figures as an example:

Column A Column B Column C
49270000 49289999 4927, 4928

The two numbers in Columns C represent the complete range of numbers between the two. For the second row the results would be:-

Column A Column B Column C
49350000 49409999 4935,4936,4937,4938,4939,4940

Hope this makes sense.

Instead of the results showing as csv in a single cell on the same row, I would prefer them to appear in a list in Column C (one under the other), with the results of the next rows calculation being appended to the list (and so on for all the entries in Columns A & B).

I've attached a sample of the spreadsheet to allow you to see what I'm up against.

Thanks

Michael

[mikerickson]

Is this a restatement of your problem.
"Given two numbers, a and b, list all the integers between a/10000 and b/10000, inclusive, in a comma delimited string."

Do you want a formula solution or is VBA acceptable?

[FDibbins]

it looks like you are just interested in using the 1st 4 numbers?

maybe in your sample workbook, you could show what your expected outcome would be?

[mikerickson]

You might find this UDF useful in formulas like
=DelimitedIntegers(INT(A2/10000),INT(B2/10000))

Please Login or Register  to view this content.

[mike.greene]

Hi Mike,

Thanks you for taking the time to respond

Unfortunately the restatement is not wholly accurate. Not all the numbers in column A end in 0000. Column A values can end with any number of zeros ranging from anything from six to two zeros.

To better clarify this I have added there expected results to the rows number 2 to 6 into Column C. I have coloured the range start and end of each row (ie Column A and B) a particular colour and also displayed the results for that particular row in column C (which I have kept the same colour to keep the tie in easy to follow).

As you can see, the results of each new row are appended to the results of the previous rows results.

Please let me know I have succeeding in clarifying the requirement (or instead made it more confusing).

A VBA solution would be great, but really, either is fine (beggars can't be choosers), what ever is most convenient for you.


Regards


Mike

[mike.greene]

Hi FDibbins,

Again, thank you for taking time to respond

I have responded to Mikerickson's post to which I attached the same spreadsheet with examples of output.

Rather than repost the same spreadsheet again here (and using up valuable server space unnecessarily), could I ask you to look at this and see whether this sheds any more light on the subject for you (or anyone else who has offered to help).

Thanks and regards

Mike

[mikerickson]

Use the formula
=DelimitedIntegers(A1/10^(9-MAX(SIGN(MOD(A1/10^{0,1,2,3,4,5,6,7,8,9,10},1))*{10,9,8,7,6,5,4,3,2,1,0})), INT(B1/10^(9-MAX(SIGN(MOD(A1/10^{0,1,2,3,4,5,6,7,8,9,10},1))*{10,9,8,7,6,5,4,3,2,1,0}))))

[mike.greene]

Use the formula
=DelimitedIntegers(A1/10^(9-MAX(SIGN(MOD(A1/10^{0,1,2,3,4,5,6,7,8,9,10},1))*{10,9,8,7,6,5,4,3,2,1,0})), INT(B1/10^(9-MAX(SIGN(MOD(A1/10^{0,1,2,3,4,5,6,7,8,9,10},1))*{10,9,8,7,6,5,4,3,2,1,0}))))

Hi Mike. Thank you for the formula. I'm having a little trouble knowing how and where to use it (forgive my ignorance).

I've pasted the formula into an adjacent cell (column C), but I just get #NAME? showing in the cell. What am I doing wrong?

Regards

Michael

[mikerickson]

It has to be used with the UDF that I posted. The UDF should go in a normal code module.

[mike.greene]

Hi Mike,

I have tried your solution but, unfortunately, the results are not as expected. After I added the UDF to a code module and pasted the other formula into column C for row 2 the result was displayed as 50112. The actual results I was expect were 5011200 and 5011201, which is represents all the values between 50112000000 and 501120119999 (to the minimum number of digits to allow distinction between the two).

Examples are shown in the attached spread sheet which is colour coded.

Regards,

Michael