Filter First Unique value

[Uzma Shaheen]

Hey is it possible to use the Filter function to filter my data in a specific order and the give me me the first unique value for that contact number on that date?

For example

Say i have data like this (data is sorted by contact and then datetime)

DateTime…Date….Dept…Contact
01/10 16:06 01/10 Finance 0267
01/10 16:55 01/10 HR. 0267
01/10. 16:78 01/10 0267

In example above

1) I want filter on the date = to cell J1
2) Dept <> “”
3) Contact to exclude anything that has the word SHIP in it

I would want the data to then look like this based on above conditions

DateTime…Date….Dept…Contact
01/10 16:06 01/10 Finance 0267
01/10 16:55 01/10 HR. 0267

And then using that data - filter on DateTime to give first unique value for that contact/first contact occurrence details

End Result (this is the first record for that contact number from filtered list on that date)

DateTime…Date….Dept…Contact
01/10 16:06 01/10 Finance 0267

 

[BigBeachBananas]

I think the error is because the match unique is trying to filter the an additional unique value (row 8)?

Is there anything in row 8?

 

[Uzma Shaheen]

There is yep but shouldnt be returned

example dataset below

Datetime	Date	Dept	Contact
44927.625	01/01/2023		2477
44927.62569	01/01/2023		2477
44927.62917	01/01/2023	HR	2477
44927.66667	01/01/2023	FINANCE	2477
44927.66806	01/01/2023	FINANCE	2477
44927.66875	01/01/2023	FINANCE	SHIP
44927.66944	01/01/2023	HR	SHIP
44927.67014	01/01/2023	FINANCE	500
44927.67153	01/01/2023	FINANCE	500
44928.67083	02/01/2023	FINANCE	500
44929.66875	03/01/2023	HR	500
44929.66736	03/01/2023	FINANCE	SHIP
44929.66806	03/01/2023	FINANCE	SHIP
44929.66875	03/01/2023	SHIP	SHIP
44929.66944	03/01/2023	SHIP	SHIP

 

[Uzma Shaheen]

in the output based on the data it returns 5 rows and it should give back row 1 and row 4 as thats the first row for that contact number
the unique function i can see would work but because it spills out that extra row will cause issue

Datetime	Date	Dept	Contact
01/01/2023 15:06:00	44927	HR	2477	1
01/01/2023 16:00:00	44927	FINANCE	2477
01/01/2023 16:02:00	44927	FINANCE	2477
01/01/2023 16:05:00	44927	FINANCE	500	4
01/01/2023 16:07:00	44927	FINANCE	500

 

[Uzma Shaheen]

the match needs to be spilling out 1 and 4 based on the filtered table - i guess need to return the 1st position of that contact in filtered table then next position of next contact but wont have a clue on how to do that

 

[BigBeachBananas]

This looks terrible but..

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	Datetime	Date	Dept	Contact								1/1/23
2	44927.625	1/1/23		2477			Datetime	Date	Dept	Contact	SHIP
3	44927.6257	1/1/23		2477			1/1/23 15:06	1/1/23	HR	2477
4	44927.6292	1/1/23	HR	2477			1/1/23 16:05	1/1/23	FINANCE	500
5	44927.6667	1/1/23	FINANCE	2477
6	44927.6681	1/1/23	FINANCE	2477
7	44927.6688	1/1/23	FINANCE	SHIP
8	44927.6694	1/1/23	HR	SHIP
9	44927.6701	1/1/23	FINANCE	500
10	44927.6715	1/1/23	FINANCE	500
11	44928.6708	2/1/23	FINANCE	500
12	44929.6688	3/1/23	HR	500
13	44929.6674	3/1/23	FINANCE	SHIP
14	44929.6681	3/1/23	FINANCE	SHIP
15	44929.6688	3/1/23	SHIP	SHIP
16	44929.6694	3/1/23	SHIP	SHIP
Sheet2

Cell Formulas
Range	Formula
G3:J4	G3	=LET(f,FILTER(Table1,(Table1[Date]=$L$1)*(Table1[Dept]<>"")*(NOT(ISNUMBER(SEARCH($K$2,Table1[Contact]))))),CHOOSEROWS(f,MATCH(UNIQUE(FILTER(Table1[Contact],Table1[Contact]<>$K$2)),CHOOSECOLS(f,4),0)))
Dynamic array formulas.

 

[Uzma Shaheen]

Let me give that a go - thank you - really appreciate it

Would this be dynamic too?

 

[BigBeachBananas]

Let me give that a go - thank you - really appreciate it

Would this be dynamic too?

Dynamic in what way? It is a dynamic range.

 

[Uzma Shaheen]

That seems to work beautifully - thank you

Trying to understand the let bit of the function

The thing that doesnt work is if i wanted to filter just the Dept for my other sheets i mentioned so need to tweak slightly when i input formula on its own sheet too

i tried

=LET(f,FILTER(Table1,(Table1[Date]=$L$1)*(Table1[Dept]<>"")*(Table1[Dept]=$R$17)*(NOT(ISNUMBER(SEARCH($K$2,Table1[Contact]))))),CHOOSEROWS(f,MATCH(UNIQUE(FILTER(Table1[Contact],Table1[Contact]<>$K$2)),CHOOSECOLS(f,4),0)))

 

[Uzma Shaheen]

sorry i seen hard coded 4 in choose cols and thought it wont be dynamic - my bad

 

[BigBeachBananas]

That seems to work beautifully - thank you

Trying to understand the let bit of the function

The thing that doesnt work is if i wanted to filter just the Dept for my other sheets i mentioned so need to tweak slightly when i input formula on its own sheet too

i tried

=LET(f,FILTER(Table1,(Table1[Date]=$L$1)*(Table1[Dept]<>"")*(Table1[Dept]=$R$17)*(NOT(ISNUMBER(SEARCH($K$2,Table1[Contact]))))),CHOOSEROWS(f,MATCH(UNIQUE(FILTER(Table1[Contact],Table1[Contact]<>$K$2)),CHOOSECOLS(f,4),0)))

Can you share a sample of your department-specific sheet and what's the desired output? It might not be this complicated.