+ Reply to Thread
Results 1 to 4 of 4

List values based on rows

  1. #1
    Registered User
    Join Date
    12-10-2013
    Location
    dallas, tx
    MS-Off Ver
    Excel 2007
    Posts
    36

    List values based on rows

    This is one of those were a vlookup won't cut it. My data looks like this

    G -- H -- I
    16337 --XX--XX
    16337-- 238447-- PI009313
    16337-- 238836--XX

    16337--XX--XX
    16337-- 238447-- PI009313
    16337-- 238836--XX

    41524--XX--XX
    41524--XX-- #VALUE!

    41524--XX--XX
    41524--XX-- #VALUE!

    The -- separates columns in the example, and the XX signifies null cells. I need to build a formula to list of any Column G values, preferably unique, where Column I is #VALUE!. I need a formula because the list will be dynamic and may contain 10K + values altogether, but maybe 100 of those will have the error code.
    Last edited by Scalpel4; 03-11-2014 at 11:40 AM.

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: List values based on rows

    Well, I am not really sure if formula is the best concept. Ahe you tried advanced filter with duplicates removal?

    If you want formula try the array one (comitted Ctrl+Shift+Enter - not just enter) in L2:

    Please Login or Register  to view this content.
    and copy down

    If you allow duplicates it will be substantially shorter (again - array formula):

    Please Login or Register  to view this content.
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    12-10-2013
    Location
    dallas, tx
    MS-Off Ver
    Excel 2007
    Posts
    36

    Re: List values based on rows

    Thanks. Tried that, didn't work. I'm halfway there with this array formula which gives me the row numbers for matching cells:

    =LARGE(IF(ISERROR($I$1:$I$199999),ROW($I$1:$I$199999)),ROW(1:1))

    Now I'm trying to remember the formula to use to pull the value from column G

  4. #4
    Registered User
    Join Date
    12-10-2013
    Location
    dallas, tx
    MS-Off Ver
    Excel 2007
    Posts
    36

    Re: List values based on rows

    GOT IT!!! Thanks for your help!

    Array formula:
    =INDEX($G$1:$I$199999,LARGE(IF(ISERROR($I$1:$I$199999),ROW(!$I$1:$I$199999)),ROW(1:1)),1)

+ 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. Replies: 2
    Last Post: 10-25-2013, 07:01 PM
  2. [SOLVED] Finding Values in one random list, highlighting, and labeling based on a known list
    By mark00thomas in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-14-2013, 07:41 AM
  3. [SOLVED] Counting Occurrences of Items in a List Based on Separate List Values
    By wheel1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-10-2013, 01:04 PM
  4. [SOLVED] Edit Recorded Macro to filter column with about 200k rows based on a list of values.
    By rocksan in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-19-2012, 03:35 AM
  5. SUMIF to add values only from selected rows based on a list
    By ride_op in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 12-06-2010, 08:38 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