Extract unique values in Excel 2019

[Junarkar]

Hi,

I need to extract unique values based on two conditions. Tried below formula but no luck.

={IFERROR(INDEX('Raw Data'!$R:$R,MATCH(0,COUNTIF(Y10:$Y$10,'Raw Data'!$R:$R)+IF('Raw Data'!$N:$N<>$B$2,1,0)+IF('Raw Data'!$AD:$AD<>$D$2,1,0),0)),"")}

where condition 1 is in $B$2, Condition 2 is in $D$2.
Data to extract is in Raw Data'!$R:$R

Entered the formula in Y9.

My issue is this formula is pulling down the performance of my system.

Requirement is to extract unique list of Brands if if meets the condition in B2 & D2;

Ex. Get list of unique brands if Group is "com" and Type is "Pack".

As I'm using excel 2019. Is there any other way to achieve this without hampering the system performance.

Group	Type	Brand
Com	Pack	Apple
Com	NA	Lenovo
Com	Single	HP
Com	Single	Apple
Com	Single	Apple
comp	Single	HP
comp	Single	Lenovo
comp	Pack	Acer
comp	Pack	Acer
comp	Pack	Apple
comp	NA	Asus
comp	Pack	Apple
comp	Single	HP
comp	Pack	HP
comp	Pack	Apple

Attached sample file.

 

[pecoflyer]

There is no Raw Data sheet, nor value in D column in the attached file?

 

[John Jairo V]

Hi to both!

If I understood correctly, see attach file with some option. Recomendation: Don't use whole columns in order to compare ranges directly. Although COUNTIF, MATCH and INDEX can manage whole columns, direct comparison with a cell with whole column (like you use in an IF statement - IF('Raw Data'!$N:$N<>$B$2) - could be troublesome. Is better to use tables in Excel, for dynamic references.

Check the file with an option. Blessings!

 

[bosco_yip]

Try this single formula solution for your Excel 2019.

Get list of unique brands if Group is "com" and Type is "Pack".

1] Criteria of Group "com" in cell F1

2] Criteria of Type "Pack" in cell F2

3] List of Unique Brand F4, formula copied down:

=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,IF(ISNUMBER(FIND(F$1,A$2:A$16))*(B$2:B$16=F$2),C$2:C$16,""))&"</b></a>","a/b[not(.=preceding::b) and following::b]["&ROW(A1)&"]"),"")

 

[Junarkar]

Hi to both!

If I understood correctly, see attach file with some option. Recomendation: Don't use whole columns in order to compare ranges directly. Although COUNTIF, MATCH and INDEX can manage whole columns, direct comparison with a cell with whole column (like you use in an IF statement - IF('Raw Data'!$N:$N<>$B$2) - could be troublesome. Is better to use tables in Excel, for dynamic references.

Check the file with an option. Blessings!

Hi John,

Thanks alot for the solution. As you rightly said I converted my data to table.
And this formula is working for majority of the groups but not working for one group. I have added one more condition though. I am unable to understand where I went wrong.

Attaching file for your reference.

 

[Junarkar]

Hi to both!

If I understood correctly, see attach file with some option. Recomendation: Don't use whole columns in order to compare ranges directly. Although COUNTIF, MATCH and INDEX can manage whole columns, direct comparison with a cell with whole column (like you use in an IF statement - IF('Raw Data'!$N:$N<>$B$2) - could be troublesome. Is better to use tables in Excel, for dynamic references.

Check the file with an option. Blessings!

Hi, expecting your expert opinion.

 

[John Jairo V]

Hi again, @Junarkar!

You have some #N/A errors in the table, exactly in column MCH. Remove or replace them, and your problem is gone! Blessings!

 

[Junarkar]

Hi again, @Junarkar!

You have some #N/A errors in the table, exactly in column MCH. Remove or replace them, and your problem is gone! Blessings!

Thanks alot John. That worked like a charm.....

 

[Peter Bartholomew]

The #N/As sure made a monumental mess of the data.
Since I use Excel 365, my formulas are somewhat different.

= LET(
    combinedCriterion, (RawData[Group]=department)*(RawData[Category Desc]=category)*((RawData[MCH]=type)+(RawData[MCH]="NA")),
    selectedRecords,   FILTER(RawData[Brand], combinedCriterion),
    distinctRecords,   UNIQUE(selectedRecords),
    distinctRecords
  )

As you see, I make no attempt at brevity. The one difference I noted from the workbook I downloaded is that I took "NA" to be a match for any 'type' value.
BTW Was the table name meant to be 'RawData', or should I have left it as 'RowData'.

 

[Peter Bartholomew]

Just reread the title "... in Excel 2019".
My apologies and please accept my commiserations.
There were already versions of Excel that worked correctly when 2019 was released but the changes didn't make the cut.
Personally I think MS owes you a free upgrade to Excel 2021 which appears to be half-way decent code.