IF condition

[Sahitya]

I have to change the name as per the group in 'Type' column. it is working fine if it is represented as single name, but not with multiple names.

Eg., For Apple, Orange, Watermelon, it should come as Medium, Soft

Formula used in 'Type' Column -

=IF(OR([@Names]="Orange",[@Names]="Watermelon",[@Names]="Banana"),"Soft",IF([@Names]="Apple","Medium",
IF([@Names]="Pineapple","Hard",
"Check Names")))

[TABLE="class: grid, width: 316"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Names[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Soft[/TD]
[/TR]
[TR]
[TD]Pineapple[/TD]
[TD]Hard[/TD]
[/TR]
[TR]
[TD]Apple, Orange, Watermelon[/TD]
[TD]Check Names[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Soft[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]Soft[/TD]
[/TR]
[TR]
[TD]Watermelon, Pineapple[/TD]
[TD]Check Names[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

You need to have a separate table which shows the qualification per item.

 

[Sahitya]

i will be taking dump from sharepoint and which has fruit names which are true or false. With your help i used textjoin to concatenate the 'Names' column.

Problem here is everytime i refresh the dump, data keeps varying and also i will be creating some more validations and from this sheet pivot will be called.

is there any other which will help without having a seperate table


[TABLE="class: grid, width: 636"]
<colgroup><col span="5"><col><col></colgroup><tbody>[TR]
[TD]Apple[/TD]
[TD]Orange[/TD]
[TD]Banana[/TD]
[TD]Watermelon[/TD]
[TD]Pineapple[/TD]
[TD]Names[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Orange[/TD]
[TD]Soft[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD]Pineapple[/TD]
[TD]Hard[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Apple, Orange, Watermelon[/TD]
[TD]Check Names[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Orange[/TD]
[TD]Soft[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Banana[/TD]
[TD]Soft[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]TRUE[/TD]
[TD]Watermelon, Pineapple[/TD]
[TD]Check Names[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Apple	Orange	Banana	Watermelon	Pineapple	Names	Type				item	type
2	FALSE	TRUE	FALSE	FALSE	FALSE	Orange	soft				apple	medium
3	FALSE	FALSE	FALSE	FALSE	TRUE	Pineapple	hard				orange	soft
4	TRUE	TRUE	FALSE	TRUE	FALSE	Apple, Orange, Watermelon	medium, soft, soft				banana	soft
5	FALSE	TRUE	FALSE	FALSE	FALSE	Orange	soft				watermelon	soft
6	FALSE	FALSE	TRUE	FALSE	FALSE	Banana	soft				pineapple	hard
7	FALSE	FALSE	FALSE	TRUE	TRUE	Watermelon, Pineapple	soft, hard
Sheet1

In G2 control+shift+enter, not just enter, and copy down:

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(" "&$K$2:$K$6&","," "&$F2&",")),$L$2:$L$6,""))

 

[shaowu459]

I will show you a simple example which can tell you whether text in one cell contains something or not:

IF(countif([@Names],"*Apple*")>0,"Medium"

if [@Names] contains "apple", COUNTIF returns 1, otherwise COUNTIF returns 0.

 

[Sahitya]

Thank you Very much, it solves the problem.

Instead of 'medium, soft, soft' can be get as 'medium, soft' without duplicating the type.

	A	B	C	D	E	F	G	H	I	J	K	L
Apple	Orange	Banana	Watermelon	Pineapple	Names	Type	item	type
Orange	soft	apple	medium
Pineapple	hard	orange	soft
Apple, Orange, Watermelon	medium, soft, soft	banana	soft
Orange	soft	watermelon	soft
Banana	soft	pineapple	hard
Watermelon, Pineapple	soft, hard

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]TRUE[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

In G2 control+shift+enter, not just enter, and copy down:

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(" "&$K$2:$K$6&","," "&$F2&",")),$L$2:$L$6,""))

 

[Sahitya]

I will show you a simple example which can tell you whether text in one cell contains something or not:

IF(countif([@Names],"*Apple*")>0,"Medium"

if [@Names] contains "apple", COUNTIF returns 1, otherwise COUNTIF returns 0.

Will ii get concatenated names ?

 

[shaowu459]

To get concatenated names, Textjoin is still need.

 

[Aladin Akyurek]

Thank you Very much, it solves the problem.

Instead of 'medium, soft, soft' can be get as 'medium, soft' without duplicating the type.

In G2 control+shift+enter, not just enter, and copy down:

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(MATCH({"hard","medium","soft"},IF(ISNUMBER(SEARCH(" "&$K$2:$K$6&","," "&$F2&",")),$L$2:$L$6,""),0)),{"hard","medium","soft"},""))

 

[Sahitya]

In G2 control+shift+enter, not just enter, and copy down:

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(MATCH({"hard","medium","soft"},IF(ISNUMBER(SEARCH(" "&$K$2:$K$6&","," "&$F2&",")),$L$2:$L$6,""),0)),{"hard","medium","soft"},""))

You are real saviour for me. Thank you very much Mrexcel.