Data with Different score

[sksanjeev786]

Hi Team,

I have a Wave 2 and Wave 1 data and need different only if we have any letter in Column A and B in Column D for example i have kept data on Column F.

FS1.xlsx
	A	B	C	D	E	F
1	WAVE 1 (A)	WAVE 2 (B)		Diff.		Difference Between (Wave 2 - Wave 1) (Need in this format)
2	53B	14		#VALUE!		14 (-39)
3	22	24A		#VALUE!		24 (+2)
4	21B	15		#VALUE!		15 (-6)
5	10	11A		#VALUE!		11(+1)
6	43	42		-1
7	7	6		-1
8	18	43A		#VALUE!		43 (+25)
Sheet4

Cell Formulas
Range	Formula
D2:D8	D2	=B2-A2

Thanks in Advance
Regards,
Sanjeev

 

[Snakehips]

@sksanjeev786
I have assumed just one letter at the end of the number?
If so then maybe the below is a possible solution. I have used two 'helper' columns to prevent the formula in D becoming overly long.

MRXLMAY21.xlsm
	A	B	C	D	E	F
1	WAVE 1 (A)	WAVE 2 (B)		Num1	NUM2	Diff.
2	53B	14		53	14	14 (-39)
3	22	24A		22	24	24 (+2)
4	21B	15		21	15	15 (-6)
5	10	11A		10	11	11 (+1)
6	43	42		43	42
7	7	6		7	6
8	18	43A		18	43	43 (+25)
Recipe

Cell Formulas
Range	Formula
D2:E8	D2	=IF(CODE(RIGHT(A2,1))>57,LEFT(A2,LEN(A2)-1),A2)
F2:F8	F2	=IF(AND(A2=D2,B2=E2),"",E2&" ("&IF(D2>E2,"","+")&E2-D2&")")

Hope that helps.

 

[JackDanIce]

Try:

Other.xlsm
	A	B	C	D	E	F	G
1	WAVE 1 (A)	WAVE 2 (B)		Diff.	Difference Between (Wave 2 - Wave 1) (Need in this format)		Suggestion
2	53B	14		#VALUE!	14 (-39)		14(-39)
3	22	24A		#VALUE!	24 (+2)		24(+2)
4	21B	15		#VALUE!	15 (-6)		15(-6)
5	10	11A		#VALUE!	11(+1)		11(+1)
6	43	42		-1
7	7	6		-1
8	18	43A		#VALUE!	43 (+25)		43(+25)
Sheet2

Formula in G2 dragged down:

=IF(ISNUMBER(A2+B2),"",LET(max_len,MAX(LEN($A$1:$B$8)),A,TEXTJOIN("",1,IFERROR(MID(A2,SEQUENCE(max_len),1)+0,"")),B,TEXTJOIN("",1,IFERROR(MID(B2,SEQUENCE(max_len),1)+0,"")),B&"("&IF(B>A,"+","")&B-A&")"))

 

[sksanjeev786]

@sksanjeev786
I have assumed just one letter at the end of the number?
If so then maybe the below is a possible solution. I have used two 'helper' columns to prevent the formula in D becoming overly long.

MRXLMAY21.xlsm
	A	B	C	D	E	F
1	WAVE 1 (A)	WAVE 2 (B)		Num1	NUM2	Diff.
2	53B	14		53	14	14 (-39)
3	22	24A		22	24	24 (+2)
4	21B	15		21	15	15 (-6)
5	10	11A		10	11	11 (+1)
6	43	42		43	42
7	7	6		7	6
8	18	43A		18	43	43 (+25)
Recipe

Cell Formulas
Range	Formula
D2:E8	D2	=IF(CODE(RIGHT(A2,1))>57,LEFT(A2,LEN(A2)-1),A2)
F2:F8	F2	=IF(AND(A2=D2,B2=E2),"",E2&" ("&IF(D2>E2,"","+")&E2-D2&")")

Hope that helps.

Thanks you so much Sir for your help on this.

As per your note some time i may have more letter at the end for example in Cell A2(53 BCDE)

Let us know Sir if we can get the solution for that as well

Regards,
Sanjeev

 

[sksanjeev786]

Try:

Other.xlsm
	A	B	C	D	E	F	G
1	WAVE 1 (A)	WAVE 2 (B)		Diff.	Difference Between (Wave 2 - Wave 1) (Need in this format)		Suggestion
2	53B	14		#VALUE!	14 (-39)		14(-39)
3	22	24A		#VALUE!	24 (+2)		24(+2)
4	21B	15		#VALUE!	15 (-6)		15(-6)
5	10	11A		#VALUE!	11(+1)		11(+1)
6	43	42		-1
7	7	6		-1
8	18	43A		#VALUE!	43 (+25)		43(+25)
Sheet2

