if function problem need solving

[determinedtoexceed]

Dear Pros,

Please help me with 1 function that can determine whether there is any gap by item name.

example1: For itemA, "Cell E1" will show "No Gap" if b1,c1,d1 all are"No Gap"

example2: For itemA, "Cell E1" will show "Gap to 1st" when b1 ="Gap to 1st" while C1 and D1 ="No Gap"

example3: For itemA, "Cell E1" will show " Gap to 1st&Gap to 3rd" when B1=Gap to 1st, C1=No Gap, D1=Gap to 3rd

Thank you very much for your time~

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]itemA[/TD]
[TD]Gap to 1st[/TD]
[TD]No Gap[/TD]
[TD]Gap to 3rd[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]itemB[/TD]
[TD]No Gap[/TD]
[TD]Gap to 2nd[/TD]
[TD]Gap to 3rd[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Re: if funcction problem need solving

Will you only have these three columns (B, C, and D), or is this a simplified example and might you have more than 3 columns?

 

[Joe4]

Re: if funcction problem need solving

If there are only three, try this formula:

=IF(COUNTIF(B1:D1,"No Gap")=3,"No Gap",SUBSTITUTE(TRIM(IF(B1<>"No Gap",B1&" ","")&IF(C1<>"No Gap",C1&" ","")&IF(D1<>"No Gap",D1,""))," G","&G"))

 

[determinedtoexceed]

Re: if funcction problem need solving

If there are only three, try this formula:

=IF(COUNTIF(B1:D1,"No Gap")=3,"No Gap",SUBSTITUTE(TRIM(IF(B1<>"No Gap",B1&" ","")&IF(C1<>"No Gap",C1&" ","")&IF(D1<>"No Gap",D1,""))," G","&G"))

If there are only three, try this formula:

=IF(COUNTIF(B1:D1,"No Gap")=3,"No Gap",SUBSTITUTE(TRIM(IF(B1<>"No Gap",B1&" ","")&IF(C1<>"No Gap",C1&" ","")&IF(D1<>"No Gap",D1,""))," G","&G"))

Dear Joe4,

You are correct, it is indeed a simplified example, I do have more than 3 columns and when i apply the function base on methodology you provided, it did not work.

sample B1 actually locate on U10,
sample C1 actually locate on AA10
sample D1 actuly located on AG10

sample E1 result cell actually located on Q10

"NO GAP""GAP to xx" are also returned if functions


Function show blank when
Q10=IF(COUNTIF(U:AG,"NO GAP")=13,"NO GAP",SUBSTITUTE(TRIM(IF(U10<>"NO GAP",U10&" ","")&IF(AA10<>"NO GAP",AA10&" ","")&IF(AG10<>"NO GAP",AG10,""))," G","&G"))

Please help~ and thank you very much.

 

[salim hasan]

Re: if funcction problem need solving

Can you Try My Formula

=INDEX({"Gap to 1st & Gap to 3rd"," Gap to 2nd  & Gap to 3rd","Gap to 1st & Gap to 2nd ","No Gap "},MATCH(B1&" "&C1&" "&D1,{"Gap to 1st No Gap Gap to 3rd","No Gap Gap to 2nd Gap to 3rd","Gap to 1st Gap to 2nd No Gap","No Gap No Gap No Gap"},0))

	A	B	C	D	E
ItemA	Gap to 1st	No Gap	Gap to 3rd
ItemB	No Gap	Gap to 2nd	Gap to 3rd
ItemC	Gap to 1st	Gap to 2nd	No Gap
ItemD	No Gap	No Gap	No Gap

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]Gap to 1st & Gap to 3rd[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "] Gap to 2nd & Gap to 3rd[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]Gap to 1st & Gap to 2nd [/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]No Gap [/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]=INDEX({"Gap to 1st & Gap to 3rd"," Gap to 2nd & Gap to 3rd","Gap to 1st & Gap to 2nd ","No Gap "},MATCH(B1&" "&C1&" "&D1,{"Gap to 1st No Gap Gap to 3rd","No Gap Gap to 2nd Gap to 3rd","Gap to 1st Gap to 2nd No Gap","No Gap No Gap No Gap"},0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[determinedtoexceed]

Re: if funcction problem need solving

Can you Try My Formula

=INDEX({"Gap to 1st & Gap to 3rd"," Gap to 2nd  & Gap to 3rd","Gap to 1st & Gap to 2nd ","No Gap "},MATCH(B1&" "&C1&" "&D1,{"Gap to 1st No Gap Gap to 3rd","No Gap Gap to 2nd Gap to 3rd","Gap to 1st Gap to 2nd No Gap","No Gap No Gap No Gap"},0))

	A	B	C	D	E
ItemA	Gap to 1st	No Gap	Gap to 3rd	Gap to 1st & Gap to 3rd
ItemB	No Gap	Gap to 2nd	Gap to 3rd	Gap to 2nd & Gap to 3rd
ItemC	Gap to 1st	Gap to 2nd	No Gap	Gap to 1st & Gap to 2nd
ItemD	No Gap	No Gap	No Gap	No Gap

<tbody>
[TD="align: center"]1[/TD]

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

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

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

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]E1[/TH]
[TD="align: left"]=INDEX({"Gap to 1st & Gap to 3rd"," Gap to 2nd & Gap to 3rd","Gap to 1st & Gap to 2nd ","No Gap "},MATCH(B1&" "&C1&" "&D1,{"Gap to 1st No Gap Gap to 3rd","No Gap Gap to 2nd Gap to 3rd","Gap to 1st Gap to 2nd No Gap","No Gap No Gap No Gap"},0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Dear Salim, it will return error when C1 return Gap to 2nd

I think there are more than 10 outcomes for E1 when b,c,d are triggered

 

[Peter_SSs]

Re: if funcction problem need solving

If this doesn't work, could we please have a few sets of more realistic sample dummy data, including where that data is located & the expected results
This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
10	Gap to 1st & Gap to 3rd				Gap to 1st						No Gap						Gap to 3rd
11	No Gap				No Gap						No Gap						No Gap
12	Gap to 1st & Gap to 2nd & Gap to 3rd				Gap to 1st						Gap to 2nd						Gap to 3rd
13	Gap to 2nd				No Gap						Gap to 2nd						No Gap
Gap

 

[Joe4]

Re: if funcction problem need solving

You are correct, it is indeed a simplified example, I do have more than 3 columns and when i apply the function base on methodology you provided, it did not work.

That is the danger of oversimplifying things. You get a reply that answers the exact question you asked, but doesn't really solve your problem!
For that reason, it is best NOT to oversimplify things, and give us an accurate depiction of the data you are working with.

I see that Peter has provided you with a solution, so hopefully that will work for you.

 

[determinedtoexceed]

Re: if funcction problem need solving

Thank you all for taking the time and helping me out to solve my problem.

All your effort is deeply appreciated.

 

[Peter_SSs]

Re: if funcction problem need solving

Thank you all for taking the time and helping me out to solve my problem.

All your effort is deeply appreciated.

You're welcome. Thanks for the follow-up.