Results 1 to 10 of 10

Thread: Help with matching by alpha and by number
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help with matching by alpha and by number

    Hello, Excel friends. I am trying to wrap my head around how to match by alpha and by number. I have below an example of what I'm going for.

    Example: Meeting group discussions with one group leader for every room number. People assigned to room one with the last name between a-ch will have Wayne Barron as their group leadder. People assigned to room two with last name between d-he will have Urich Elms as their group leader, etc.

    I will have about 50 to 75 people assigned with a room. I need a print out so they will know who their group leader will be. I am forced to work with a Excel 10 on client's computer. I'm a novice, so this is a challenge. I tried index match but I'm faced with the alphabetical system they insist on with various length a-ch, mc-rote, etc. I tried doing a helper cell with =length, but I'm a bit at a loss how to proceed.

    Thank you for the help. Much appreciated! I can of course split up the a-ch, ci-gi into separate cells and get rid of the dash. I just don't know where to go from there.

    Room Barron Elms Johnson Heller
    1 a-ch ci-gi go-ma mc-r
    2 a-c d-he hi-ma mc-rote
    3 a-cra de-he hi-lo lu-r



  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Help with matching by alpha and by number

    Welcome to the MrExcel board!

    Assumptions
    - You will have more 'Leader columns' to cover the entire alphabet in your real data
    - You will have ensured that the break-up of the alphabet across each row does not exclude any possible names. For example, with your sample, Don Davis or Jan Cryller would have no leader if assigned to room 3 (though my formula would give them a leader.

    Anyway see if this is any use for you. Post back with examples if you find some that this does not work for (I'm not over-confident that this will have covered all bases )

    Leader

    ABCDEFGHIJK
    1RoomBarronElmsJohnsonHeller First nameLast NameRoomLeader
    21a-chci-gigo-mamc-r SimonJones1Johnson
    32aa-cd-hehi-mamc-rote AnnAllen3Barron
    43a-crade-hehi-lolu-r KenHeavener2aElms
    5 DonDavis3Barron
    6 JenRainsford2aHeller

    Spreadsheet Formulas
    CellFormula
    K2=LOOKUP(I2,INDEX(B$2:E$4,MATCH(J2,A$2:A$4,0),0),B$1:E$1)


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by Peter_SSs; Aug 31st, 2019 at 12:26 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    Board Regular Sam_D_Ben's Avatar
    Join Date
    Oct 2012
    Location
    New Jersey, USA
    Posts
    371
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with matching by alpha and by number

    Peter - Is this MaGIC ?
    Sam_D_Ben

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Help with matching by alpha and by number

    Quote Originally Posted by Sam_D_Ben View Post
    Peter - Is this MaGIC ?
    No, in fact it is wrong as it puts Ahn Go room 1 with leader Elms when it should be Johnson.
    Next try is

    =LOOKUP(I2&"z",INDEX(B$2:E$4,MATCH(J2,A$2:A$4,0),0),B$1:E$1)
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    New Member
    Join Date
    Jan 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with matching by alpha and by number

    Wow, thank you so much! I am sitting here just studying your examples. I will test it tonight and report back.

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Help with matching by alpha and by number

    Quote Originally Posted by kate s View Post
    Wow, thank you so much! I am sitting here just studying your examples. I will test it tonight and report back.
    No problem. Let us know of any problems.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    New Member
    Join Date
    Jan 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with matching by alpha and by number

    What does &"z" mean? I am assuming it is a wild card. I can't get it figured out yet, but I'm trying to break down the components. Might be that this is just too advanced for my level. That's okay, I will keep working at it. Thank you!

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Help with matching by alpha and by number

    Quote Originally Posted by kate s View Post
    What does &"z" mean? I am assuming it is a wild card.
    No, it isn't a wild card. It is to avoid the situation identified in post 4 where a last name might happen to exactly equal the characters to the left of the "-" in one of your character ranges. In that example the last name is "Go" and for room 1 you have a character range "go-ma".
    Alphabetically "Go" comes before "go-ma" so this person would be assigned to the previous leader. By adding a "z" to their name for the lookup, it becomes "Goz" which alphabetically comes after "go-ma" so they get assigned to the correct leader.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  9. #9
    New Member
    Join Date
    Jan 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with matching by alpha and by number

    I've been working for a few hours after work each night trying to sort the spreadsheet I was making. Today I got it to work!!!

    Thank you SO much for your help. I kept studying your example and finally I fiddled enough. I just tested and tested and so far it is error free!

    I realize I love Excel! I love it more than watching television actually! Working through this has taught me a lot.

    Thank you again. I'll post my next question on another thread!

  10. #10
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Help with matching by alpha and by number

    Quote Originally Posted by kate s View Post
    I've been working for a few hours after work each night trying to sort the spreadsheet I was making. Today I got it to work!!!

    Thank you SO much for your help. I kept studying your example and finally I fiddled enough. I just tested and tested and so far it is error free!

    I realize I love Excel! I love it more than watching television actually! Working through this has taught me a lot.

    Thank you again. I'll post my next question on another thread!
    I've been away for a while but I'm glad you got it sorted.

    You're welcome. And if you want more help with your other question, you would need to give a bit more specific detail about just what you have and what you are trying to achieve, as well as what version of Excel you do have.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •