Unique Records

Happy · 07-26-2005, 01:05 PM

I have 3 columns

Name Tel Fax
Ang 93830220 99329207
Boo 93309374 98403803
Coo 99094890 98403803
Doo 84958948 38490384
Foo 74937489 09389099
Goo 43984093 09389099
Hoo 98983490 98439849
Koo 09439489 74837834

How can I make it like this? (ie remove subsequent same fax numbers)

Name Tel Fax
Ang 93830220 99329207
Boo 93309374 98403803
Doo 84958948 38490384
Foo 74937489 09389099
Hoo 98983490 98439849
Koo 09439489 74837834

Thks

Ray A · 07-26-2005, 02:05 PM

HTH
See data>filter>advanced filter
ray

"Happy" wrote:

> I have 3 columns
>
> Name Tel Fax
> Ang 93830220 99329207
> Boo 93309374 98403803
> Coo 99094890 98403803
> Doo 84958948 38490384
> Foo 74937489 09389099
> Goo 43984093 09389099
> Hoo 98983490 98439849
> Koo 09439489 74837834
>
> How can I make it like this? (ie remove subsequent same fax numbers)
>
> Name Tel Fax
> Ang 93830220 99329207
> Boo 93309374 98403803
> Doo 84958948 38490384
> Foo 74937489 09389099
> Hoo 98983490 98439849
> Koo 09439489 74837834
>
> Thks
>
>
>

Dave Peterson · 07-26-2005, 02:05 PM

Take a look at Debra Dalgleish's site:
http://www.contextures.com/xladvfilter01.html#FilterUR

Happy wrote:
>
> I have 3 columns
>
> Name Tel Fax
> Ang 93830220 99329207
> Boo 93309374 98403803
> Coo 99094890 98403803
> Doo 84958948 38490384
> Foo 74937489 09389099
> Goo 43984093 09389099
> Hoo 98983490 98439849
> Koo 09439489 74837834
>
> How can I make it like this? (ie remove subsequent same fax numbers)
>
> Name Tel Fax
> Ang 93830220 99329207
> Boo 93309374 98403803
> Doo 84958948 38490384
> Foo 74937489 09389099
> Hoo 98983490 98439849
> Koo 09439489 74837834
>
> Thks

--

Dave Peterson

Max · 07-26-2005, 02:05 PM

One way to try ..

Assume the table below is in Sheet1,
cols A to C, data from row2 down

> Name Tel Fax
> Ang 93830220 99329207
> Boo 93309374 98403803
> Coo 99094890 98403803
> Doo 84958948 38490384
> Foo 74937489 09389099
> Goo 43984093 09389099
> Hoo 98983490 98439849
> Koo 09439489 74837834

Using an empty col to the right, say col D

Put in D2:
=IF(C2="","",IF(COUNTIF($C$2:C2,C2)>1,"",ROW()))

Copy D2 down to say, D100 to cover the max expected data in the table
(can copy down ahead of data input)

In Sheet2
-----
With the same headers in A1:C1, i.e.:
> Name Tel Fax

Put in A2:
=IF(ISERROR(SMALL(Sheet1!$D:$D,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$D:$D,ROWS($A$1:A1)),Sheet1!$D:$D,0)))

Copy A2 across to C2, fill down to C100
(cover the same range size as in col D in Sheet1)

For the sample table in Sheet1, you'd get the desired results, viz.:

> Name Tel Fax
> Ang 93830220 99329207
> Boo 93309374 98403803
> Doo 84958948 38490384
> Foo 74937489 09389099
> Hoo 98983490 98439849
> Koo 09439489 74837834
(blank rows below)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Happy" <[email protected]> wrote in message
news:[email protected]...
> I have 3 columns
>
> Name Tel Fax
> Ang 93830220 99329207
> Boo 93309374 98403803
> Coo 99094890 98403803
> Doo 84958948 38490384
> Foo 74937489 09389099
> Goo 43984093 09389099
> Hoo 98983490 98439849
> Koo 09439489 74837834
>
> How can I make it like this? (ie remove subsequent same fax numbers)
>
> Name Tel Fax
> Ang 93830220 99329207
> Boo 93309374 98403803
> Doo 84958948 38490384
> Foo 74937489 09389099
> Hoo 98983490 98439849
> Koo 09439489 74837834
>
> Thks
>
>

Happy · 07-27-2005, 07:06 AM

I tried the Advanced Filter, still didn't work.
Every records is copied into the new location.
I guess this is becos every record is unique as Col A has different names.
So, how to specify that I want Col C to be unique??

Thks

"Happy" <[email protected]> wrote in message
news:[email protected]...
>I have 3 columns
>
> Name Tel Fax
> Ang 93830220 99329207
> Boo 93309374 98403803
> Coo 99094890 98403803
> Doo 84958948 38490384
> Foo 74937489 09389099
> Goo 43984093 09389099
> Hoo 98983490 98439849
> Koo 09439489 74837834
>
> How can I make it like this? (ie remove subsequent same fax numbers)
>
> Name Tel Fax
> Ang 93830220 99329207
> Boo 93309374 98403803
> Doo 84958948 38490384
> Foo 74937489 09389099
> Hoo 98983490 98439849
> Koo 09439489 74837834
>
> Thks
>

