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.
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

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

ADVERTISEMENT

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

ADVERTISEMENT

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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

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