+ Reply to Thread
Results 1 to 9 of 9

Display an Array of Data Based on Other Data

  1. #1
    Registered User
    Join Date
    09-08-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    25

    Display an Array of Data Based on Other Data

    I have a worksheet where column A is a list of names (with duplicates) &
    Column B is an order number. In column C I have a list of unique names
    pulled from Column A (done manually for now). In Column D I'd like to list
    all order numbers from column B that correspond to a particular name in
    column C.

    In this example I'm trying to figure out column D based in columns A, B & C:
    A B C D
    Wright A-1 Beltran A-21
    Pujols B-3 Jeter C-22
    Jeter C-22 Pujols B-33, J-22
    Wright F-38 Wright A-1, F-38
    Beltran A-21
    Pujols J-22

    Thanks.

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Display an Array of Data Based on Other Data

    heres one way with a helper column see col a
    Attached Files Attached Files
    "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

  3. #3
    Registered User
    Join Date
    09-08-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: Display an Array of Data Based on Other Data

    Quote Originally Posted by martindwilson View Post
    heres one way with a helper column see col a
    It's a nice idea but won't really work for me here. There are currently close to 300 rows in this spreadsheet so manually editing to get your column A is not feasible. Also, all the invoice numbers need to be in a single cell and separated by commas (not in separate cells).
    Last edited by nycstinger; 09-17-2009 at 07:38 AM.

  4. #4
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Display an Array of Data Based on Other Data

    you dont have to use a use any column you like ,once done copy paste back special values then delete helper column
    as long as none of your numbers start with 0 then you could perhaps
    do it this way, once done copy paste back special values (those in column I) then delete all the bits you dont need
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-08-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: Display an Array of Data Based on Other Data

    Quote Originally Posted by martindwilson View Post
    you dont have to use a use any column you like ,once done copy paste back special values then delete helper column
    as long as none of your numbers start with 0 then you could perhaps
    do it this way, once done copy paste back special values (those in column I) then delete all the bits you dont need
    I see now. Very interesting. Would this work for a named range as well? Like if I wanted column C to have a named range called Invoices?

  6. #6
    Registered User
    Join Date
    09-08-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: Display an Array of Data Based on Other Data

    I haven't worked out a good enough solution yet so oo reiterate what I'm looking to do: I'm looking for all occurrences of a given value in column A and looking to return the contents of their corresponding cells in column B.

  7. #7
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Display an Array of Data Based on Other Data

    its awkward with functions, a vba solution would probably be better if its something you have to repeat often.

  8. #8
    Registered User
    Join Date
    09-08-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: Display an Array of Data Based on Other Data

    I'm game for a VBA solution.

  9. #9
    Registered User
    Join Date
    09-08-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: Display an Array of Data Based on Other Data

    If I follow martinwilson's solution and end up with invoice numbers in their own individual cells (columns E-N) is there a way I could separate them from there? Something like 'if the invoice number begins with G-S put it in column P otherwise put it in column Q'?

    (I realize this solution is a bit clunky but I could put it in it's own separate sheet where people wouldn't have to see the mess.)
    Last edited by nycstinger; 09-18-2009 at 06:59 AM.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1