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

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.

Thanks for your help,

Thomas
Copy-of-Daily-Sales-Today--6-.xlsx
0
nutsch
Asked:
nutsch
  • 2
1 Solution
 
krishnakrkcCommented:
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
 
nutschAuthor 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
 
nutschAuthor Commented:
Kris, I've asked an other similar follow-up question here.

Thanks,

Thomas
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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