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]

Hey Sorry - i dont have access to a computer at the moment

Essentially all i wanted to is also have the option to filter the departments on its own sheet too so that way i have a unique filtered list using the function you gave and then adding an additional filter to filter department if i wanted to on its own sheet

 

[BigBeachBananas]

Hey Sorry - i dont have access to a computer at the moment

Essentially all i wanted to is also have the option to filter the departments on its own sheet too so that way i have a unique filtered list using the function you gave and then adding an additional filter to filter department if i wanted to on its own sheet

Can't you just filter the result table from above assuming it's the same data?

 

[Uzma Shaheen]

hey yes thats why i tried doing in the formula

I wanted it to be dynamic so each dept can have its own filtered contacts

=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))) This formula works beautifully but as mentioned not if i then filtered a step further to just get all departments= department cell I tried =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))), FILTER(f, (Table1[Dept]=DeptRange))

 

[BigBeachBananas]

FINANCE...

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

 

[Uzma Shaheen]

Thank you

Ill give that a go when i have access to a computer.
At 1st glance not sure it will work like that as its filtering the dept to finance first therefore say i had

01/01/2023 01:10 01/01/2023 HR 247
01/01/2023 01:20 01/01/2023 FINANCE 247

I think the formula will give me
01/01/2023 01:20 01/01/2023 FINANCE 247

However that will be the wrong outcome as its still the 2nd value for that contact number so if did a filter for HR too separately it will give

01/01/2023 01:10 01/01/2023 HR 247

So will appear in both which wont be right hence why what i needed was to use the formula you provided as a whole and then filter that to give HR or Finance etc

Essentially just splitting that end result filter table

 

[Uzma Shaheen]

Hey please see my latest post - the latest result formula doesnt give right result as for the dept it could have the contact number appear in multiple departments hence why i need to use the formula you provided and then apply a filter on that to get the department from that end result table

 

[BigBeachBananas]

How about this? Update "HR" to your dept.

=LET(t,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))),FILTER(t,CHOOSECOLS(t,3)="HR"))

 

[Uzma Shaheen]

Thank you so much
Il give that a go
I didn’t realise you could use let multiple tims

 

[Uzma Shaheen]

Hey - thank you once again

For some reason im getting an error using todays dataset (even when testing on say 7 rows of data i get an NA error instead)

So everything with the filter function when stepping through works but when i evaluate the Choosecols i get
(NA, NA, 1, NA, 2) and then when it fully evaluates it just returns an error

Any reasons why this could happen?

 

[BigBeachBananas]

Hey - thank you once again

For some reason im getting an error using todays dataset (even when testing on say 7 rows of data i get an NA error instead)

So everything with the filter function when stepping through works but when i evaluate the Choosecols i get
(NA, NA, 1, NA, 2) and then when it fully evaluates it just returns an error

Any reasons why this could happen?

Are there additional columns?