+ Reply to Thread
Results 1 to 14 of 14

Seemingly impossible request? - EXPERT LEVEL

  1. 12-12-2012, 05:28 AM #1
    WHATSTHISBUTTONDO
    WHATSTHISBUTTONDO is offline
    Registered User
    Join Date
    12-12-2012
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    7

    Seemingly impossible request? - EXPERT LEVEL

    SHEET 1 COLUMN 1 COLUMN 2
    ROW 1 A1
    ROW 2 A2 (WHO?)
    ROW ... C5 "NONE"

    SHEET 2 COLUMN 1 COLUMN 2
    ROW X A5, B7, A2 LARRY
    ROW Y F5 CURLY
    ROW Z N9, A2, H4 MOE



    I would like column 2 to show me in Sheet1 to show me "LARRY, MOE" from Sheet2, or at least "MULTIPLE". Somewhere in the equation, I suppose an =IF to throw in a "Not Selected Yet" or "None" would be nice as well. It is relatively simple request until you throw in the possibility that A2 can be picked by more than one person. I'm not a lazy or an incapable user, just one with a short amount of time. I have been fiddlin' around with multiple different functions for about 2 hours, and googlin' for about 1 hour, and searching this forum for about 30 minutes without too much luck.

    Any help with the dilemma above will be greatly appreciated.

    NOTE: Please see Book 2 for a better explanation and visualization. Also I am "starring" your helpful responses.
    Attached Files Attached Files
    Last edited by WHATSTHISBUTTONDO; 12-13-2012 at 04:38 AM. Reason: Clarification
    Reply With QuoteRegister To Reply
  2. 12-12-2012, 05:41 AM #2
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,426

    Re: Index-match | if | vlookup | ???

    It's not very clear what data you have and where - can you post a sample workbook (the FAQ describes how) ?

    Pete
    Reply With QuoteRegister To Reply
  3. 12-13-2012, 02:21 AM #3
    WHATSTHISBUTTONDO
    WHATSTHISBUTTONDO is offline
    Registered User
    Join Date
    12-12-2012
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Index-match | if | vlookup | ???

    Any response would be nice guys... even if you don't know. I thought I would have 150 answers by now.
    Reply With QuoteRegister To Reply
  4. 12-13-2012, 02:29 AM #4
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: Index-match | if | vlookup | ???

    try
    =iferror(INDEX($G$2:$G$9,MATCH("*"&A2&"*",$H$2:$H$9,0)),"no name")
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir
    Reply With QuoteRegister To Reply
  5. 12-13-2012, 04:28 AM #5
    WHATSTHISBUTTONDO
    WHATSTHISBUTTONDO is offline
    Registered User
    Join Date
    12-12-2012
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Index-match | if | vlookup | ???

    Thanks vlady! I'm not sure what exactly this is returning though. Regarding your formula and its application in Book2, it returns LARRY. If the desired response is supposed to return who has product "A2", MOE also has A2.
    Reply With QuoteRegister To Reply
  6. 12-13-2012, 05:47 AM #6
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,426

    Re: Index-match | if | vlookup | ???

    Your first request can be achieved by putting this in B2:

    =IF(COUNTIF(H$2:H$9,"*"&A2&"*")>1,"Multiple","none")

    and copying down.

    I'll have a think about getting the matching names instead of "Multiple".

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  7. 12-13-2012, 05:58 AM #7
    WHATSTHISBUTTONDO
    WHATSTHISBUTTONDO is offline
    Registered User
    Join Date
    12-12-2012
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Index-match | if | vlookup | ???

    Pete, thank you. This doesn't seem to be working. What is the "*"&A2&"*" ? Excel is referring to it as cell A2 instead of trying to find who has product A2.
    Reply With QuoteRegister To Reply
  8. 12-13-2012, 05:59 AM #8
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,426

    Re: Index-match | if | vlookup | ???

    Okay, this is not very pretty, but Excel doesn't have any shortcut ways (array formulae) for multiple concatenation of strings - put this in B2:

    =IF(COUNTIF(H$2:H$9,"*"&A2&"*")<=1,"none",IF(ISNUMBER(SEARCH(A2,H$2)),G$2&" ","")&IF(ISNUMBER(SEARCH(A2,H$3)),G$3&" ","")&IF(ISNUMBER(SEARCH(A2,H$4)),G$4&" ","")&IF(ISNUMBER(SEARCH(A2,H$5)),G$5&" ","")&IF(ISNUMBER(SEARCH(A2,H$6)),G$6&" ","")&IF(ISNUMBER(SEARCH(A2,H$7)),G$7&" ","")&IF(ISNUMBER(SEARCH(A2,H$8)),G$8&" ","")&IF(ISNUMBER(SEARCH(A2,H$9)),G$9&" ",""))

    then copy down.

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  9. 12-13-2012, 06:33 AM #9
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,426

    Re: Index-match | if | vlookup | ???

    Quote Originally Posted by WHATSTHISBUTTONDO View Post
    This doesn't seem to be working. What is the "*"&A2&"*" ? Excel is referring to it as cell A2 instead of trying to find who has product A2.
    As you posted just before I did, I've only just seen this. You can use wildcards (i.e. asterisks) with COUNTIF, so the formula is looking to see if A2 (which contains "A1") appears anywhere in the range H2:H9, and if it occurs more than once the formula returns "multiple". A2 changes to A3, A4 etc when the formula is copied down, i.e. it is checking for the contents of column A for each row.

    I've attached the file with the later formula in, so you can see it working.

    Hope this helps.

    Pete
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 12-13-2012, 07:17 PM #10
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: Seemingly impossible request? - EXPERT LEVEL

    I think this is a lookup,concatenate with wild card.

    it returns LARRY. If the desired response is supposed to return who has product "A2", MOE also has A2.
    so
    A1 returns Lary
    A2 returns Lary and Moe
    A3 returns Lary

    .....
    is that correct?
    Reply With QuoteRegister To Reply
  11. 12-13-2012, 08:30 PM #11
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,368

    Re: Seemingly impossible request? - EXPERT LEVEL

    Hi,

    Here is how I'd do this problem. See if it works for you.

    http://screencast.com/t/cWXqOAy1K
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  12. 12-14-2012, 02:06 AM #12
    WHATSTHISBUTTONDO
    WHATSTHISBUTTONDO is offline
    Registered User
    Join Date
    12-12-2012
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Index-match | if | vlookup | ???

    Regarding Pete_UK's posts:

    "Okay, this is not very pretty, but Excel doesn't have any shortcut ways (array formulae) for multiple concatenation of strings - put this in B2:

    =IF(COUNTIF(H$2:H$9,"*"&A2&"*")<=1,"none",IF(ISNUMBER(SEARCH(A2,H$2)),G$2&" ","")&IF(ISNUMBER(SEARCH(A2,H$3)),G$3&" ","")&IF(ISNUMBER(SEARCH(A2,H$4)),G$4&" ","")&IF(ISNUMBER(SEARCH(A2,H$5)),G$5&" ","")&IF(ISNUMBER(SEARCH(A2,H$6)),G$6&" ","")&IF(ISNUMBER(SEARCH(A2,H$7)),G$7&" ","")&IF(ISNUMBER(SEARCH(A2,H$8)),G$8&" ","")&IF(ISNUMBER(SEARCH(A2,H$9)),G$9&" ",""))

    then copy down.

    Hope this helps.

    Pete"
    -AND-

    "As you posted just before I did, I've only just seen this. You can use wildcards (i.e. asterisks) with COUNTIF, so the formula is looking to see if A2 (which contains "A1") appears anywhere in the range H2:H9, and if it occurs more than once the formula returns "multiple". A2 changes to A3, A4 etc when the formula is copied down, i.e. it is checking for the contents of column A for each row.

    I've attached the file with the later formula in, so you can see it working.

    Hope this helps.

    Pete"
    Wow, what a formula! Okay the first thing I notice in your file is that it is ONLY returning names in column B if there ARE multiples. If I type B2 next to someone's name, the return for the product B2 in cell B13 is still "none". Only if I type B2 next to a different persons name as well will the return display who all has product B2. I can't seem to find why this is occurring.

    In short, only multiples are displayed; not who has what INCLUDING the multiples.
    Last edited by WHATSTHISBUTTONDO; 12-14-2012 at 02:31 AM.
    Reply With QuoteRegister To Reply
  13. 12-14-2012, 02:23 AM #13
    WHATSTHISBUTTONDO
    WHATSTHISBUTTONDO is offline
    Registered User
    Join Date
    12-12-2012
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Seemingly impossible request? - EXPERT LEVEL

    Quote Originally Posted by MarvinP View Post
    Hi,

    Here is how I'd do this problem. See if it works for you.

    http://screencast.com/t/cWXqOAy1K
    Thanks! This a great way to get the desired results. This is also a great procedural illustration using Snagit. Thank for your time in creating this.

    However, my need for this is from an unalterable datasheet that that is formatted like the table on the right in Book2.xlsx. I can not change, convert, alter, etc the way the data is presented. Thank you for your help, but I think Pete_UK's solution is on the right track with some modifications.
    Reply With QuoteRegister To Reply
  14. 12-14-2012, 05:36 AM #14
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,426

    Re: Seemingly impossible request? - EXPERT LEVEL

    I thought you only wanted multiple names returned where they occur. To get single names as well you just need to change the formula to this:

    =IF(COUNTIF(H$2:H$9,"*"&A2&"*")=0,"none",IF(ISNUMBER(SEARCH(A2,H$2)),G$2&" ","")&IF(ISNUMBER(SEARCH(A2,H$3)),G$3&" ","")&IF(ISNUMBER(SEARCH(A2,H$4)),G$4&" ","")&IF(ISNUMBER(SEARCH(A2,H$5)),G$5&" ","")&IF(ISNUMBER(SEARCH(A2,H$6)),G$6&" ","")&IF(ISNUMBER(SEARCH(A2,H$7)),G$7&" ","")&IF(ISNUMBER(SEARCH(A2,H$8)),G$8&" ","")&IF(ISNUMBER(SEARCH(A2,H$9)),G$9&" ",""))

    then copy down.

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1