Truncating Number Series

[greenemm]

Hi all,

I'm a newbie with very little excel experience. I have a problem which
would very much apprieciate the assistance of you experts with

I have a series of numbers in a list. Each of the numbers in the lis
can be up to 12 digits long (but can also be shorter):

54114059
54114059
54114059
54114059
54114059
54114059
54114059
54114059
54114059
5411406
5411406
5411406
5411406
5411406
5411406
5411406
5411406
5411406
5411407
5411407
5411407
5411407
5411407
5411407
5411407
5411407
5411407
5411407
5411408
5411408
5411408
5411408
5411408
5411408
5411408
5411408
5411408
5411408

In this example (from the list above) the first set of numbers are nin
digit long starting 541140590. If I truncate these to the 8th digit the
would all be the same, right? So it would be fair to say that 'if' th
nineth digit of the first block covered the complete range of 0-9
truncating would not be a problem. However, as you can see the numbe
541140591 is missing from the range, so I do not want the rang
truncated

The next block of numbers are eight digits long starting from 54114060
Like the first set, by the considering the last digit (ie the eight
digit in this case) on each of the numbers doesn't cover the full rang
of 0-9 as 54114063 is missing. These should also not be truncated

However, the next (third) set are also eight digits long starting fro
54114070. Unlike the first and second set of numbers the last digit (i
the eigth digit) on each of the numbers covers the full range of 0-
(with none missing). This means that these can and should be truncate
to seven digits (namely 5411407). And so on for the next groups o
numbers startomg 54114080. Which can (following the above rules) b
truncated to 5411408

The question is, how do I get MS Excel to do this for me, either b
usings a macro, or a formula which I can paste into an adjacent cell
that will show any truncated numbers together with those that (followin
the rules above) should not truncated (so that I can drag it down)?

Thanks in advance of your anticipated assistance

Kind regard

 

[Ron Rosenfeld]

Hi all,

I'm a newbie with very little excel experience. I have a problem which I
would very much apprieciate the assistance of you experts with.

I have a series of numbers in a list. Each of the numbers in the list
can be up to 12 digits long (but can also be shorter):-

541140590
541140592
541140593
541140594
541140595
541140596
541140597
541140598
541140599
54114060
54114061
54114062
54114064
54114065
54114066
54114067
54114068
54114069
54114070
54114071
54114072
54114073
54114074
54114075
54114076
54114077
54114078
54114079
54114080
54114081
54114082
54114083
54114084
54114085
54114086
54114087
54114088
54114089

In this example (from the list above) the first set of numbers are nine
digit long starting 541140590. If I truncate these to the 8th digit they
would all be the same, right? So it would be fair to say that 'if' the
nineth digit of the first block covered the complete range of 0-9,
truncating would not be a problem. However, as you can see the number
541140591 is missing from the range, so I do not want the range
truncated.

The next block of numbers are eight digits long starting from 54114060.
Like the first set, by the considering the last digit (ie the eighth
digit in this case) on each of the numbers doesn't cover the full range
of 0-9 as 54114063 is missing. These should also not be truncated.

However, the next (third) set are also eight digits long starting from
54114070. Unlike the first and second set of numbers the last digit (ie
the eigth digit) on each of the numbers covers the full range of 0-9
(with none missing). This means that these can and should be truncated
to seven digits (namely 5411407). And so on for the next groups of
numbers startomg 54114080. Which can (following the above rules) be
truncated to 5411408.

The question is, how do I get MS Excel to do this for me, either by
usings a macro, or a formula which I can paste into an adjacent cell,
that will show any truncated numbers together with those that (following
the rules above) should not truncated (so that I can drag it down)?

Thanks in advance of your anticipated assistance.

Kind regards

If there are no duplicate numbers, then,
if the first value is in A1
and ColA is a Named Range representing all the numbers in column A
then

B1: =IF(SUMPRODUCT(--(INT(ColA/10)=INT(A1/10)))=10,INT(A1/10),A1)

and fill down as far as required.

 

[greenemm]

;1607486']On Sat, 17 Nov 2012 23:54:52 +0000, greenem

Hi all,

I'm a newbie with very little excel experience. I have a problem whic I
would very much apprieciate the assistance of you experts with.

I have a series of numbers in a list. Each of the numbers in the list
can be up to 12 digits long (but can also be shorter):-

541140590
541140592
541140593
541140594
541140595
541140596
541140597
541140598
541140599
54114060
54114061
54114062
54114064
54114065
54114066
54114067
54114068
54114069
54114070
54114071
54114072
54114073
54114074
54114075
54114076
54114077
54114078
54114079
54114080
54114081
54114082
54114083
54114084
54114085
54114086
54114087
54114088
54114089

In this example (from the list above) the first set of numbers ar nine
digit long starting 541140590. If I truncate these to the 8th digi they
would all be the same, right? So it would be fair to say that 'if the
nineth digit of the first block covered the complete range of 0-9,
truncating would not be a problem. However, as you can see the number
541140591 is missing from the range, so I do not want the range
truncated.

The next block of numbers are eight digits long starting fro 54114060.
Like the first set, by the considering the last digit (ie the eighth
digit in this case) on each of the numbers doesn't cover the ful range
of 0-9 as 54114063 is missing. These should also not be truncated.

However, the next (third) set are also eight digits long starting from
54114070. Unlike the first and second set of numbers the last digi (ie
the eigth digit) on each of the numbers covers the full range of 0-9
(with none missing). This means that these can and should be truncated
to seven digits (namely 5411407). And so on for the next groups of
numbers startomg 54114080. Which can (following the above rules) be
truncated to 5411408.

The question is, how do I get MS Excel to do this for me, either by
usings a macro, or a formula which I can paste into an adjacent cell,
that will show any truncated numbers together with those tha (following
the rules above) should not truncated (so that I can drag it down)?

Thanks in advance of your anticipated assistance.

Kind regards-

If there are no duplicate numbers, then,
if the first value is in A1
and ColA is a Named Range representing all the numbers in column A
then

B1: =IF(SUMPRODUCT(--(INT(ColA/10)=INT(A1/10)))=10,INT(A1/10),A1)

and fill down as far as required.

Hi Ron,

Firstly, thank you for taking the time to look into helping me solve m
'problem'.

Your solution has saved me many hours of tedious and monotonous manua
checking. I am very grateful. Thank you so much.

Kind regards

Michae

 

[Ron Rosenfeld]

Hi Ron,

Firstly, thank you for taking the time to look into helping me solve my
'problem'.

Your solution has saved me many hours of tedious and monotonous manual
checking. I am very grateful. Thank you so much.

Kind regards

Michael

Glad to help. Thanks for the feedback. Note that the solution depends critically on the absence of duplicate numbers. If there might be duplicates, a different solution will be required.