Formula in G2 dragged down:

=IF(ISNUMBER(A2+B2),"",LET(max_len,MAX(LEN($A$1:$B$8)),A,TEXTJOIN("",1,IFERROR(MID(A2,SEQUENCE(max_len),1)+0,"")),B,TEXTJOIN("",1,IFERROR(MID(B2,SEQUENCE(max_len),1)+0,"")),B&"("&IF(B>A,"+","")&B-A&")"))

Thanks you sir for looking on it. it is working well if we have more letter . but iam very curious to know why you have use "max_len".
To be frank i want to understand your formula is there any way to understand you formula like step by step.

Thanks once again
Sanjeev

 

[JackDanIce]

To break out the formula, I've used VBA wrapping, but below is an Excel cell formula:

=IF(ISNUMBER(A2+B2),"",

LET  (  max_len,  MAX(LEN($A$1:$B$8)),
          A,              TEXTJOIN("",TRUE,IFERROR(MID(A2,SEQUENCE(max_len),1)+0,"")),
          B,              TEXTJOIN("",TRUE,IFERROR(MID(B2,SEQUENCE(max_len),1)+0,"")),

B&"("&IF(B>A,"+","")&B-A&")"))

IF A AND B are both numbers Then return ""
ELSE
Calculate max length of strings in entire range and name this value "max_len"
Calculate remove non-numbers out of A2 and name this value "A"
Calculate remove non-numbers out of B2 and name this value "B"
Create Output String as: B & "( IF B>A,"+","") & B-A & ")"

Using LET reduces repeat calculations within a formula, helping to reduce formula length and calculate faster.
If I didn't use LET, I'd have to calculate the max length of all strings 3 times within each formula used, same for if A is a string or number and if B is a string or number

 

[sksanjeev786]

To break out the formula, I've used VBA wrapping, but below is an Excel cell formula:

=IF(ISNUMBER(A2+B2),"",

LET  (  max_len,  MAX(LEN($A$1:$B$8)),
          A,              TEXTJOIN("",TRUE,IFERROR(MID(A2,SEQUENCE(max_len),1)+0,"")),
          B,              TEXTJOIN("",TRUE,IFERROR(MID(B2,SEQUENCE(max_len),1)+0,"")),

B&"("&IF(B>A,"+","")&B-A&")"))

IF A AND B are both numbers Then return ""
ELSE
Calculate max length of strings in entire range and name this value "max_len"
Calculate remove non-numbers out of A2 and name this value "A"
Calculate remove non-numbers out of B2 and name this value "B"
Create Output String as: B & "( IF B>A,"+","") & B-A & ")"

Using LET reduces repeat calculations within a formula, helping to reduce formula length and calculate faster.
If I didn't use LET, I'd have to calculate the max length of all strings 3 times within each formula used, same for if A is a string or number and if B is a string or number

Thanks you so much sir for you time and explaining step by step

You are Awesome

 

[JackDanIce]

You're welcome, glad it helped

Also, formula to remove non-numeric characters from a cell is modified from here:

How to Remove Non Numeric Characters From Cells in Excel

To remove non numeric characters from an alphanumeric string in Excel, we use the new TEXTJOIN function. Strip of non numeric characters from a string can help us clean our data for better data analysis. So here's how you do it.

www.exceltip.com

 

[sksanjeev786]

You're welcome, glad it helped

Also, formula to remove non-numeric characters from a cell is modified from here:

How to Remove Non Numeric Characters From Cells in Excel

To remove non numeric characters from an alphanumeric string in Excel, we use the new TEXTJOIN function. Strip of non numeric characters from a string can help us clean our data for better data analysis. So here's how you do it.

www.exceltip.com

One more thing sir if it is possible

Can we get different score in color like "(-39) in Red color " (all Negative score) and "(+2) in Green color" (All positive Score).

I know it is bit difficult but still just wanted to check you on this

Thanks
Sanjeev

 

[Peter_SSs]

It seems like you are happy for a Microsoft 365 solution and have the LET function, so another option might be this.
It does assume that columns A & B have no more than 9 leading digits.

21 06 03.xlsm
	A	B			E	F
1	WAVE 1 (A)	WAVE 2 (B)				Result
2	53B	14				14 (-39)
3	22	24A				24 (+2)
4	21B	15				15 (-6)
5	10	11A				11 (+1)
6	43	42
7	7	6
8	18	43A				43 (+25)
Difference

Cell Formulas
Range	Formula
F2:F8	F2	=IF(COUNT(A2:B2)=2,"",LET(s,SEQUENCE(9),a,LOOKUP(9^99,LEFT(A2,s)+0),b,LOOKUP(9^99,LEFT(B2,s)+0),r,b-a,b&" ("&IF(r>0,"+","")&r&")"))