+ Reply to Thread
Results 1 to 4 of 4

Horizontal Lookup With Multiple Instances, to pull string in adjacent field randomly

  1. #1
    Registered User
    Join Date
    12-09-2015
    Location
    SoCal, CA
    MS-Off Ver
    2010
    Posts
    2

    Horizontal Lookup With Multiple Instances, to pull string in adjacent field randomly

    Let me explain what I'm working with:

    I have a sheet laid out like so:

    A B C D E F G H I J
    SCHOOL Student 1 (first) Student 1 (last) Completed Student 2 (first) Student 2 (last) Completed Student 3 (first) Student 3 (last) Completed
    XYZ Elementary Student One Yes Student Two No Student Three Yes
    ABC Elementary Student One Yes Student Two No Student Three Yes

    I need to perform a horizontal lookup to find all instances of "Yes" in a given row
    Then I need to randomly select one of those instances and pull the two fields immediately to the left (First Name, Last Name)

    I am having trouble because I need to perform a specific lookup to find the students who have completed the task within each row, and then choose a number of those "Yes" students out of the row and pull the two fields to the left.

    I usually should choose one, but there may be an instance where I need to choose up to three "Yes" students from each row.

    Is this possible without reformatting my spreadsheet? Is it possible at all.

    Thanks in advance,

    mkakish
    Last edited by mkakish; 12-09-2015 at 05:31 PM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Horizontal Lookup With Multiple Instances, to pull string in adjacent field randomly

    (How to) Upload a Workbook directly to the Forum
    (please do not post pictures or links to worksheets)
    • Click Advanced (next to quick post),
    • Scroll down until you see "Manage Attachments",
    • Click that then select "add files" (top right corner).
    • Click "Select Files" find your file, click "open" click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Click "Done" at bottom right to close the Attachment Manager.
    • Click "Submit Reply"
    Note: Please do not attach password protected workbooks/worksheets
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    12-09-2015
    Location
    SoCal, CA
    MS-Off Ver
    2010
    Posts
    2

    Re: Horizontal Lookup With Multiple Instances, to pull string in adjacent field randomly

    workbook-mkakish.xlsx

    Any help or guidance is greatly appreciated.

  4. #4
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: Horizontal Lookup With Multiple Instances, to pull string in adjacent field randomly

    Hi, I've made up a sheet that I think is fairly dodgy and am only submitting it for ideas because no-one else has provided anything better as yet
    Firstly, I added a column after every student to place column number if "Yes".
    Sheet2 lists the first 20 students for each line (this idea will probably suck if you have hundreds of students for each school).
    You enter 1 ,2, or 3 in the "No. Required" column to get random students that have "Yes" for completed.

    Another problem I had was that I quite often get duplicate random finds....no idea how to avoid that.

    As I said, I'm only submitting this to maybe prompt a Guru's thoughts
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Vba to lookup for specif alphanumeric value in a column and pull value from adjacent cell
    By saravanan1981 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-12-2016, 10:37 PM
  2. lookup multiple instances
    By dkoucky in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-28-2014, 09:30 PM
  3. [SOLVED] Lookup for multiple instances
    By sonyaswan in forum Excel General
    Replies: 2
    Last Post: 07-17-2012, 08:26 AM
  4. Count instances of a string across multiple columns with multiple criteria
    By rpthoth in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-01-2012, 09:23 AM
  5. is it possible to scan a string and eliminate multiple instances of a word?
    By SEBS_PARADISE in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-13-2011, 03:19 PM
  6. Vlookup Add Multiple instances of same string
    By jalweber in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-13-2011, 08:18 AM
  7. lookup multiple instances
    By NathanR in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-14-2007, 07:03 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1