Excel, Create column stating if row is equal to certain values-CodePudding

There is an extract of my table below,

ID	1	2	3	4	5	6	7	8
252708	Phone	Phone	Phone	Phone	Phone	Phone
252252	Phone
253022	Phone
253080	Phone	Phone	Phone	Email	Phone
253228	Phone	Phone	Phone	Phone	Phone
253282	Phone
256200	Phone
256279	Phone	Phone	Email
256703	Phone
236457	Email
257560	Phone	Phone	Phone	Email	Phone	Phone	Phone	Phone

I want to create an additional column that states whether it's row is made up of Just Phone, Phone and Email, or Just Email. To look like the below,

Status
Just Phone
Just Phone
Just Phone
Phone and Email
Just Phone
Just Phone
Just Phone
Phone and Email
Just Phone
Just Email
Phone and Email

I've tried lots of variations of the if function, but my main issues is the blanks in some of the rows. Thanks in advance

CodePudding user response：

Try below formula-

=TEXTJOIN(" and ",TRUE,UNIQUE(TRANSPOSE(B2:I2)))

If your version of excel supports LAMBDA() function then can try below formula for one go.

=BYROW(B2:I12,LAMBDA(x,TEXTJOIN(" and ",TRUE,UNIQUE(TRANSPOSE(x)))))