formule met ALS (formula using IF)

Hi there,

I'm a beginmner, trying to use formula's for (almost) the first time.
In colum A I have about 220.000 barcodes
eg:
0000040151595
0000040151595
0000040151601
0000050936885
0000050936885
0000050936915
0000050936922
0000050953127
0000087189025
0000087189025
0000087189032
0000087189032
0000087189049
0000087189049
0000087189056
0000087189056
0010343602229
0010343602229
0010343602236
0010343602236
Many of those barcodes are unique, but many more have doubles.
By using data/sort, I sorted the barcodes, so that the double barcodes
are standing side by side in the colum.
Now I'm looking for a formula that picks out the stand alone barcodes,
But I don't know how to create a formula like that.

EG:
If cel A2 equals A1 OR If Cel A2 equals A3, the two cells that equal
should remain where they are.
If cel A2 does not equal A1 and does not equal A3 it should copy itself
another page.

Can anybody tell me what to do?

Thanks

How about using a different technique?

From Debra Dalgleish's site:
http://www.contextures.com/xladvfilter01.html#FilterUR

Bart wrote:
>
> Hi there,
>
> I'm a beginmner, trying to use formula's for (almost) the first time.
> In colum A I have about 220.000 barcodes
> eg:
> 0000040151595
> 0000040151595
> 0000040151601
> 0000050936885
> 0000050936885
> 0000050936915
> 0000050936922
> 0000050953127
> 0000087189025
> 0000087189025
> 0000087189032
> 0000087189032
> 0000087189049
> 0000087189049
> 0000087189056
> 0000087189056
> 0010343602229
> 0010343602229
> 0010343602236
> 0010343602236
> Many of those barcodes are unique, but many more have doubles.
> By using data/sort, I sorted the barcodes, so that the double barcodes
> are standing side by side in the colum.
> Now I'm looking for a formula that picks out the stand alone barcodes,
> But I don't know how to create a formula like that.
>
> EG:
> If cel A2 equals A1 OR If Cel A2 equals A3, the two cells that equal
> should remain where they are.
> If cel A2 does not equal A1 and does not equal A3 it should copy itself
> another page.
>
> Can anybody tell me what to do?
>
> Thanks

--

Dave Peterson

Hi Dave,

This is not what I mean.
By using this option (Filter unique records) I remove all the doubles
and thereby I increase the number of unique records.
I only want the records that are unique now in my list of records.

Any ideas?

Bart


Dave Peterson schreef:
> How about using a different technique?
>
> From Debra Dalgleish's site:
> http://www.contextures.com/xladvfilter01.html#FilterUR
>
> Bart wrote:
>
>>Hi there,
>>
>>I'm a beginmner, trying to use formula's for (almost) the first time.
>>In colum A I have about 220.000 barcodes
>>eg:
>>0000040151595
>>0000040151595
>>0000040151601
>>0000050936885
>>0000050936885
>>0000050936915
>>0000050936922
>>0000050953127
>>0000087189025
>>0000087189025
>>0000087189032
>>0000087189032
>>0000087189049
>>0000087189049
>>0000087189056
>>0000087189056
>>0010343602229
>>0010343602229
>>0010343602236
>>0010343602236
>>Many of those barcodes are unique, but many more have doubles.
>>By using data/sort, I sorted the barcodes, so that the double barcodes
>>are standing side by side in the colum.
>>Now I'm looking for a formula that picks out the stand alone barcodes,
>>But I don't know how to create a formula like that.
>>
>>EG:
>>If cel A2 equals A1 OR If Cel A2 equals A3, the two cells that equal
>>should remain where they are.
>>If cel A2 does not equal A1 and does not equal A3 it should copy itself
>>another page.
>>
>>Can anybody tell me what to do?
>>
>>Thanks
>
>

They show up exactly once?

Put this in column B:
=countif(a:a,a1)
and drag down

Then apply data|filter|autofilter to that column.

The formulas that return 1's are the singletons.

You can filter to show only those 1's and copy those visible cells elsewhere if
you want.

Bart wrote:
>
> Hi Dave,
>
> This is not what I mean.
> By using this option (Filter unique records) I remove all the doubles
> and thereby I increase the number of unique records.
> I only want the records that are unique now in my list of records.
>
> Any ideas?
>
> Bart
>
> Dave Peterson schreef:
> > How about using a different technique?
> >
> > From Debra Dalgleish's site:
> > http://www.contextures.com/xladvfilter01.html#FilterUR
> >
> > Bart wrote:
> >
> >>Hi there,
> >>
> >>I'm a beginmner, trying to use formula's for (almost) the first time.
> >>In colum A I have about 220.000 barcodes
> >>eg:
> >>0000040151595
> >>0000040151595
> >>0000040151601
> >>0000050936885
> >>0000050936885
> >>0000050936915
> >>0000050936922
> >>0000050953127
> >>0000087189025
> >>0000087189025
> >>0000087189032
> >>0000087189032
> >>0000087189049
> >>0000087189049
> >>0000087189056
> >>0000087189056
> >>0010343602229
> >>0010343602229
> >>0010343602236
> >>0010343602236
> >>Many of those barcodes are unique, but many more have doubles.
> >>By using data/sort, I sorted the barcodes, so that the double barcodes
> >>are standing side by side in the colum.
> >>Now I'm looking for a formula that picks out the stand alone barcodes,
> >>But I don't know how to create a formula like that.
> >>
> >>EG:
> >>If cel A2 equals A1 OR If Cel A2 equals A3, the two cells that equal
> >>should remain where they are.
> >>If cel A2 does not equal A1 and does not equal A3 it should copy itself
> >>another page.
> >>
> >>Can anybody tell me what to do?
> >>
> >>Thanks
> >
> >

--

Dave Peterson