# Data with Different score

#### sksanjeev786

##### Active Member
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
ABCDEF
1WAVE 1 (A)WAVE 2 (B)Diff.Difference Between (Wave 2 - Wave 1) (Need in this format)
253B14#VALUE!14 (-39)
32224A#VALUE!24 (+2)
421B15#VALUE!15 (-6)
51011A#VALUE!11(+1)
64342-1
776-1
81843A#VALUE!43 (+25)
Sheet4
Cell Formulas
RangeFormula
D2:D8D2=B2-A2

Regards,
Sanjeev

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### Snakehips

##### Well-known Member
@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
ABCDEF
1WAVE 1 (A)WAVE 2 (B)Num1NUM2Diff.
253B14531414 (-39)
32224A222424 (+2)
421B15211515 (-6)
51011A101111 (+1)
643424342
77676
81843A184343 (+25)
Recipe
Cell Formulas
RangeFormula
D2:E8D2=IF(CODE(RIGHT(A2,1))>57,LEFT(A2,LEN(A2)-1),A2)
F2:F8F2=IF(AND(A2=D2,B2=E2),"",E2&" ("&IF(D2>E2,"","+")&E2-D2&")")

Hope that helps.

• sksanjeev786

#### JackDanIce

##### Well-known Member
Try:
Other.xlsm
ABCDEFG
1WAVE 1 (A)WAVE 2 (B)Diff.Difference Between (Wave 2 - Wave 1) (Need in this format)Suggestion
253B14#VALUE!14 (-39)14(-39)
32224A#VALUE!24 (+2)24(+2)
421B15#VALUE!15 (-6)15(-6)
51011A#VALUE!11(+1)11(+1)
64342-1
776-1
81843A#VALUE!43 (+25)43(+25)
Sheet2

Formula in G2 dragged down:
Excel Formula:
``=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

##### Active Member
@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
ABCDEF
1WAVE 1 (A)WAVE 2 (B)Num1NUM2Diff.
253B14531414 (-39)
32224A222424 (+2)
421B15211515 (-6)
51011A101111 (+1)
643424342
77676
81843A184343 (+25)
Recipe
Cell Formulas
RangeFormula
D2:E8D2=IF(CODE(RIGHT(A2,1))>57,LEFT(A2,LEN(A2)-1),A2)
F2:F8F2=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

##### Active Member

Try:
Other.xlsm
ABCDEFG
1WAVE 1 (A)WAVE 2 (B)Diff.Difference Between (Wave 2 - Wave 1) (Need in this format)Suggestion
253B14#VALUE!14 (-39)14(-39)
32224A#VALUE!24 (+2)24(+2)
421B15#VALUE!15 (-6)15(-6)
51011A#VALUE!11(+1)11(+1)
64342-1
776-1
81843A#VALUE!43 (+25)43(+25)
Sheet2

Formula in G2 dragged down:
Excel Formula:
``=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

##### Well-known Member
To break out the formula, I've used VBA wrapping, but below is an Excel cell formula:
VBA Code:
``````=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

#### sksanjeev786

##### Active Member

To break out the formula, I've used VBA wrapping, but below is an Excel cell formula:
VBA Code:
``````=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

#### JackDanIce

##### Well-known Member

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

• sksanjeev786

#### sksanjeev786

##### Active Member

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

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

##### MrExcel MVP, Moderator
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
ABEF
1WAVE 1 (A)WAVE 2 (B)Result
253B1414 (-39)
32224A24 (+2)
421B1515 (-6)
51011A11 (+1)
64342
776
81843A43 (+25)
Difference
Cell Formulas
RangeFormula
F2:F8F2=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&")"))

• sksanjeev786

Replies
14
Views
508
Replies
2
Views
146
Replies
3
Views
256
Replies
9
Views
384
Replies
1
Views
48

### Forum statistics

1,136,762
Messages
5,677,599
Members
419,706
Latest member
lydytunes13 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back