Unique Records

H

Happy

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
 
M

Max

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
Click to expand...

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
Click to expand...

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
Click to expand...
(blank rows below)
 
H

Happy

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
 
D

Domenic

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!
 
D

Dave Peterson

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

olasa

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

Hope it helped
Ola Sandströ
 
D

Dave Peterson

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