Today's second excel conundrum

Column A – a long list of say 100 surnames
Column B – Black – for now.
Column C – a list of names (say 20, and maybe 15 are the same as those in column A
Column D – a list of numbers that are associated with the names in column C.

What I want to do is to assign the numbers in column D (which relate to the names in column C) to the cell in column B (that is next to the same name in column A. Does that make sense?

So essentially making two lists of names and numbers.

Any idea if this is first possible?

=vlookup

It’s easier if you swap columns A & B with C & D, in which case you just need to do:

=vlookup(C1,A:B,2,false)

And copy it down.

Is it really that easy? OMG! Runs off to try – thanks!

Edit: what happens if the names are not in the same row? Will it still work ok?

It’ll show #N/A, if you don’t want that change the formula to:

=iferror(vlookup(C1,A:B,2,false),””)

That’ll leave it blank. If you put any text in between the speech marks like “not in list A”, it’ll put that instead.

Njee50 – I just get a long list of #N/A’s?

Scratch that – it works on a small sample list I just made up – but not on my big list – I am probably just doing something silly.

Hmmmmmmm….

If I understand this right (it’s quite likely I don’t…), having swapped the columns you should now have:

Column A: Short list of names
Column B: Numbers
Column C: Long list of names
Column D: where you want the data

In which case, if you type the formula I gave you into cell D1 it should work.

Njee20 – that is exactly what I have.

Would it matter if the short list of names is copied and pasted from an internet page and so if you click on them they contain hyperlinks?

It appears that it does matter – as when I delete the original name and retype it myself it works.

So next and likely final question – can we remover the hyperlinks without having to delete and retype all the names?

How do you mean? If you direct the cursor to Cell A1, what does it show in the bar, a URL, or a name?

Can you highlight them all and select “remove hyperlink” to turn them into values?

Yes. Can you provide a sample? You can remove a string from the middle of a string.

You may have to find the length by searching for the position of certain characters in the URL, like encompassing quotes

MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.

When I hover over the name (in the cell) I get a ‘finger’ symbol and a hyperlink to the webpage appears but in the bar the name is shown.

Deleting the name (with hyperlink) and retyping the name again solves the problem – I just have 550 names to delete and retype (without any spelling mistakes of course).

(using the original structure of the data)

=INDEX(D$1:D$21,(MATCH(A1,C$1:C$21,0))

and a variant to remove the errors if the name can’t be found

=IF(ISNA((MATCH(A1,C$1:C$21,0),””,INDEX(D$1:D$21,(MATCH(A1,C$1:C$21,0))

put this formula in B1 and copy down

Create a macro.

Alt F11.

Create new module & paste:

Sub RemoveHyperlinks()

‘Remove all hyperlinks from the active sheet
ActiveSheet.Hyperlinks.Delete

End Sub

Run the macro.

You can use “Left” to remove the first fixed amount of characters to speed things up a bit.

So if it was “http://www.guardian.co.uk/sport” you’d use:

LEFT(A16,11)

to give you: guardian.co.uk/sport

Then remove “.co.uk/sport” with RIGHT.

A few things to try – this will take me a little while to work through – thanks everyone!

If I understand correctly, its not the http.. or .co.uk he wants to remove.

He’s got a list of names which currently look like:

rondo101
portlyone
LabMonkey
reggiegasket

but wants

rondo101
portlyone
LabMonkey
reggiegasket

The macro will do that in seconds.

Which version of Excel are you using LM?

Can you copy all the hyperlinked cells and then ,paste special, values?

I would rearrange the columns as said previously then is column D =if(isna(vlookup(a1,b:b,1,false)),vlookup(a1,c:c,1,false),vlookup(a1,b:b,1,false))

Then if the first column returns #N/A it will the lookup in the second column.

Rondo thats right – but the:

rondo101
portlyone
LabMonkey
reggiegasket

Is what I see in the cell already – the hyperlinks are hidden

But deleting the original and rewriting the same text makes it work.

Does anyone now how to the the macro thing on a Mac?

Why can’t you just right click and ‘remove hyperlink’ then? You should be left with the displayed text!

Njee20 – you are indeed correct – that didn’t work when I tried it first time, but did the second – maybe I highlighted the column, not the cells, or something?

Anyway, hyperlinks sorted now – thanks!

I guess I will be a little more cheeky, whilst I have so many excel GODS on here – If I have a name in a cell such as this:

SMITH John

Is it possible to remove the ‘John’ and leave the ‘SMITH’ with a formula of some sort?

“Text to columns” using space as the separator will give you “john” in one column and “smith” in the other.

Rondo101 – awesome – thanks!

Everyone – I have learnt lots today, thank you all for your time and patience – it is really appreciated!

=IF(ISNA((MATCH(A1,C$1:C$21,0),””,INDEX(D$1:D$21,(MATCH(A1,C$1:C$21,0))

That needs a 0 as follows I think you’ll find if you want to return a 0 instead of N/A.

=IF(ISNA((MATCH(A1,C$1:C$21,0),”0″,INDEX(D$1:D$21,(MATCH(A1,C$1:C$21,0))

to separate ‘John’ and ‘Smith’ simply ‘find’ Smith and ‘replace’ with nothing.

to separate ‘John’ and ‘Smith’ simply ‘find’ Smith and ‘replace’ with nothing.

Rondo’s method is better IMO, but if you wanted to do that I’d replace ‘ Smith’ otherwise you’ll have a space after John etc.

to separate ‘John’ and ‘Smith’ simply ‘find’ Smith and ‘replace’ with nothing

I assumed he’d not got a list of multiple “SMITH John” entries, but lots of different names & didn’t want to find & replace all the “John”, “Jack”, “Rita”, “Bob” and “Sue”s individually. As they’re all separated with a space, text to columns works best.

An excellent point! Couldn’t think of why I’d use text to columns rather than find & replace, but that’s exactly why!

The only problem when you have a name like:

DE WERT Chris

As you get three columns and it separates the surname.

But, I can manually edit these – not a big deal.

Serves him right for having a stupid name.

to do it with a formula, with the target name in cell A1

Surname
=LEFT(A1,FIND(” “,A1)-1)

First name
=RIGHT(A1,LEN(A1)-FIND(” “,A1))

[still has the DE WERT bug though]

To split the names according to the position of the last space, then you could use this

surnames
=LEFT(A1,SEARCH(“£”,SUBSTITUTE(A1,” “,”£”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,””)))))

first name
=RIGHT(A1,LEN(A1)-SEARCH(“£”,SUBSTITUTE(A1,” “,”£”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,””)))))

this should work in the DE WERT case (just need to make sure that the names don’t have any trailing spaces. oh and that the original names didn’t contain £ signs…)

*SHOUTS* STONER COOEEE