Find rows/groups with unique values and return

I am having 127 columns and 405 rows. A row consist of a group and column has species name as given below.

Table 1.

Groups	species
Group[ 0 ]	Olea_europaea	Olea_europaea
Group[ 1 ]	Codium_arabicum	Codium_arabicum
Group[ 2 ]	Codium_duthieae	Codium_duthieae	Codium_arabicum*
Group[ 3 ]	Rhazya_stricta

* odd species in this group

In result section, I need to have comments as below by matching the species list column from the table 2 to table 1:

If Same species are grouped together and not found in any other group then : TRUE
If Same species are grouped together but also found in other group then : AMBIGUOUS
If Multiple species are grouped together then : INCORRECT
If only one individual of representative species, this species in not in any other group then : SINGLETON

Table 2.

Species list	Comment (Result)
Codium_arabicum	AMBIGIOUS
Codium_duthieae	INCORRECT
Olea_europaea	TRUE
Rhazya_stricta	SINGLETON

Kindly, suggest formula to comment as above.

one option attached... leverages some basic helpers on results tab to keep calcs relatively lightweight.

you may prefer to use a UDF / Sub Routine {VBA} to do this as would be much more efficient.


to avoid getting into trouble with the mods - calcs involved below

1. identify unique count of species - used to limit no. of calcs performed {given relatively expensive}

Formula:

Please Login or Register  to view this content.

2. identify position in matrix of each unique item, in A-Z order

Formula:

Please Login or Register  to view this content.

3. identify total count of species in data set

Formula:

Please Login or Register  to view this content.

with above in place the actual results are then, simply:

Species:

Formula:

Please Login or Register  to view this content.

the VB engine is preventing me from posting the final formula (for comment).

NOTE:

one question -- is the * in your sample set a literal * or did you add to highlight difference? if literal this will impact your unique count / countif type calcs, and would need to be addressed.

Thank you very much! XLent & SimonLock it worked...

* was added to highlight difference only...

I am having one more problem in the species names: Every species has unique accession ids like in"Olea_europaea" for Table No. 1:
LT222085.1|Olea_europaea LT222086.1|Olea_europaea

How can I ignore the accession ids for getting results as in Table No. 2.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

add a ROUND to A1:

Formula:

Please Login or Register  to view this content.

edit: added file w/above adjustment and tweak on "key" calc.

You will want to remove the SOLVED tag from the thread if you wish for further help.

Are you still using Excel 2016, or have you upgraded to something more recent?? Please confirm and/or amend your profile.

Specifically, I need to know a) if you have Office 365 and b) if you are using a PC (i.e. not a Mac). If yes & yes.... this is simple to do.

I used PQ to unpivot your table and tried to do the required calculations. I hope it came out right

HTML Code: 

I will not pretend to understand the formulae in use in column A... I have changed the formulae in the bits shaded red... At least it goes all the way to 382 now...

I've not been on the board much of late so didn't see this until today.

I have been using your formulated .xlsb file without any errors or issues to date. However, with the present dataset, I am getting errors as '#NUM' in the 'results sheet' from the line no. 334.

as the matrix now exceeds 100 columns the previous calculation (Col A) will not generate the holistic list of unique co-ordinates; to address, change references to 100 in both A3 & C3 to 1000, and copy down:

Formula:

Please Login or Register  to view this content.