Double Items In List

[Masta John Newsgroups]

Hi, I am looking for some help with this;

I have a worksheet with up to 2000 lines, the first column contains
numbers, the entries in this column are either 7 or 8 digit numbers, by
formatting it with 0#.###.### I force it to be a 8 digit number in case
a 7 digit number has been entered.
The worksheet is sorted on this column, I want the first 3 digits of
each number being copied into another worksheet without copying double
items.
ie.

original column the copied column should contain
01.123.456 01.1
01.155.897 02.5
01.199.852 02.6
02.527.255 42.1
02.531.459 60.1
02.625.365
02.631.889
02.652.987
02.689.211
42.111.111
42.125.998
42.159.333
60.180.721
60.190.721

I can“t find the answer myself, so I need a little help

[Domenic]

Assuming that Column A of Sheet1 contains your data, on Sheet2...

1) Leave A1 empty

2) Enter the following formula in A2, and copy down until you get #N/A:

=INDEX(LEFT(Sheet1!A1:$A$14,4),MATCH(FALSE,ISNUMBER(MATCH(LEFT(Sheet1!A1:
$A$14,4),$A$1:A1,0)),0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article <[email protected]>,
Masta John Newsgroups <[email protected]> wrote:

> Hi, I am looking for some help with this;
>
> I have a worksheet with up to 2000 lines, the first column contains
> numbers, the entries in this column are either 7 or 8 digit numbers, by
> formatting it with 0#.###.### I force it to be a 8 digit number in case
> a 7 digit number has been entered.
> The worksheet is sorted on this column, I want the first 3 digits of
> each number being copied into another worksheet without copying double
> items.
> ie.
>
> original column the copied column should contain
> 01.123.456 01.1
> 01.155.897 02.5
> 01.199.852 02.6
> 02.527.255 42.1
> 02.531.459 60.1
> 02.625.365
> 02.631.889
> 02.652.987
> 02.689.211
> 42.111.111
> 42.125.998
> 42.159.333
> 60.180.721
> 60.190.721
>
> I canĀ“t find the answer myself, so I need a little help

[Masta John Newsgroups]

Thanks a lot Dominic,

After changing the formula into the Dutch language
{=INDEX(LINKS(Blad1!A1:$A$14;3);VERGELIJKEN(ONWAAR;ISGETAL(VERGELIJKEN(LINKS(Blad1!A1:$A$14;3);$A$2:A2;0));0))}
it works very well, because of the {} I know that it is a matrixformula,
I have tried to find out how it works but I donĀ“t really get it.
The most important is that it works, if you have chance please explain
briefly how it works.

Thanks again!!
John




Domenic wrote:

> Assuming that Column A of Sheet1 contains your data, on Sheet2...
>
> 1) Leave A1 empty
>
> 2) Enter the following formula in A2, and copy down until you get #N/A:
>
> =INDEX(LEFT(Sheet1!A1:$A$14,4),MATCH(FALSE,ISNUMBER(MATCH(LEFT(Sheet1!A1:
> $A$14,4),$A$1:A1,0)),0))
>
> ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
>
> Hope this helps!
>
> In article <[email protected]>,
> Masta John Newsgroups <[email protected]> wrote:
>
>
>>Hi, I am looking for some help with this;
>>
>>I have a worksheet with up to 2000 lines, the first column contains
>>numbers, the entries in this column are either 7 or 8 digit numbers, by
>>formatting it with 0#.###.### I force it to be a 8 digit number in case
>>a 7 digit number has been entered.
>>The worksheet is sorted on this column, I want the first 3 digits of
>>each number being copied into another worksheet without copying double
>>items.
>>ie.
>>
>>original column the copied column should contain
>>01.123.456 01.1
>>01.155.897 02.5
>>01.199.852 02.6
>>02.527.255 42.1
>>02.531.459 60.1
>>02.625.365
>>02.631.889
>>02.652.987
>>02.689.211
>>42.111.111
>>42.125.998
>>42.159.333
>>60.180.721
>>60.190.721
>>
>>I canĀ“t find the answer myself, so I need a little help

[Domenic]

If, for example, we have the following data and formula...

01.123.456
01.155.897
01.199.852
02.527.255
02.531.459
02.625.365

=INDEX(LEFT(Sheet1!A1:$A$6,4),MATCH(FALSE,ISNUMBER(MATCH(LEFT(Sheet1!A1:$
A$6,4),$A$1:A1,0)),0))

....we can break it down as follows...

MATCH(LEFT(Sheet1!A1:$A$6,4),$A$1:A1,0) returns the following array of
values...

