Delete unwanted row after merging duplicating cells

[tarone]

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

 

[Max]

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]

Email received from OP
-------
Date: Fri, 8 Dec 2006 17:22:37 -0800
From: "tarone" ..
To: "Max" <[email protected]>
Subject: Re: Delete unwanted row after merging duplicating cells
thanks for your help.
it saved lot of time