return non blank values in a row with additional logic

[Dervos]

Hello Everyone,

Seeking for assistance to help solve scenario below...

I am looking for a formula which would return a non-blank value in a row and cover some additional logic:

1) if the row has one value (i.e. ROW 2 / B2) return that value
2) if the row has two or more values (i.e. ROW3 / C3&D3) return specific text
3) if the row has no values (i.e. ROW4) return specific text (2)

*Please note that the actual cells are filled with formulas returning string values in a General format.



Appreciate your help!

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this, copied down, does what you want.

Excel Workbook
	A	B	C	D	E
1
2	X1	yellow			yellow
3	X2		red	yellow	Error
4	X3				No results
Check

BTW, for the future, look at the link in my signature block below for ways to post sample data that we can actually copy/paste from to test.

 

[Dervos]

Peter,

Thanks for the tip!

Unfortunately, the suggested formula is returning "error" value across all the rows... I think this may be due to the cell format which I'll try to explain.

I am using below formulas to get the outputs (yellow/red/...) in Columns B/C/D:

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

1​

[/TD]
[TD][/TD]
[TD]color1[/TD]
[TD]color2[/TD]
[TD]color3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

2​

[/TD]
[TD]text : yellow x[/TD]
[TD]=IF((SUMPRODUCT(--ISNUMBER(SEARCH(Table73[[#All],[yellow]], A2)))>0)=TRUE,Table73[[#Headers],[yellow]], "")[/TD]
[TD]=IF((SUMPRODUCT(--ISNUMBER(SEARCH(Table74[[#All],[red]], A2)))>0)=TRUE,Table74[[#Headers],[red]], "")[/TD]
[TD]=IF((SUMPRODUCT(--ISNUMBER(SEARCH(Table76[[#All],[blue]], A2)))>0)=TRUE,Table76[[#Headers],[blue]], "")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

3​

[/TD]
[TD]text : red yellow[/TD]
[TD]=IF((SUMPRODUCT(--ISNUMBER(SEARCH(Table73[[#All],[yellow]], A3)))>0)=TRUE,Table73[[#Headers],[yellow]], "")[/TD]
[TD]=IF((SUMPRODUCT(--ISNUMBER(SEARCH(Table74[[#All],[red]], A3)))>0)=TRUE,Table74[[#Headers],[red]], "")[/TD]
[TD]=IF((SUMPRODUCT(--ISNUMBER(SEARCH(Table76[[#All],[blue]], A3)))>0)=TRUE,Table76[[#Headers],[blue]], "")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

4​

[/TD]
[TD]text : empty[/TD]
[TD]=IF((SUMPRODUCT(--ISNUMBER(SEARCH(Table73[[#All],[yellow]], A4)))>0)=TRUE,Table73[[#Headers],[yellow]], "")[/TD]
[TD]=IF((SUMPRODUCT(--ISNUMBER(SEARCH(Table74[[#All],[red]], A4)))>0)=TRUE,Table74[[#Headers],[red]], "")[/TD]
[TD]=IF((SUMPRODUCT(--ISNUMBER(SEARCH(Table76[[#All],[blue]], A4)))>0)=TRUE,Table76[[#Headers],[blue]], "")[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Actual Outputs after calculations (Columns B/C/D):

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

1​

[/TD]
[TD][/TD]
[TD]color1[/TD]
[TD]color2[/TD]
[TD]color3[/TD]
[TD]Expected Output[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

2​

[/TD]
[TD]text : yellow x[/TD]
[TD]yellow[/TD]
[TD][/TD]
[TD][/TD]
[TD]yellow[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

3​

[/TD]
[TD]text : red yellow[/TD]
[TD]yellow[/TD]
[TD]red[/TD]
[TD][/TD]
[TD]Error[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

4​

[/TD]
[TD]text : empty[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]No result[/TD]
[/TR]
</tbody>[/TABLE]

Now using the output (Columns B/C/D) from the table above, how do I get Column E values:

1) if the row has one value (i.e. ROW 2 / B2) return that value
2) if the row has two or more values (i.e. ROW3 / C3&D3) return specific text
3) if the row has no values (i.e. ROW4) return specific text (2)

Appreciate your help!

 

[Peter_SSs]

I am using ... formulas to get the outputs (yellow/red/...) in Columns B/C/D

OK, that does make a difference.

So now the question is what version of Excel are you using? Do you have the CONCAT function?

E2: =IF(COUNTIF(B2:D2,"?*")=0,"No results",IF(COUNTIF(B2:D2,"?*")=1,CONCAT(B2:D2),"Error"))

 

[Dervos]

I am using Office 365 and the CONCAT function is working perfectly!

Thank you!!!

 

[Peter_SSs]

I am using Office 365 and the CONCAT function is working perfectly!

Thank you!!!

Ah, great!
You're welcome.