filtering for new and expired deals

[daveyc18]

Loading Google Sheets

here's my current sheet but there must be a better way to do this

I don't like it right now because if I, say, try to filter for "expired deals", it'll also filter the first three columns abc ...

ideal scenario is the sheet displays both only expired and new deals

 

[jorismoerings]

In office 365 - try something like this:

SAMPLe DATA (2) (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J
1	ExtractDate	TradeID	New Deals		ExtractDate	TradeID	Expired Deals		Type	TradeID
2	7-9-2023	SAD677	SAD677		6-9-2023	SAD677	SAD677		New Deal	SJR117
3	7-9-2023	SAF785	SAF785		6-9-2023	SAF785	SAF785		New Deal	SJR124
4	7-9-2023	SAF789	SAF789		6-9-2023	SAF789	SAF789		New Deal	SJR139
5	7-9-2023	SAF794	SAF794		6-9-2023	SAF794	SAF794		New Deal	SJR204
6	7-9-2023	SAF797	SAF797		6-9-2023	SAF797	SAF797		New Deal	SJR230
7	7-9-2023	SAF800	SAF800		6-9-2023	SAF800	SAF800		New Deal	SJR233
8	7-9-2023	SAK489	SAK489		6-9-2023	SAK489	SAK489		Expired Deal	SHR493
9	7-9-2023	SAM545	SAM545		6-9-2023	SAM545	SAM545		Expired Deal	SID833
10	7-9-2023	SAO357	SAO357		6-9-2023	SAO357	SAO357		Expired Deal	SII917
11	7-9-2023	SAQ008	SAQ008		6-9-2023	SAQ008	SAQ008		Expired Deal	SIJ206
New, Matured

Cell Formulas
Range	Formula
I2:J11	I2	=LET(previous,FILTER(Previous!B:B,Previous!B:B<>""),current,FILTER(Current!B:B,Current!B:B<>""),stack1,UNIQUE(VSTACK(current,previous),FALSE),check,SWITCH(TRUE(),(ISNUMBER(MATCH(stack1,current,0))+ISERROR(MATCH(stack1,previous,0)))=2,"New Deal",(ISERROR(MATCH(stack1,current,0))+ISNUMBER(MATCH(stack1,previous,0)))=2,"Expired Deal","AA"),stack2,HSTACK(check,stack1),FILTER(stack2,check<>"AA"))
C2:C11	C2	=XLOOKUP(B2,Previous!B:B,Previous!B:B,"NEW DEAL")
G2:G11	G2	=XLOOKUP(F2,B:B,B:B,"EXPIRED")
Dynamic array formulas.