I merge cell in col b and now I want to delete duplicate records.


What is the formula if i want to delete unwanted rows e.g

Available file

COL A COLB
1 John Kim, Capcano Rd , 92821
1 Capcano Rd
1 92821
2 Peter, Mike
3 Mason Ko, 898934, california, Excellent, Yes
3 898934
3 california
3 Excellent
3 Yes
4 Tomorow
5 Welcome, Michael
5 Micael
6 Jonny, Based
6 based

Want to make it like this

COL A COLB
1 John Kim, Capcano Rd , 92821
2 Peter, Mike
3 Mason Ko, 898934, california, Excellent, Yes
4 Tomorow
5 Welcome, Michael
6 Jonny, Based

Help will be highly appreciated

Assuming source data in cols A and B from row1 down

Put in C1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",ROW()))

Put in D1:
=IF(ROW()>COUNT($C:$C),"",INDEX(A:A,SMALL($C:$C,ROW())))
Copy D1 to E1

Then select C1:E1, copy down to the last row of data in cols A and B.
Cols D and E will return the required results, all neatly bunched at the
top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I merge cell in col b and now I want to delete duplicate records.
>
>
> What is the formula if i want to delete unwanted rows e.g
>
> Available file
>
> COL A COLB
> 1 John Kim, Capcano Rd , 92821
> 1 Capcano Rd
> 1 92821
> 2 Peter, Mike
> 3 Mason Ko, 898934, california, Excellent, Yes
> 3 898934
> 3 california
> 3 Excellent
> 3 Yes
> 4 Tomorow
> 5 Welcome, Michael
> 5 Micael
> 6 Jonny, Based
> 6 based
>
> Want to make it like this
>
> COL A COLB
> 1 John Kim, Capcano Rd , 92821
> 2 Peter, Mike
> 3 Mason Ko, 898934, california, Excellent, Yes
> 4 Tomorow
> 5 Welcome, Michael
> 6 Jonny, Based
>
> Help will be highly appreciated
>

Email received from OP
-------
Date: Fri, 8 Dec 2006 17:22:37 -0800
From: "tarone" ..
To: "Max" <(E-Mail Removed)>
Subject: Re: Delete unwanted row after merging duplicating cells
thanks for your help.
it saved lot of time
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---