{#N/A;#N/A;#N/A;#N/A;#N/A,#N/A}

ISNUMBER(MATCH(LEFT(Sheet1!A1:$A$6,4),$A$1:A1,0)) returns the following
array of values...

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

Note that MATCH has an array of lookup values, that is, the first four
characters of each cell from A1 to A6. Each one of these is matched
against the lookup array A1:A1, which at the moment contains no value.

MATCH(FALSE,ISNUMBER(MATCH(LEFT(Sheet1!A1:$A$6,4),$A$1:A1,0)),0) returns
1, since the lookup value FALSE occurs first in the array of values
returned by ISNUMBER(MATCH(...). The number 1, in turn, is used as an
argument for the INDEX function.

When the formula is copied down to the next cell below, we get the
following formula...

=INDEX(LEFT(Sheet1!A2:$A$6,4),MATCH(FALSE,ISNUMBER(MATCH(LEFT(Sheet1!A2:$
A$6,4),$A$1:A2,0)),0))

Notice that the range for INDEX, and the range for the array of lookup
values for MATCH has changed from A1:A6 to A2:A6. Also, the range for
the lookup array has changed from A1:A1 to A1:A2, where A1 is blank and
A2 now contains the value '01.1' returned from the formula in the cell
above. With these changes, we have the following...

MATCH(LEFT(Sheet1!A2:$A$6,4),$A$1:A2,0) returns the following array of
values...

{2;2,#N/A;#N/A;#N/A}

ISNUMBER(MATCH(LEFT(Sheet1!A2:$A$6,4),$A$1:A2,0)) returns the following
array of values...

{TRUE;TRUE;FALSE;FALSE;FALSE}

MATCH(FALSE,ISNUMBER(MATCH(LEFT(Sheet1!A2:$A$6,4),$A$1:A2,0)),0) returns
3, which in turn is used as an argument for the INDEX function.

As the formula is copied down to each cell below, the process is
repeated.

Hope this helps!

In article <[email protected]>,
Masta John Newsgroups <[email protected]> wrote:

> Thanks a lot Dominic,
>
> After changing the formula into the Dutch language
> {=INDEX(LINKS(Blad1!A1:$A$14;3);VERGELIJKEN(ONWAAR;ISGETAL(VERGELIJKEN(LINKS(B
> lad1!A1:$A$14;3);$A$2:A2;0));0))}
> it works very well, because of the {} I know that it is a matrixformula,
> I have tried to find out how it works but I donĀ“t really get it.
> The most important is that it works, if you have chance please explain
> briefly how it works.
>
> Thanks again!!
> John

[Masta John Newsgroups]

Domenic,

thanks for the time you took for this explanation, its clear now.

Regards John


Domenic wrote:
> If, for example, we have the following data and formula...
>
> 01.123.456
> 01.155.897
> 01.199.852
> 02.527.255
> 02.531.459
> 02.625.365
>
> =INDEX(LEFT(Sheet1!A1:$A$6,4),MATCH(FALSE,ISNUMBER(MATCH(LEFT(Sheet1!A1:$
> A$6,4),$A$1:A1,0)),0))
>
> ...we can break it down as follows...
>
> MATCH(LEFT(Sheet1!A1:$A$6,4),$A$1:A1,0) returns the following array of
> values...
>
> {#N/A;#N/A;#N/A;#N/A;#N/A,#N/A}
>
> ISNUMBER(MATCH(LEFT(Sheet1!A1:$A$6,4),$A$1:A1,0)) returns the following
> array of values...
>
> {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
>
> Note that MATCH has an array of lookup values, that is, the first four
> characters of each cell from A1 to A6. Each one of these is matched
> against the lookup array A1:A1, which at the moment contains no value.
>
> MATCH(FALSE,ISNUMBER(MATCH(LEFT(Sheet1!A1:$A$6,4),$A$1:A1,0)),0) returns
> 1, since the lookup value FALSE occurs first in the array of values
> returned by ISNUMBER(MATCH(...). The number 1, in turn, is used as an
> argument for the INDEX function.
>
> When the formula is copied down to the next cell below, we get the
> following formula...
>
> =INDEX(LEFT(Sheet1!A2:$A$6,4),MATCH(FALSE,ISNUMBER(MATCH(LEFT(Sheet1!A2:$
> A$6,4),$A$1:A2,0)),0))
>
> Notice that the range for INDEX, and the range for the array of lookup
> values for MATCH has changed from A1:A6 to A2:A6. Also, the range for
> the lookup array has changed from A1:A1 to A1:A2, where A1 is blank and
> A2 now contains the value '01.1' returned from the formula in the cell
> above. With these changes, we have the following...
>
> MATCH(LEFT(Sheet1!A2:$A$6,4),$A$1:A2,0) returns the following array of
> values...
>
> {2;2,#N/A;#N/A;#N/A}
>
> ISNUMBER(MATCH(LEFT(Sheet1!A2:$A$6,4),$A$1:A2,0)) returns the following
> array of values...
>
> {TRUE;TRUE;FALSE;FALSE;FALSE}
>
> MATCH(FALSE,ISNUMBER(MATCH(LEFT(Sheet1!A2:$A$6,4),$A$1:A2,0)),0) returns
> 3, which in turn is used as an argument for the INDEX function.
>
> As the formula is copied down to each cell below, the process is
> repeated.
>
> Hope this helps!
>
> In article <[email protected]>,
> Masta John Newsgroups <[email protected]> wrote:
>
>
>>Thanks a lot Dominic,
>>
>>After changing the formula into the Dutch language
>>{=INDEX(LINKS(Blad1!A1:$A$14;3);VERGELIJKEN(ONWAAR;ISGETAL(VERGELIJKEN(LINKS(B
>>lad1!A1:$A$14;3);$A$2:A2;0));0))}
>>it works very well, because of the {} I know that it is a matrixformula,
>>I have tried to find out how it works but I donĀ“t really get it.
>>The most important is that it works, if you have chance please explain
>>briefly how it works.
>>
>>Thanks again!!
>>John