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

inarbeth

Well-known Member
Joined
Apr 8, 2002
Messages
913
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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
 
Upvote 0
Unfortunately filtering only lists unique cells. I need to extract names from within the cells.
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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
Back
Top