+ Reply to Thread
Results 1 to 2 of 2

Pull unique two column combinations and skip particular value

  1. #1
    Registered User
    Join Date
    08-07-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Pull unique two column combinations and skip particular value

    I need to create a summary table listing only unique entries from a source table. The key is split across two columns (Unit, Lesson). I've found an example online to do that much. The difficulty is that I also need to omit entries where 'Lesson' = 0, and I don't have a good enough grasp of array formulas to adapt the solution.

    The formula I'm using is:

    {=INDEX(Source[Unit],IFERROR(MATCH(TRUE(),ISNA(MATCH(Source[Unit]&Source[Lesson],Summary[[#Headers],[Unit]]:OFFSET([@Unit],-1,0)&Summary[[#Headers],[Lesson]]:OFFSET([@Lesson],-1,0),0)),0),1))}

    Right now, it's returning:

    Please Login or Register  to view this content.
    What I need is:


    Please Login or Register  to view this content.
    I'm attaching a minimal working example. Any help you can offer on adding the additional criterion or making the whole thing more efficient would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    08-07-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Pull unique two column combinations and skip particular value

    Figured it out.

    {=INDEX(Source[Unit],IFERROR(MATCH(TRUE(),ISNA(MATCH(Source[Unit]&Source[Lesson],Summary[[#Headers],[Unit]]:D1&Summary[[#Headers],[Lesson]]:E1,0))*Source[Lesson]<>0,0),2))}

    I do still wish there was a more elegant way of handling the #N/A produced by the first iteration, and that I could use structured references throughout without resorting to OFFSET, which slowed everything WAY down. I figured out a way to use INDEX instead that should have worked, but it produced circular reference errors. I'll settle for what I've got unless anyone has any strokes of genius.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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