EXCEL FUNCTION

  carmella 19:16 18 Mar 03
Locked

here's my problem, well 2 really:

1. I have an excel spreadsheet with one FULL NAME column. I need a function to separate TITLE, FIRST NAME and SURNAME. Is that possible?
2. Once I have my 3 new columns for Title, Name and Surname, I then need to match these records with another spreadsheet and keep ONLY the duplicates.

I hope this is clear. I never use Excel and hope someone has done this before.
Many thanks

  VoG™ 19:21 18 Mar 03

1. Select all the names, Data/Text to columns.

Tick Delimited, then Next.

Tick Space as the delimiter and Finish.

  VoG™ 19:32 18 Mar 03

2. Having thought about it, I think that you will need a macro to do this.

Are both of the worksheets in the same file? If not what are the names of the two files.

What are the names of the worksheets?

What is the range of the data on each sheet - in other words, which columns are the three names in and how many rows are there in each sheet?

  VoG™ 22:47 18 Mar 03

1. Make sure that you have three blank columns to the right of the one containing FULL NAME.

2. Still awaiting instructions - I will write you a macro to do this but need to know where to start.

  carmella 10:06 19 Mar 03

thanks.
OK, problem number one is now solved.

So I now have 2 docs (doc1 and doc2) with title, surname and firstname columns each. Doc1 has 400 records, to be matched with doc2, with 2243 records.
Doc2 has an ID column for each record, which I must keep.
I also have postcode in each doc, so I could try to match names that have same surname AND postcode.

What I need to end up with is the 400 records of doc1 and the IDs of doc2.

I hope this is clear and many thanks again.
C

  cherria 10:40 19 Mar 03

in both doc1 and doc2 insert a new column at column A.

Assuming that you have column headers in row 1 and that surname is in column C (after insert of new column) and postcode is in column F and the ID is in column E.

in A1 put a header 'key'
in A2 for both sheets type =TRIM(C2) & TRIM(F2)

This creates your key of surname+postcode

in doc1 insert another column after column A and give it the header 'ID'

in cell B2 of doc1 type
=VLOOKUP(A2,doc2!$A$2:$E$2244,5,FALSE)

This looks up the key in doc1 and tries to find the same value in the key column of doc2 then returns the value from the 5th column (ID) of the matching row.

copy this down the 400 records of doc1 and wherever there is a match, you should have the ID, wherever there is not a match, you will get #N/A

  carmella 11:50 19 Mar 03

thanks for your reply.
I followed your instructions, but something is not working.

First, I do get the key of surname+postoce.
Then I type the VLOOKUP function as instructed in doc one, but get this window: "update values in doc2:" and prompting me to find the file. I'm not quite sure if it's asking me to find doc 2 or 1, but have tried both and get value N/A in all 400 cells, which I know is wrong.

Should I keep doc 2 closed?

thanks

C

  carmella 11:58 19 Mar 03

CherrieA, sorry, I had forgotten to copy the TRIM function to other cells.
I now copied it and it worked perfectly.
thanks so much, you just saved me a whole day's work!
C

This thread is now locked and can not be replied to.

What is Amazon Go and will it come to the UK? The store without checkouts or queues

1995-2015: How technology has changed the world in 20 years

Why ecommerce hasn't taken off on social media

New MacBook Pro 2016 review | MacBook Pro with Touch Bar review: Apple's expensive and powerful…