Solved

# Retrieve list without duplicates excluding blanks - formulas

Posted on 2012-09-11
551 Views
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.

Thomas
Copy-of-Daily-Sales-Today--6-.xlsx
0
Question by:nutsch

LVL 18

Accepted Solution

Hi,

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

try

=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)),"")

Kris
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.

Thanks,

Thomas

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)),"")
0

LVL 39

Author Comment

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

Thanks,

Thomas
0

## Featured Post

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.