Extract unique names from within cells and paste in to a new

inarbeth

Well-known Member
Joined
Apr 8, 2002
Messages
912
I have a database with parties to various legal documents listed within a large range called Parties. The range is presently in cells F3:F750 but may be expanded. Typically a cell contains information such as: ZZ Top (1) Acme Engineering 1987 Limited (2) John Lee Hooker(3).
(I made those names up.)
What I want to do is to run a macro to extract all names of more than two characters length and paste them in a column on Sheet 4 but once only. Thus if "Hooker" or "1987" appears several times they will appear once only on Sheet 4.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Haluk

Rules Violation
Joined
Oct 26, 2002
Messages
1,075
i don't know if this helps but, you may try to do this;

* select the range of data
* Data | Filter | Advanced Filter >> check the "Unique records only"
* select the "copy to another location" option
* select the destination range
* press "OK" button to close the dialog window.
This message was edited by Raider on 2002-11-01 07:31
 

inarbeth

Well-known Member
Joined
Apr 8, 2002
Messages
912
Unfortunately filtering only lists unique cells. I need to extract names from within the cells.
 

inarbeth

Well-known Member
Joined
Apr 8, 2002
Messages
912
On 2002-11-01 07:50, Andrew Poulsom wrote:
Can you post a sample of the various types of data in your list of parties?

I have done so in my question. I have over 700 rows with usually two or three names within each cell in the format mentioned, i.e. with a mixture of names, initials, brackets and numbers.
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
inarbeth

recommend you attack it like this

1. in columns across the sheet where the data is held run extractions on the string to gather the various names - say cater for 6 so B would get the 1st, C would get the 2nd etc (this is pretty straightforward)

2. Copy and Paste Cols B-G into one column and then sort according to name - then run a loop macro through this list removing replicas.

So first thing would be B = IF(FIND("(1)",A1)>2,LEFT(A1,FIND("(1)",A1)-1),"")

etc...
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
On 2002-11-01 09:03, inarbeth wrote:
On 2002-11-01 07:50, Andrew Poulsom wrote:
Can you post a sample of the various types of data in your list of parties?

I have done so in my question. I have over 700 rows with usually two or three names within each cell in the format mentioned, i.e. with a mixture of names, initials, brackets and numbers.

It would be quicker for you to post a sample than for me to make one up. Then I could write some code and test it.
 

inarbeth

Well-known Member
Joined
Apr 8, 2002
Messages
912
Andrew, I can't seem to get Colo's HTML maker to work so here is some raw data:

Parties
Baron Ashburton (1) E A H Mosenthal, H R Mosenthal (2)
H E Hutton, W T Brown (1) Baron Ashburton (2)
E M Bishop (1) A Bishop, L Bishop (2)
W Douglas (1) H E Hutton, W T Brown (2)
W C Norton
XYZ Limited (1) Santa Barbara Hotels Ltd. (2) Shadwood Ltd. (3) 190 Queens Gate PLC (4)
H J Cotton, H Lainson, H W Pemberton (1) H Lainson (2)
H J Cotton, H Lainson, H W Pemberton (1) H J Cotton (2)
H J Cotton, H Lainson, H W Pemberton (1) A Hodgson, H W Pemberton (2)
H J Cotton, H Lainson, H W Pemberton (1) F J Cooper (2)
H W Pemberton (1) G A Spottiswoode (2)
G A Spottiswoode, E B Alexander (1) G A Spottiswoode (2) E B Alexander, G A Spottiswoode, St Vincent Alexander Hamwick (3) W Kirby (4)
M H Laskey, E Runtz (1) W H Jones, E James Jones (2)
W Kirby (1) Trustees of New York Permanent Benefit Building Society (2)
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
For:

H J Cotton, H Lainson, H W Pemberton

do you want just that or

H J Cotton
H Lainson
H W Pemberton
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Sent you an email with attachment

Hopefully Andrew will come up with another of his specials and will do it all via VB (that would be good!!)

Anyway...if not, check your mail.

Cheers
Luke
 

Forum statistics

Threads
1,171,047
Messages
5,873,463
Members
432,980
Latest member
KMorrison12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top