Need to remove Arrow againts -,* and #value

[sksanjeev786]

Hi Team,

I need a output as column G as i am comparing row 4 B vs L in the data

so if I have *,- or Value I don't want any arrow please see the example

book1
	A	B	C	D	E	F	G
1					▲
2					▼
3		Wave 1	Wave 2
4		B	L
5		* If we have "Value, *,- then i don’t want any arrow on Column E output data in G Column
6
7							Output
8
9	53%	#VALUE!	53 OPQRSTB*		53% ▲	Incorrect	53%
10	45%	*	45 ORTB*		45% ▲	Incorrect	45%
11	49%	61 ADEF*	49 OPRS*		49%		49%
12	63%	-	63 NOPQRSTB*		63% ▲	Incorrect	63%
13	61%	70 ADEFL*	61 KOPQRST*		61% ▼		61% ▼
14	47%	59 ADEF*	47 OQRT*		47%		47%
15	50%	68 ADEFL	#VALUE!		50% ▼	Incorrect	50%
16	39%	41 ADL*	*		39% ▼	Incorrect	39%
17	72%	55 ADEF	72 TB*		72% ▲		72% ▲
18	80%	80 L	40 O		80% ▼		80% ▼
Sheet1

Cell Formulas
Range	Formula
E9:E18	E9	=A9*100&"%"&" "&IFERROR(IF(FIND($B$4,C9),E$1),"")&IFERROR(IF(FIND($C$4,B9),E$2),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B8:C1048576,C7,B1:C6	Cell	contains an error	text	NO
B8:C1048576,C7,B1:C6	Cell Value	contains "#VALUE!"	text	NO
B3:C3	Cell Value	duplicates	text	NO

 

[sksanjeev786]

Hi Team,

I need a output as column G as i am comparing row 4 B vs L in the data

so if I have *,- or Value I don't want any arrow please see the example

book1
	A	B	C	D	E	F	G
1					▲
2					▼
3		Wave 1	Wave 2
4		B	L
5		* If we have "Value, *,- then i don’t want any arrow on Column E output data in G Column
6
7							Output
8
9	53%	#VALUE!	53 OPQRSTB*		53% ▲	Incorrect	53%
10	45%	*	45 ORTB*		45% ▲	Incorrect	45%
11	49%	61 ADEF*	49 OPRS*		49%		49%
12	63%	-	63 NOPQRSTB*		63% ▲	Incorrect	63%
13	61%	70 ADEFL*	61 KOPQRST*		61% ▼		61% ▼
14	47%	59 ADEF*	47 OQRT*		47%		47%
15	50%	68 ADEFL	#VALUE!		50% ▼	Incorrect	50%
16	39%	41 ADL*	*		39% ▼	Incorrect	39%
17	72%	55 ADEF	72 TB*		72% ▲		72% ▲
18	80%	80 L	40 O		80% ▼		80% ▼
Sheet1

Cell Formulas
Range	Formula
E9:E18	E9	=A9*100&"%"&" "&IFERROR(IF(FIND($B$4,C9),E$1),"")&IFERROR(IF(FIND($C$4,B9),E$2),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B8:C1048576,C7,B1:C6	Cell	contains an error	text	NO
B8:C1048576,C7,B1:C6	Cell Value	contains "#VALUE!"	text	NO
B3:C3	Cell Value	duplicates	text	NO

Hi Team,

Any update on above things...!!

 

[Peter_SSs]

I don't understand the logic. Please spell it out in ore detail (with examples).

 

[sksanjeev786]

I don't understand the logic. Please spell it out in ore detail (with examples).

Hi Peter,

Thank you for looking into it..

So we have a "B" and "L" letter in Row number 4 so

If Cell B4 letter "B" comes in column (C) C9 it will be up arrow
If Cell C4 letter "L" comes in Column(B) B9 it will be a down arrow.

But if w have *,- or Value then there should be no arrow..

My output is in Column G

Where we can see the Arrow

Cell B4 "B" is coming in C17 "72TB" so B is coming so it will be up arrow
Cell C4 "L" is coming in B18 "80L" so L is coming so it will be down arrow
But if we have But if w have *,- or Value then there should be no arrow..

Regards
Sanjeev

 

[Peter_SSs]

Thanks for the further information.

if we have But if w have *,- or Value

1. Does value actually mean #VALUE!?
   
   
2. In row 9, "B" is found in C9. Your desired output has no arrow. Is that because B9 has #VALUE or because C9 includes an asterisk at the end or some other reason?
   
   
3. Would it ever be possible to have a "B" in col C and an "L" in col B in the same row? If so, what happens?

 

[sksanjeev786]

Thanks for the further information.

1. Does value actually mean #VALUE!?
   
   
2. In row 9, "B" is found in C9. Your desired output has no arrow. Is that because B9 has #VALUE or because C9 includes an asterisk at the end or some other reason?
   
   
3. Would it ever be possible to have a "B" in col C and an "L" in col B in the same row? If so, what happens?

Hi Peter,

1. Yes, Value means #Value
2.yes because B9 has #value due to that I don't want arrow if instead of #value if we have "C9" data "23 L" (assume) then it will be an up arrow.
3. Yes, Always we will get "B" in col C and "L" in col B so we will get an arrow (output is available in Col G)

 

[Peter_SSs]

3. Yes, Always we will get "B" in col C and "L" in col B so we will get an arrow (output is available in Col G)

I'm not sure you interpreted my question correctly. I was asking about those two things on the same row. See my example in row 19 below. I was just wondering if both arrows are possible on the same row.

Anyway, see if this helps.

sksanjeev786_1.xlsm
	A	B	C	D	E	F	G
1					▲
2					▼
3		Wave 1	Wave 2
4		B	L
5
6
7							Output
8
9	53%	#VALUE!	53 OPQRSTB*		53% ▲	Incorrect	53%
10	45%	*	45 ORTB*		45% ▲	Incorrect	45%
11	49%	61 ADEF*	49 OPRS*		49%		49%
12	63%	-	63 NOPQRSTB*		63% ▲	Incorrect	63%
13	61%	70 ADEFL*	61 KOPQRST*		61% ▼		61% ▼
14	47%	59 ADEF*	47 OQRT*		47%		47%
15	50%	68 ADEFL	#VALUE!		50% ▼	Incorrect	50%
16	39%	41 ADL*	*		39% ▼	Incorrect	39%
17	72%	55 ADEF	72 TB*		72% ▲		72% ▲
18	80%	80 L	40 O		80% ▼		80% ▼
19	56%	71 DGL	50 AB		56% ▲▼		56% ▲▼
Sheet1

Cell Formulas
Range	Formula
E9:E19	E9	=A9*100&"%"&" "&IFERROR(IF(FIND($B$4,C9),E$1),"")&IFERROR(IF(FIND($C$4,B9),E$2),"")
G9:G19	G9	=LET(N,ISNUMBER(LEFT(B9,1)*LEFT(C9,1)),TRIM(A9*100&"% "&IF(N*IFERROR(FIND(B$4,C9),0),E$1,"")&IF(N*IFERROR(FIND(C$4,B9),0),E$2,"")))

 

[sksanjeev786]

I'm not sure you interpreted my question correctly. I was asking about those two things on the same row. See my example in row 19 below. I was just wondering if both arrows are possible on the same row.

Anyway, see if this helps.

sksanjeev786_1.xlsm
	A	B	C	D	E	F	G
1					▲
2					▼
3		Wave 1	Wave 2
4		B	L
5
6
7							Output
8
9	53%	#VALUE!	53 OPQRSTB*		53% ▲	Incorrect	53%
10	45%	*	45 ORTB*		45% ▲	Incorrect	45%
11	49%	61 ADEF*	49 OPRS*		49%		49%
12	63%	-	63 NOPQRSTB*		63% ▲	Incorrect	63%
13	61%	70 ADEFL*	61 KOPQRST*		61% ▼		61% ▼
14	47%	59 ADEF*	47 OQRT*		47%		47%
15	50%	68 ADEFL	#VALUE!		50% ▼	Incorrect	50%
16	39%	41 ADL*	*		39% ▼	Incorrect	39%
17	72%	55 ADEF	72 TB*		72% ▲		72% ▲
18	80%	80 L	40 O		80% ▼		80% ▼
19	56%	71 DGL	50 AB		56% ▲▼		56% ▲▼
Sheet1

Cell Formulas
Range	Formula
E9:E19	E9	=A9*100&"%"&" "&IFERROR(IF(FIND($B$4,C9),E$1),"")&IFERROR(IF(FIND($C$4,B9),E$2),"")
G9:G19	G9	=LET(N,ISNUMBER(LEFT(B9,1)*LEFT(C9,1)),TRIM(A9*100&"% "&IF(N*IFERROR(FIND(B$4,C9),0),E$1,"")&IF(N*IFERROR(FIND(C$4,B9),0),E$2,"")))

Thank you so much, Peter

That works for me and yes last row data letter was typo from my end
I have removed the letter and now it is coming correct for me

0.56

71 DG

50 AB

56% ▲

56% ▲

Once again thank youuuuuuuu so much... Have a nice evening ahead......

 

[Peter_SSs]

Thank you so much, Peter

That works for me

You're welcome. Glad it worked for you. Thanks for the confirmation.

last row data letter was typo from my end

No typo from you - The last row was my sample data to see if that sort of data was possible.

 

[sksanjeev786]

You're welcome. Glad it worked for you. Thanks for the confirmation.


No typo from you - The last row was my sample data to see if that sort of data was possible.

np... thank you once again, Peter