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]

Try this? Adjust range as needed.

=TAKE(FILTER(A1:D10,(B1:B10=J1)*(C1:C10<>"")*NOT(ISNUMBER(SEARCH("SHIP",D1:D10)))),1)

 

[Uzma Shaheen]

Hey I tried that, the take function wont work as it only takes the first row however i want to take the first row for each contact number

My cells have changed but i managed to tweak to pull back the columns but the end result is still an issue

=FILTER(FILTER(Table1,((Table1[Date]=$L$1)*(Table1[Dept]<>"")*(NOT(ISNUMBER(SEARCH($K$2,Table1[Contact])))))),{1,0,1,1,0})

Datetime	Contact	Dept
01/01/2023 15:06:00	2477	HR
01/01/2023 16:00:00	2477	FINANCE
01/01/2023 16:02:00	2477	FINANCE
01/01/2023 16:05:00	500	FINANCE
01/01/2023 16:07:00	500	FINANCE
Final Result
Datetime	Contact	Dept
01/01/2023 15:06:00	2477	HR
01/01/2023 16:05:00	500	FINANCE

 

[BigBeachBananas]

Hey I tried that, the take function wont work as it only takes the first row however i want to take the first row for each contact number

My cells have changed but i managed to tweak to pull back the columns but the end result is still an issue

=FILTER(FILTER(Table1,((Table1[Date]=$L$1)*(Table1[Dept]<>"")*(NOT(ISNUMBER(SEARCH($K$2,Table1[Contact])))))),{1,0,1,1,0})

Datetime	Contact	Dept
01/01/2023 15:06:00	2477	HR
01/01/2023 16:00:00	2477	FINANCE
01/01/2023 16:02:00	2477	FINANCE
01/01/2023 16:05:00	500	FINANCE
01/01/2023 16:07:00	500	FINANCE
Final Result
Datetime	Contact	Dept
01/01/2023 15:06:00	2477	HR
01/01/2023 16:05:00	500	FINANCE

Ah that wasn't clear from the OP.

How about this?

=CHOOSEROWS(FILTER(A1:D10,(B1:B10=J1)*(C1:C10<>"")*NOT(ISNUMBER(SEARCH("SHIP",D1:D10)))),MATCH(UNIQUE(D1:D10),D1:D10,0))

where A=Datime, B = Date, C=Dept, D = contact.

 

[Uzma Shaheen]

And also if i can filter to take the first row for each contact and get the final result

I would like to add another filter to just filter the Dept (I have a separate HR sheet and Finance sheet) so i tried this without any luck

i know i need to get the end final result first - Getting a value error
=FILTER(FILTER(FILTER(Table1,((Table1[Date]=$L$1)*(Table1[Dept]<>"")*(NOT(ISNUMBER(SEARCH($K$2,Table1[Contact])))))),{1,0,1,1,0}),Table1[Dept]=$R$17)

Final Result
Datetime	Contact	Dept
01/01/2023 15:06:00	2477	HR
01/01/2023 16:05:00	500	FINANCE
Final Result HR				Final Result HR				HR
Datetime	Contact	Dept		Datetime	Contact	Dept		FINANCE
01/01/2023 15:06:00	2477	HR		#VALUE!
Final Result FINANCE
Datetime	Contact	Dept
01/01/2023 16:05:00	500	FINANCE

 

[BigBeachBananas]

And also if i can filter to take the first row for each contact and get the final result

I would like to add another filter to just filter the Dept (I have a separate HR sheet and Finance sheet) so i tried this without any luck

i know i need to get the end final result first - Getting a value error
=FILTER(FILTER(FILTER(Table1,((Table1[Date]=$L$1)*(Table1[Dept]<>"")*(NOT(ISNUMBER(SEARCH($K$2,Table1[Contact])))))),{1,0,1,1,0}),Table1[Dept]=$R$17)

Final Result
Datetime	Contact	Dept
01/01/2023 15:06:00	2477	HR
01/01/2023 16:05:00	500	FINANCE
Final Result HR				Final Result HR				HR
Datetime	Contact	Dept		Datetime	Contact	Dept		FINANCE
01/01/2023 15:06:00	2477	HR		#VALUE!
Final Result FINANCE
Datetime	Contact	Dept
01/01/2023 16:05:00	500	FINANCE

Did the formula in post#4 work for you for the non-blank Dept?

If it did then change the C1:C10 condition to..

=CHOOSEROWS(FILTER(A1:D10,(B1:B10=J1)*(C1:C10=$R$17)*NOT(ISNUMBER(SEARCH("SHIP",D1:D10)))),MATCH(UNIQUE(D1:D10),D1:D10,0))

for your specific finance, and HR sheet.

 

[Uzma Shaheen]

Thank you - i tried that but getting a value error

=CHOOSEROWS(FILTER(Table1[[#All],[Datetime]:[Contact]],(Table1[[#All],[Date]]=$L$1)*(Table1[[#All],[Dept]]<>"")*NOT(ISNUMBER(SEARCH($K$2,Table1[[#All],[Contact]])))),MATCH(UNIQUE(Table1[[#All],[Contact]]),Table1[[#All],[Contact]],0))

 

[BigBeachBananas]

It worked for me.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	Datetime	DATE	Contact	Dept								1/1/23
2	1/1/23 15:06	1/1/23	2477	HR			Result				SHIP
3	1/1/23 16:00	1/1/23	2477	FINANCE			1/1/23 15:06	1/1/23	2477	HR
4	1/1/23 16:02	1/1/23	2477	FINANCE			1/1/23 16:05	1/1/23	500	FINANCE
5	1/1/23 16:05	1/1/23	500	FINANCE
6	1/1/23 16:07	1/1/23	500	FINANCE
Sheet2

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

 

[Uzma Shaheen]

dont know why im getting that value error

The evaluate up until choose rows looks like this

=CHOOSEROWS({44927.6291666667,44927,"HR",2477,"No";44927.6666666667,44927,"FINANCE",2477,"No";44927.6680555556,44927,"FINANCE",2477,"No";44927.66875,44927,"FINANCE","SHIP","No";44927.6694444444,44927,"HR","SHIP","No";44927.6701388889,44927,"FINANCE",500,"No";44927.6715277778,44927,"FINANCE",500,"No"},{1;6;8})

 

[Uzma Shaheen]

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