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

 

[Uzma Shaheen]

Yep there is but i changed the range to compensate for that

There is an additional 5 column in the main dataset (so initial table has 9 columns)

Contact number is still the 4th column so i guess that part of choosecols shouldn’t change?

Im using the original formula to pull back all data rather than just dept to test first

 

[BigBeachBananas]

Yep there is but i changed the range to compensate for that

There is an additional 5 column in the main dataset (so initial table has 9 columns)

Contact number is still the 4th column so i guess that part of choosecols shouldn’t change?

Im using the original formula to pull back all data rather than just dept to test first

Yeah make sure col 3 is still Dept and 4 is Contact , else you can make changes as needed.

 

[Uzma Shaheen]

Hey yes i tried the main table formula but for some reason something in dataset doesnt allow for it to return result from that choose cols part

There are no errors in data set

The formula below is what im using

3	=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)))