+ Reply to Thread
Results 1 to 3 of 3

Help fix, almost perfect, formula that extracts unique list from two columns

  1. #1
    Forum Contributor
    Join Date
    10-03-2015
    Location
    North Carolina, USA
    MS-Off Ver
    Office 365
    Posts
    167

    Help fix, almost perfect, formula that extracts unique list from two columns

    I have two columns with numbers in them. I found a formula that extracts a unique list of the numbers in these two columns.

    For example, if 3202 is present in column 0, but not present in column P, then the formula will show 3202 in column Q (Q is where the formula is). If 7102 is present in both columns O and P, then the formula will show 7102 in column Q only once.

    The formula I have works perfectly except one little annoying issue. It leaves the first cell with the formula blank. I would expect my first result to appear in the first cell where I have the formula, but it doesn't work that way. How can I fix the formula so that the first instance of a unique number appears in the first cell in which I have entered the formula?

    See attached for reference. Note, I would expect the first result (7302) to appear in cell Q4.

    Additional detials (which are obvious once you open the attachment):
    Column O header is in cell O3, and data below it is in cells O4:O24
    Column P header is in cell P3, and data below it is in cells P4:P24
    Column Q header is in cell Q3, and formula below it is in cells Q4:Q24

    Formula is: =IFERROR(IFERROR(LOOKUP(2, 1/(COUNTIF($Q$3:Q3,$O$4:$O$24)=0), $O$4:$O$24), LOOKUP(2, 1/(COUNTIF($Q$3:Q3, $P$4:$P$24)=0), $P$4:$P$24)),"")

    If I change it as follows, it works 100% perfect, but it isn't like the example I found, and I just want to know if there is potential for issues with my alteration:
    =IFERROR(IFERROR(LOOKUP(2, 1/(COUNTIF($Q$2:Q3,$O$4:$O$24)=0), $O$4:$O$24), LOOKUP(2, 1/(COUNTIF($Q$2:Q3, $P$4:$P$24)=0), $P$4:$P$24)),"")

    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Help fix, almost perfect, formula that extracts unique list from two columns

    it's not clear if the order the values are returned in is critical, or not -- i.e. Col O last to first, Col P last to first.

    If not, then a simpler approach, given your (numeric) data, would be:

    Q3: =IFERROR(AGGREGATE(15,6,($O$4:$P$24)/(COUNTIF($Q$3:$Q3,$O$4:$P$24)=0),1),"")
    copied down

    if, in real life, your values are not numbers - or the ordering is critical - post back.

  3. #3
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Help fix, almost perfect, formula that extracts unique list from two columns

    How can I fix the formula so that the first instance of a unique number appears in the first cell in which I have entered the formula?
    I didn't answer your actual question -- you need to account for blanks in your source ranges (O & P), one way:

    LOOKUP(2,1/COUNTIF(...)/(range<>""),range)

+ 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: 1
    Last Post: 03-01-2018, 01:18 PM
  2. Replies: 3
    Last Post: 06-29-2017, 04:19 PM
  3. Replies: 2
    Last Post: 06-16-2016, 08:09 PM
  4. Array formula to return list of unique items from selected columns only
    By jlawton1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-23-2016, 04:53 AM
  5. Replies: 0
    Last Post: 07-26-2012, 08:29 PM
  6. 3 columns: None contain unique values, but I need a list of every unique set
    By mathematician in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-15-2012, 04:47 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