Finding Unique Values Based on Dates

[zalik22]

Hi,

I have some data, column A is text terms, and column F is 52 week ending dates. Is there a way to show only the unique values in column G? For example, if G1 is 1/8/24, G2 and below would only show the column A terms that are in 1/8/24 week ending but not in 1/1/24 week ending (i.e. new terms)?

Thanks!

 

[etaf]

would
=UNIQUE(FILTER(A2:A6,F2:F6=G1))
work for you

Book5
	A	B	C	D	E	F	G
1							1/1/24
2	a					1/1/24	a
3	b					2/1/23	b
4	c					3/1/23
5	a					1/1/24
6	b					1/1/24
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=UNIQUE(FILTER(A2:A6,F2:F6=G1))
Dynamic array formulas.

 

[zalik22]

Hi,

Sorry, I think it would have to compare 1/8/24 to 1/1/24 and see the "new" unique values. So if 1/1/24 and 1/8/24 had values of "a", "a" would not be included in column G, since it was there in 1/1/24. If "b" was not in week ending 1/1/24 but in week ending 1/8/24, then "b" would be in column G. Thanks!

 

[etaf]

how about
=UNIQUE(FILTER(A2:A6,(F2:F6>=G1)*(F2:F6<=G1+7)))

Book5
	A	B	C	D	E	F	G
1							1/1/24
2	a					1/1/24	a
3	a					1/8/23
4	c					3/1/23
5	a					1/5/24
6	a					1/1/24
Sheet1

Cell Formulas
Range	Formula
G2	G2	=UNIQUE(FILTER(A2:A6,(F2:F6>=G1)*(F2:F6<=G1+7)))

 

[zalik22]

how about
=UNIQUE(FILTER(A2:A6,(F2:F6>=G1)*(F2:F6<=G1+7)))

Book5
	A	B	C	D	E	F	G
1							1/1/24
2	a					1/1/24	a
3	a					1/8/23
4	c					3/1/23
5	a					1/5/24
6	a					1/1/24
Sheet1

Cell Formulas
Range	Formula
G2	G2	=UNIQUE(FILTER(A2:A6,(F2:F6>=G1)*(F2:F6<=G1+7)))

Thanks, this appears to give me unique values but not "new" ones. For example below, formula should only return "system" because it is in 1/8/24 but not 1/1/24.

wording				date
test				1/1/24
test				1/8/24
system				1/8/24
fail				1/1/24

 

[etaf]

working for me

Book5
	A	B	C	D	E	F	G
1	wording					date	1/1/24
2	test					1/1/24	test
3	test					1/8/24	system
4	system					1/8/24	fail
5	fail					1/1/24
Sheet2

Cell Formulas
Range	Formula
G2:G4	G2	=UNIQUE(FILTER(A2:A6,(F2:F6>=G1)*(F2:F6<=G1+7)))
Dynamic array formulas.

 

[zalik22]

Hi, I only want "system" to show up. I am getting test, system, and fail. "system" is in 1/8/24 but not in 1/1/24.

 

[etaf]

i dont understand then what you are after - WHY only system, since the other items are in the list for 1/1/24 to 8/1/24

 

[Fluff]

My take on this

=FILTER(A2:A100,(F2:F100=G1)*(COUNTIFS(F:F,"<"&G1,A:A,A2:A100)=0))

 

[zalik22]

i dont understand then what you are after - WHY only system, since the other items are in the list for 1/1/24 to 8/1/24

Sorry for the confusion. I am looking for ONLY new values compared to the previous week. So for 1/8/24, there are 2 terms, "test" and "system". "test" is in the previous week of 1/1/24, so I don't want that to display in column G. I am looking for the formula to output "system" because it is in 1/8/24 but not in 1/1/24.