Viewing 32 posts - 1 through 32 (of 32 total)
  • Today's second excel conundrum
  • LabMonkey
    Free Member

    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?

    TheSouthernYeti
    Free Member

    =vlookup

    njee20
    Free Member

    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.

    Fresh Goods Friday 696: The Middling Edition

    Fresh Goods Friday 696: The Middlin...
    Latest Singletrack Videos
    LabMonkey
    Free Member

    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?

    njee20
    Free Member

    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.

    LabMonkey
    Free Member

    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.

    njee20
    Free Member

    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.

    LabMonkey
    Free Member

    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?

    njee20
    Free Member

    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?

    portlyone
    Full Member

    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.

    LabMonkey
    Free Member

    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).

    reggiegasket
    Free Member

    (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

    rondo101
    Free Member

    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.

    portlyone
    Full Member

    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.

    LabMonkey
    Free Member

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

    rondo101
    Free Member

    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?

    muggomagic
    Full Member

    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.

    LabMonkey
    Free Member

    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?

    njee20
    Free Member

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

    LabMonkey
    Free Member

    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!

    LabMonkey
    Free Member

    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?

    rondo101
    Free Member

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

    LabMonkey
    Free Member

    Rondo101 – awesome – thanks!

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

    derekrides
    Free Member

    =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.

    njee20
    Free Member

    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.

    rondo101
    Free Member

    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.

    njee20
    Free Member

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

    LabMonkey
    Free Member

    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.

    njee20
    Free Member

    Serves him right for having a stupid name.

    reggiegasket
    Free Member

    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]

    richP
    Full Member

    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…)

    geoffj
    Full Member

    *SHOUTS* STONER COOEEE

Viewing 32 posts - 1 through 32 (of 32 total)

The topic ‘Today's second excel conundrum’ is closed to new replies.