• Status: Solved
• Priority: Medium
• Security: Public
• Views: 562

# Retrieve list without duplicates excluding blanks - formulas

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
nutsch
• 2
1 Solution

Commented:
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

Author Commented:
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

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

Thanks,

Thomas
0

## Featured Post

• 2
Tackle projects and never again get stuck behind a technical roadblock.