How to dynamically denote unique records in filtered range or table?

Hey everyone, I'm using a desktop or enterprise server version of Excel 2016 so i don't have access to some of the newer functions like UNIQUE or XLOOKUP. I'm trying to find a dynamic array formula (sorry if that's not the right terminology) like AGGREGATE or SUBTOTAL that will automatically recalculate when my table is filtered to identify unique or distinct records. I've done a lot of searching and tried several different formula variations, but haven't been able to get the "dynamic" piece figured out...

In the example below, i have a table that contains duplicate neigh values. I can use this formula; =IF(COUNTIF($A$2:$A5,$A2)=1,"Distinct","") to identify distinct rows in the table, but I would like it to recalculate when I filter the table down by another column like the AGGREGATE function does when you select the option to ignore hidden rows and errors...

In my distinct column I'm currently using this formula =IF(COUNTIF($A$2:$A5,$A2)=1,"Distinct",""), I've tried using CTRL-Shift-Enter to make it an array formula as well as several other variations using SUMPRODUCT and AGGREGATE with the SMALL function, but haven't been able to achieve the desired result, which would be that if i filtered the Project Type column in the table to exclude ABC, the Distinct column would recalculate to display the value 'Distinct' in the next visible row for Neigh 001 (Project Type EFG)...

Neigh Code	Project Type	Distinct?
001	ABC	Distinct
001	EFG
001	HIJ
002	ABC	Distinct

Desired result when table is filtered to exclude Project Type ABC: (the next visible record for Neigh Code 001 becomes the Distinct record...)

Neigh Code	Project Type	Distinct?
001	EFG	Distinct
001	HIJ

The ultimate goal is to be able to filter the table to the desired result set and then apply the filter to the 'Distinct' column to further reduce that set down to only distinct Neigh Code records.

Any help would be greatly appreciated!

Thanks!

1. Please update your profile.

2. D2, copied down:

=AGGREGATE(3,7,A2)


E2 copied down:
=IF(COUNTIFS(A$2:A2,A2,D$2:D2,1)=1,"Distinct","")

Now apply your filter.