Data with Different score

sksanjeev786

Active Member
Joined
Aug 5, 2020
Messages
346
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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



Thanks in Advance
Regards,
Sanjeev
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,357
Office Version
  1. 2013
Platform
  1. Windows
@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.
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Aug 5, 2020
Messages
346
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
@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
Joined
Aug 5, 2020
Messages
346
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Aug 5, 2020
Messages
346
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad it helped

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

sksanjeev786

Active Member
Joined
Aug 5, 2020
Messages
346
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
You're welcome, glad it helped

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
Joined
May 28, 2005
Messages
50,601
Office Version
  1. 365
Platform
  1. Windows
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&")"))
 

Forum statistics

Threads
1,141,282
Messages
5,705,483
Members
421,398
Latest member
Rahat Anwar

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top