Duplicate values to return unique value

Hello,
here's a suggestion using helper columns :

attach your sample file with some more example and show your expected result by entering manually

Hello,
Did you look at the suggestion I made?
Here's your new file with the helper columns...

(note that the identifiers are all different in your file except rows 15 and 16... was it an error ?)

Yes it does: the formula in D2 was wrong because I posted the formula for D3 by mistake!


In D2

=IF(COUNTIF($B$2:B2,B2)=COUNTIF($B$2:$B$16,B2),IF(COUNTIFS($B$2:$B$16,B2,A$2:A$16,"Email Referral"),"Email Referral","Phone Referral"),"")


P.S Is it necessary for the "Email Referral" to in the row which as "Email Referral" rather then the last entry for a given identifier??

How the formula works:

=IF(COUNTIF($B$2:B2,B2)=COUNTIF($B$2:$B$16,B2),IF(COUNTIFS($B$2:$B$16,B2,A$2:A$16,"Email Referral"),"Email Referral","Phone Referral"),"")

IF(COUNTIF($B$2:B2,B2)=COUNTIF($B$2:$B$16,B2),

This checks for the last entry (row) for a given identifier. The first COUNTIFS counts the number of a given identifier as you drag down the rows: note the range $b$2:b2 which changes to $b$2:b3 in row 3, $b$2:b4 in row 4 etc. The second COUNTIFS counts total number of a given identifier in the whole range,

When they are equal i.e. we have found the last row for a given identifier , I simple check if there is an "Email Referral" - COUNTIFS($B$2:$B$16,B2,A$2:A$16,"Email Referral") for this identifier. if YES , then this the "default" you want, so assign "Email Referral": otherwise assign "Phone Referral".

COUNTIFS($B$2:$B$16,B2,A$2:A$16,"Email Referral") is equivalent to COUNTIFS($B$2:$B$16,B2,A$2:A$16,"Email Referral")>=1. If you a get results >=1, this is TRUE, a result of 0 is FALSE (in the IF statement: =IF ( formula, TRUE result, FALSE result)

Where the first COUNTIFS do not match, the result is Null ("") or could be blank * ").

Hope this explains how it works.

Modified the formula to present data as per your example.

=IF(COUNTIFS($B$2:$B$16,B2,A$2:A$16,"Email Referral")>=1,IF(A2="Email Referral","Email Referral",""),IF(COUNTIF($B$2:B2,B2)=COUNTIF
($B$2:$B$16,B2),"Phone Referral",""))

or

=IF(COUNTIFS($B$2:$B$16,B2,A$2:A$16,"Email Referral"),IF(A2="Email Referral","Email Referral",""),IF(COUNTIF($B$2:B2,B2)=COUNTIF($B$2:$B$16,B2),"Phone Referral",""))

The basic logic is I described previously so hopefully you will understand the change.

Formula checks if there is an "Email Referral" and if yes, assigns this in appropriate row. If there is no "email" but more than one "phone" it assigns "Phone Referral" to last entry.