Using IF to sort unique entries

I have a database with over 11,000 entries. I am trying to find a way to count the number of unique pathogens occurring on 160 different species of bats. So far I have managed to figure out how to relate each host entry to each pathogen entry using the formula:

=IF(SUMPRODUCT((A2:D2=A2:D2)*(E2:H2=E2:H2))>1,1,0)

Of course, this returns as true for every row as seen in the example below. What I need the formula to do is return a true statement only if it is the first unique association, with other identical associations coming back as false. Ultimately I want to be able to list each host species with the total number of unique pathogens for each. I know this table displays strange in the threads, I have attached a sample excel table as well. Any assistance at all would be greatly appreciated.

A B C D E F G H I
Host_Order Host_Family Host_Genus Host_Spec Path_Order Path_Famil Path_Genus Path_Species HOSTPATH I want
Chiroptera Phyllostomidae Ametrida centurio Acariformes Trombiculidae Whartonia nudosetos 1 1
Chiroptera Phyllostomidae Ametrida centurio Acariformes Trombiculidae Whartonia nudosetos 1 0
Chiroptera Phyllostomidae Ametrida centurio Diptera Streblidae Anastrebla spurrelli 1 1
Chiroptera Phyllostomidae Ametrida centurio Diptera Streblidae Anastrebla spurrelli 1 0
Chiroptera Phyllostomidae Ametrida centurio Diptera Streblidae Anastrebla spurrelli 1 0
Chiroptera Phyllostomidae Ametrida centurio Diptera Streblidae Strebla harderi 1 1
Chiroptera Phyllostomidae Ametrida centurio Parasitifor Spinturnicid Periglischr iheringi 1 1
Chiroptera Phyllostomidae Ametrida centurio Parasitifor Spinturnici Periglischr iheringi 1 0
Chiroptera Phyllostomidae Anoura caudata Acariformes Trombiculid Euschoeng aemulata 1 1
Chiroptera Phyllostomidae Anoura caudifer Acariformes Labidocarpi Alabidocar furmani 1 1
Chiroptera Phyllostomidae Anoura caudifer Acariformes Labidocarpi Alabidocar furmani 1 0
Chiroptera Phyllostomidae Anoura caudifer Acariformes Trombiculid Parascosc aemulata 1 1

Paste

=CONCATENATE(A2,B2,C2,D2,E2,F2,G2,H2)

into J2 and copy down

Paste

=IF(COUNTIF(J$2:J2,J2) = 1,TRUE,FALSE)

into K2 and copy down.

You could instead just select the entire table and remove duplicates:

Please Login or Register  to view this content.