Excel function to provide non-empty and unique results from a row-CodePudding

I'm trying to write a formula (would need to fit in one cell) that will check cells in the row for non-empty cells, and then also check if those values are the same. If they are the same, just return the result. If they're different, return a concatenated list of the results.

I started with a basic formula to just return results if the cells are not empty, but it doesn't consider where cells are the same or different yet.

=IFNA(IFS(A2<>"",A2,B2<>"",B2,C2<>"",C2,D2<>"",D2),"No Number Identified")

Example dataset would be:

24 - - -
- - 36 36
- 12 11 -
- - - -
9 - - 9

I tried this manually, but quickly gave up due to all the different conditions (1 AND 2 AND 3 match, 1 AND 2 AND 4 match, etc.). Is there a quicker way to do this?

CodePudding user response：

With office 365 we can use TEXTJOIN and UNIQUE:

=TEXTJOIN(", ",TRUE,UNIQUE(A1:D1,TRUE))