# How can I list in one cell, unique values in a row

I have the following data in an Excel spreadsheet

Team1   Team2   Team3   Team4
C             C             All          None

In the next cell in the row, I want to list values that are not duplicated (i.e. All and None) separated by a comma.
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Here's one non-vba way to do that. Refer to the attached. It uses a series of IF formulas. Example of formula in column E:

=IF(OR(A1=B1,A1=C1,A1=D1,A1=""),"",A1&IF(LEN(CONCATENATE(F1,G1,H1))>0,",",""))

This formula checks the value in column A against the values in B, C and D. If there are no matches, it will return the value in column A. Then it checks B, C and D for data. If one of those columns has a value, it will add a comma after the value in column A.  The formulas in columns F, G and H are somewhat similar. The results are seen in column I which uses the CONCATENATE function. Columns E-H are hidden.

Flyster
EngineerCommented:
You can also try this UDF

Function sansdups(rng)
sansdups = ""
For Each cel In rng
If WorksheetFunction.CountIf(rng, cel) = 1 Then
sansdups = sansdups & IIf(sansdups = "", "", ",") & cel
End If
Next cel
End Function

with the formula
=sansdups(A2:D2)

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Hello Flyster,

Thank-you for taking the time to create a sample spreadsheet.  Unfortunately, my spreadsheet doesn't allow for the use of hidden columns to do the multiple IFs.  Nesting the IFs would be cumbersome as there are likely to be up to 10 columns.  I am sorry - I should have been more specific about needing the formula (as well as the result) to be in a single cell.

Using Match() in an array, I could see where the unique value were, but got stuck in not knowing how to extract the values.

Once again, thanks for your assistance.  As your suggestion does provide a solution in different circumstances, I will make it as an answer.

Elimishia
Author Commented:
Hello Syed,

Thank-you for your assistance.  Although I was originally looking for a non-VBA solution, the UDF works beautifully. So I shall adopt it as my solution.

Elimishia
Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for elimishia's comment #a40695306

for the following reason:

It helped that I have some knowledge of VBA. However, the code was simple and it worked.
Author Commented:
The solution is straight forward and would work well on spreadsheets with free columns available to hold the various formulae.
EngineerCommented:
You can also combine the helper columns into a long formula if you cannot accommodate the helper columns

=IFERROR(LEFT(IF(COUNTIF(\$A2:\$D2,A2)=1,A2&",","")&IF(COUNTIF(\$A2:\$D2,B2)=1,B2&",","")&IF(COUNTIF(\$A2:\$D2,C2)=1,C2&",","")&IF(COUNTIF(\$A2:\$D2,D2)=1,D2&",",""),LEN(IF(COUNTIF(\$A2:\$D2,A2)=1,A2&",","")&IF(COUNTIF(\$A2:\$D2,B2)=1,B2&",","")&IF(COUNTIF(\$A2:\$D2,C2)=1,C2&",","")&IF(COUNTIF(\$A2:\$D2,D2)=1,D2&",",""))-1),"")
Author Commented:
Hello Syed,

Thank-you for this alternative suggestion.  It too works very well.

I have tried to mark both your suggestions as ANSWERS, without success.
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.