Forum Discussion
Solved
Generate Unique List from Duplicate Values and Date Constraints
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.
=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.
6 Replies
- 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.
=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.
- Works like a charm! Thanks very much!
- That is why it is not in the outcome. You are probably mistaken ABC with AC 🙂
