SOLVED

Generate Unique List from Duplicate Values and Date Constraints

Occasional Contributor

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.

SupplierBuyerLast_Activity
ABC TekstilKotonApr-22
ABC TekstilKotonJun-22
ABC TekstilKotonJul-22
AC TekstilKotonJul-22
AC TekstilArmedangelsJul-22
AC TekstilCelioAug-22
Bera TekstilNAKDAug-22
DyetexInditexAug-22
DyetexH&MAug-22
Meyer InditexMay-22
Meyer SQTSMay-22
Meyer KotonJul-22
SwingerVersaceJul-22
SwingerArmaniJul-22
SwingerMalen BirgerJul-22
SwingerZegnaAug-22
Viskos TeksCarollMar-22
Viskos TeksDisneyJul-22
Viskos TeksCarollJul-22
XYZ TekstilKimonoAug-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.

6 Replies

@alpertakci 

But AC Tekstil does have activity in August:

AC Tekstil Celio Aug-22
best response confirmed by alpertakci (Occasional Contributor)
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.

filter.JPG

 

In case of Jul-22 you have 5 unique supplier (ABC Tekstil, AC Tekstil, Meyer, Swinger, Viskos Teks). How your output is only 3?
There is a condition that they should not have any activities in August, thus AC Tekstil and Swinger are out, leaving me with three.
That is why it is not in the outcome. You are probably mistaken ABC with AC
Works like a charm! Thanks very much!