Retrieve list without duplicates excluding blanks - formulas

Posted on 2012-09-11
Last Modified: 2012-09-12
Hi experts,

I have the following error in the attached file. If the Sales Rep column is blank, my formula to retrieve a list without duplicates get thrown off.

The array formula is as follows, and is located in the column C of the Summary Tab
=IFERROR(INDEX(INDIRECT("Sales_Line[Salesperson]"), MATCH(MIN(IF(COUNTIF(C$6:C6, INDIRECT("Sales_Line[Salesperson]"))=0, 1, MAX((COUNTIF(INDIRECT("Sales_Line[Salesperson]"), "<"&INDIRECT("Sales_Line[Salesperson]"))+1)*2))*(COUNTIF(INDIRECT("Sales_Line[Salesperson]"), "<"&INDIRECT("Sales_Line[Salesperson]"))+1)), COUNTIF(INDIRECT("Sales_Line[Salesperson]"), "<"&INDIRECT("Sales_Line[Salesperson]"))+1, 0)),"")

Fill cell D24 and 25 of tab Report to see the way the summary formula is supposed to work.

Thanks for your help,

Question by:nutsch
    LVL 18

    Accepted Solution


    First of all, you don't need the INDIRECT which make the formula volatile.


    =IFERROR(INDEX(Sales_Line[Salesperson], MATCH(MIN(IF(IF(Sales_Line[Salesperson]<>"",COUNTIF(C$6:C6, Sales_Line[Salesperson]))=0, 1, MAX((IF(Sales_Line[Salesperson]<>"",COUNTIF(Sales_Line[Salesperson], "<"&Sales_Line[Salesperson]))+1)*2))*(IF(Sales_Line[Salesperson]<>"",COUNTIF(Sales_Line[Salesperson], "<"&Sales_Line[Salesperson]))+1)),IF(Sales_Line[Salesperson]<>"", COUNTIF(Sales_Line[Salesperson], "<"&Sales_Line[Salesperson]))+1, 0)),"")

    LVL 39

    Author Closing Comment

    Thanks Kris, I still need the indirect as that table gets dropped and rebuilt in the reporting process (Jet Reports), but your solution works perfect.



    Final formula:

    =IFERROR(INDEX(INDIRECT("Sales_Line[Salesperson]"), MATCH(MIN(IF(IF(INDIRECT("Sales_Line[Salesperson]")<>"",COUNTIF(C$6:C6, INDIRECT("Sales_Line[Salesperson]")))=0, 1, MAX((IF(INDIRECT("Sales_Line[Salesperson]")<>"",COUNTIF(INDIRECT("Sales_Line[Salesperson]"), "<"&INDIRECT("Sales_Line[Salesperson]")))+1)*2))*(IF(INDIRECT("Sales_Line[Salesperson]")<>"",COUNTIF(INDIRECT("Sales_Line[Salesperson]"), "<"&INDIRECT("Sales_Line[Salesperson]")))+1)),IF(INDIRECT("Sales_Line[Salesperson]")<>"", COUNTIF(INDIRECT("Sales_Line[Salesperson]"), "<"&INDIRECT("Sales_Line[Salesperson]")))+1, 0)),"")
    LVL 39

    Author Comment

    Kris, I've asked an other similar follow-up question here.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now