Generate Unique List from Duplicate Values and Date Constraints

[alpertakci]

I have a set of non-unique values for suppliers with corresponding activity dates. The desired outcome is to create a unique set of suppliers with the following activity date constraints:

1. Last_Activity date should not be the last whole month. (At the moment, it is August).

2. Last_Activity date should be the month before the last whole month. (For the time being, it is July).

I tried the following but still missed the desired result:

=UNIQUE(FILTER(FILTER(sample_table,((sample_table[Last_Activity]>$D$6)*(sample_table[Last_Activity]<$D$4))),{1,0,0}))

(The D6 above is Jun-22, and D4 is Aug-22.)

I have created sample data and the desired outcome in the below table.

Supplier	Buyer	Last_Activity
ABC Tekstil	Koton	Apr-22
ABC Tekstil	Koton	Jun-22
ABC Tekstil	Koton	Jul-22
AC Tekstil	Koton	Jul-22
AC Tekstil	Armedangels	Jul-22
AC Tekstil	Celio	Aug-22
Bera Tekstil	NAKD	Aug-22
Dyetex	Inditex	Aug-22
Dyetex	H&M	Aug-22
Meyer	Inditex	May-22
Meyer	SQTS	May-22
Meyer	Koton	Jul-22
Swinger	Versace	Jul-22
Swinger	Armani	Jul-22
Swinger	Malen Birger	Jul-22
Swinger	Zegna	Aug-22
Viskos Teks	Caroll	Mar-22
Viskos Teks	Disney	Jul-22
Viskos Teks	Caroll	Jul-22
XYZ Tekstil	Kimono	Aug-22

Desired Outcome:

Supplier

ABC Tekstil

Meyer

Viskos Teks

Since only the above three does not have any August activities and also have some activity in July, they make up the list. My only concern is to get unique values for the suppliers.

Thank you in advance for your assistance.

[#2]

@alpertakci 

But AC Tekstil does have activity in August:

AC Tekstil

Celio

Aug-22

[#3 · Accepted solution]

@alpertakci 

=UNIQUE(FILTER(A2:A21,(COUNTIFS($A$2:$A$21,$A$2:$A$21,$C$2:$C$21,$D$4)=0)*(COUNTIFS($A$2:$A$21,$A$2:$A$21,$C$2:$C$21,">"&$D$6)>0)))

This formula returns the result in my sheet.

[#4]

In case of Jul-22 you have 5 unique supplier (ABC Tekstil, AC Tekstil, Meyer, Swinger, Viskos Teks). How your output is only 3?

[#5]

There is a condition that they should not have any activities in August, thus AC Tekstil and Swinger are out, leaving me with three.

[#6]

That is why it is not in the outcome. You are probably mistaken ABC with AC :)

[#7]

Works like a charm! Thanks very much!