+ Reply to Thread
Results 1 to 12 of 12

Array: Find first occurance of a name

  1. 07-30-2010, 08:20 AM #1
    Lensmeister
    Lensmeister is offline
    Forum Contributor
    Join Date
    12-05-2006
    Location
    London
    MS-Off Ver
    Excel 365 (Version 2008) [work] & 365 [home]
    Posts
    182

    Question Array: Find first occurance of a name

    Hi all,

    I have attached an example of my problem to this thread too.

    Basically I have a bit of a problem.

    I have defined an group of cells (array ?) called apps.

    Each line is a match and has various items of data in it including a team line up. I need a formula that looks along each line until it finds the first instance of the name (i.e. Ben) and then puts the date in the second table.

    The date (once it is in the table) then populates the other data fields using a VLOOKUP (hence the N/A in some cells.

    Can anyone help me? Please?

    Thanks in advance.

    Lensmeister.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-30-2010, 08:58 AM #2
    WHER
    WHER is offline
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Array: Find first occurance of a name

    In cell I8: =INDEX($A$1:$A$6,LARGE((apps=H8)*(ROW(apps)),COUNTIF(apps,H8))) entered as array-formula (ctrl+shift+enter), drag down.

    In cell J8: =VLOOKUP(I8;$A$2:$B$6;2;FALSE), drag down.
    Reply With QuoteRegister To Reply
  3. 07-30-2010, 09:03 AM #3
    Lensmeister
    Lensmeister is offline
    Forum Contributor
    Join Date
    12-05-2006
    Location
    London
    MS-Off Ver
    Excel 365 (Version 2008) [work] & 365 [home]
    Posts
    182

    Re: Array: Find first occurance of a name

    Thanks,

    Retried .... Got it now.


    Many thanks for your help .... I a a bit of a doofus sometimes.
    Last edited by Lensmeister; 07-30-2010 at 09:23 AM.
    A mad football researcher and Statistician - ok just mad really !
    Reply With QuoteRegister To Reply
  4. 07-30-2010, 09:46 AM #4
    Lensmeister
    Lensmeister is offline
    Forum Contributor
    Join Date
    12-05-2006
    Location
    London
    MS-Off Ver
    Excel 365 (Version 2008) [work] & 365 [home]
    Posts
    182

    Re: Array: Find first occurance of a name

    I have changed the formula a littel to try and get it to work in a larger spreadsheet I have

    =INDEX(Data!$A$2:$A$10000,LARGE((AllApps=B2)*(ROW(AllApps)),COUNTIF(AllApps,B2)))


    This is returning #NUM each time

    Can you please advise ?
    Reply With QuoteRegister To Reply
  5. 07-30-2010, 10:14 AM #5
    WHER
    WHER is offline
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Array: Find first occurance of a name

    What number does "COUNTIF(AllApps,B2)" evaluate to?
    What range does "AllApps" cover?
    Since you refer to cell B2, the layout of your sheet must be seriously different from the example?
    Reply With QuoteRegister To Reply
  6. 07-30-2010, 10:27 AM #6
    Lensmeister
    Lensmeister is offline
    Forum Contributor
    Join Date
    12-05-2006
    Location
    London
    MS-Off Ver
    Excel 365 (Version 2008) [work] & 365 [home]
    Posts
    182

    Re: Array: Find first occurance of a name

    It is pretty different as the Spreadsheet I have already is huge (14mb+) and wouldn't upload.

    I can create a slight abbrviated version if this helps.

    Thanks for being Patient with me
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 07-30-2010, 11:41 AM #7
    WHER
    WHER is offline
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Array: Find first occurance of a name

    Some changes made, named ranges (such as AllApps) are now dynamic named ranges.
    The NUM# errors came from the countif evaluating to 0 if the name in B2 is not found in the database. Subsequently LARGE(...,0) gives the error.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 08-02-2010, 05:45 AM #8
    Lensmeister
    Lensmeister is offline
    Forum Contributor
    Join Date
    12-05-2006
    Location
    London
    MS-Off Ver
    Excel 365 (Version 2008) [work] & 365 [home]
    Posts
    182

    Re: Array: Find first occurance of a name

    OK First time back since last week.

    I got to say a BIG thank you for helping me with this and to say after changing and creating a couple new arrays and named ranged etc. I have it running on two seperate sheets giving two seperate results for two seperate sets of creiteria.

    I just want to check (as I am paranoid) this does check for the first occurance of the name row by row?

    Again THANKS VERY MUCH
    Reply With QuoteRegister To Reply
  9. 08-02-2010, 06:16 AM #9
    l.chaos45
    l.chaos45 is offline
    Registered User
    Join Date
    08-02-2010
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Array: Find first occurance of a name

    HIIII

    Each line is a match and has various items of data in it including a team line up. I need a formula that looks along each line until it finds the first instance of the name (i.e. Ben) and then puts the date in the second table.

    __________________
    "Want to get-on Google's first page and loads of traffic to your website?
    Hire a SEO Specialist from Ocean Groups seo specialist
    "
    Reply With QuoteRegister To Reply
  10. 07-06-2011, 04:30 AM #10
    Lensmeister
    Lensmeister is offline
    Forum Contributor
    Join Date
    12-05-2006
    Location
    London
    MS-Off Ver
    Excel 365 (Version 2008) [work] & 365 [home]
    Posts
    182

    Re: Array: Find first occurance of a name

    Hi ... Me again,

    I have a problem with my formula/spreadsheet above.

    I have added a couple of players in to the "database" and now those players only come up with the error #REF. I have tried loads of things.

    Also when I look in the small cell reference/named array dropdown in the left side of the formula bar the named arrays are not there

    What's gone wrong? ... Anyone?

    Please help as I am beginning to think that Excel has a personal vendetta on me lol.
    Reply With QuoteRegister To Reply
  11. 07-10-2011, 11:19 AM #11
    Lensmeister
    Lensmeister is offline
    Forum Contributor
    Join Date
    12-05-2006
    Location
    London
    MS-Off Ver
    Excel 365 (Version 2008) [work] & 365 [home]
    Posts
    182

    Re: Array: Find first occurance of a name

    Bump .. please help
    Reply With QuoteRegister To Reply
  12. 07-10-2011, 12:00 PM #12
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Array: Find first occurance of a name

    Lensmeister it is pointless reopening a year old thread, start a new one with a link to this one also attach your workbook
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    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