|
Unique Records
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 |
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 |
HTH
See datafilteradvanced 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 |
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 <atyahoo<dotcom ---- "Happy" wrote in message ... 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 |
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" wrote in message ... 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 |
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 , "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" wrote in message ... 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 |
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" wrote in message ... 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 |
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 |
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 |
Hi Domenic
Fantastic. it's done beautfully. Thanks. :-) "Domenic" wrote in message ... 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 , "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" wrote in message ... 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 |
| All times are GMT +1. The time now is 11:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com