URGENT! Non Guru and need help asap!

[eravoz]

Hi there,

I have name and surname in one sheet(sheet 1), and name, surname, address and postcode in the other (sheet 2). Unfortunately I can't just sort, cut and paste because there is a high likelyhood of redundant data.

Now adding to that visual, Sheet 1 is actually 6 separate sheets (from 1st - 6th Grade for a school).

Can someone please give me some very plain English instructions on how to get the address and postcode from Sheet 2 to the other 6 sheets?

Please let me know if you need clarification, am far from an excel guru!!

Thanks

E.

 

[Domski]

Hi,

Do you have anything else as a reference that's common across all sheets or is forename and surname the only identifier? If the latter then is there a possibility of two individuals with the same forename/surname combination i.e. 2 John Smiths?

Dom

 

[eravoz]

Nothing else that is common and there are 3-4 instances of same name/surname combination.
(thanks for the quick response

 

[Domski]

You'll need to deal with those cases where there are duplicates manually then. Otherwise we should be able to use VLOOKUP to bring the addresses into your sheets.

The first step is to create a key to use as the basis for your lookup. I'm guessing your name and addresses are in columns starting from Column A across to column G or so.

Insert a column to the left and create a combination of surname and forename using a formula like =A2&B2 and copy it down your list.

On one of your sheets with just the names on you can then start using VLOOKUP so in the column next to your list of names enter the formula like =VLOOKUP($A2&$B2,Address_Data_Range,4,FALSE)

Where I've put Address_Data_Range change that to reference the table of addresses including the column where we've combined the names for the key.

The 4 means that where it finds a combination of name and surname in the table it will return the field that is in the 4th column across in that table. If you copy the formula across the row and change 4 to 5 and so on it will return the relative column.

Hope that makes sense, let me know how you get on.

Dom

 

[Halebop]

Hi Eravos

Are the instances of same names the same person or different people with the same name? If they are different people, you would need an additional unique identifier such as a student number.

 

[eravoz]

Thanks for that Dom. Still not working. Even changed from PROPER to UPPER case cause it's all in greek which means they have tones etc. on lower case but no luck. I'll keep trying

 

[Domski]

Not really sure about Greek I'm afraid but as far as VLOOKUP is concerned the text combination of surname and forename must be exactly the same so any additional spaces etc will cause it to not find a match. Hidden characters can sometimes also be a problem depending on what your data source is. Upper or lowere case doesn't normally make a any difference normally.

Dom