**olasa** · 07-27-2005, 07:40 AM

Simple
1. Use the Advanced filter on the Fax column Only >Copy to another location, to get a New list
2. Cut and Paste the Fax column so that it comes first in your Original list
3. Then use VLOOKUP to transfere Name and Tel from the Original list to the New list.
Done

Hope it helped
Ola Sandström

Domenic · 07-27-2005, 08:05 AM

Assuming that A1:C9 contains your data, including your headers/labels,
try the following...

D1: Leave blank

D2: =COUNTIF($C$2:C2,C2)=1

Data > Filter > Advanced Filter

List range: Sheet1!$A$1:$C$9

Criteria range: $D$1:$D$2

Click Ok

Hope this helps!

In article <[email protected]>,
"Happy" <[email protected]> wrote:

> I tried the Advanced Filter, still didn't work.
> Every records is copied into the new location.
> I guess this is becos every record is unique as Col A has different names.
> So, how to specify that I want Col C to be unique??
>
> Thks
>
>
>
> "Happy" <[email protected]> wrote in message
> news:[email protected]...
> >I have 3 columns
> >
> > Name Tel Fax
> > Ang 93830220 99329207
> > Boo 93309374 98403803
> > Coo 99094890 98403803
> > Doo 84958948 38490384
> > Foo 74937489 09389099
> > Goo 43984093 09389099
> > Hoo 98983490 98439849
> > Koo 09439489 74837834
> >
> > How can I make it like this? (ie remove subsequent same fax numbers)
> >
> > Name Tel Fax
> > Ang 93830220 99329207
> > Boo 93309374 98403803
> > Doo 84958948 38490384
> > Foo 74937489 09389099
> > Hoo 98983490 98439849
> > Koo 09439489 74837834
> >
> > Thks
> >

Dave Peterson · 07-27-2005, 08:05 AM

Try selecting column C first. Then the data|filter|advanced filter will be
pre-populated with the correct range--that single column.

Happy wrote:
>
> I tried the Advanced Filter, still didn't work.
> Every records is copied into the new location.
> I guess this is becos every record is unique as Col A has different names.
> So, how to specify that I want Col C to be unique??
>
> Thks
>
> "Happy" <[email protected]> wrote in message
> news:[email protected]...
> >I have 3 columns
> >
> > Name Tel Fax
> > Ang 93830220 99329207
> > Boo 93309374 98403803
> > Coo 99094890 98403803
> > Doo 84958948 38490384
> > Foo 74937489 09389099
> > Goo 43984093 09389099
> > Hoo 98983490 98439849
> > Koo 09439489 74837834
> >
> > How can I make it like this? (ie remove subsequent same fax numbers)
> >
> > Name Tel Fax
> > Ang 93830220 99329207
> > Boo 93309374 98403803
> > Doo 84958948 38490384
> > Foo 74937489 09389099
> > Hoo 98983490 98439849
> > Koo 09439489 74837834
> >
> > Thks
> >

--

Dave Peterson

Dave Peterson · 07-27-2005, 09:05 AM

Or just copy paste all the visible cells in that range. (and drop #2 and #3).

olasa wrote:
>
> Simple
> 1. Use the Advanced filter on the Fax column Only >Copy to another
> location, to get a New list
> 2. Cut and Paste the Fax column so that it comes first in your Original
> list
> 3. Then use VLOOKUP to transfere Name and Tel from the Original list to
> the New list.
> Done
>
> Hope it helped
> Ola Sandström
>
> --
> olasa
> ------------------------------------------------------------------------
> olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
> View this thread: http://www.excelforum.com/showthread...hreadid=390267

--

Dave Peterson

Happy · 07-27-2005, 10:05 AM

Hi Domenic

Fantastic. it's done beautfully. Thanks. :-)

"Domenic" <[email protected]> wrote in message
news:[email protected]...
> Assuming that A1:C9 contains your data, including your headers/labels,
> try the following...
>
> D1: Leave blank
>
> D2: =COUNTIF($C$2:C2,C2)=1
>
> Data > Filter > Advanced Filter
>
> List range: Sheet1!$A$1:$C$9
>
> Criteria range: $D$1:$D$2
>
> Click Ok
>
> Hope this helps!
>
> In article <[email protected]>,
> "Happy" <[email protected]> wrote:
>
>> I tried the Advanced Filter, still didn't work.
>> Every records is copied into the new location.
>> I guess this is becos every record is unique as Col A has different
>> names.
>> So, how to specify that I want Col C to be unique??
>>
>> Thks
>>
>>
>>
>> "Happy" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have 3 columns
>> >
>> > Name Tel Fax
>> > Ang 93830220 99329207
>> > Boo 93309374 98403803
>> > Coo 99094890 98403803
>> > Doo 84958948 38490384
>> > Foo 74937489 09389099
>> > Goo 43984093 09389099
>> > Hoo 98983490 98439849
>> > Koo 09439489 74837834
>> >
>> > How can I make it like this? (ie remove subsequent same fax numbers)
>> >
>> > Name Tel Fax
>> > Ang 93830220 99329207
>> > Boo 93309374 98403803
>> > Doo 84958948 38490384
>> > Foo 74937489 09389099
>> > Hoo 98983490 98439849
>> > Koo 09439489 74837834
>> >
>> > Thks
>> >