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

 

[sksanjeev786]

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&")"))

Super Sir!!!!!!!!

it work very well

I must say you and your all teams are Super Amazing and Very Talented on every aspect


Just wanted to check as i have posted for Jack as well
"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"

if not work i fix the color manually

Regards,
Sanjeev

 

[Peter_SSs]

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

If you mean that you want the first number as normal and just the part in parentheses in colour (like below) then you cannot do this using a formula in the cell.
The only way would be to do it all with a macro and that would mean that the results would not automatically update if the values in columns A &/or B changed as they do when a formula is used.

 

[sksanjeev786]

If you mean that you want the first number as normal and just the part in parentheses in colour (like below) then you cannot do this using a formula in the cell.
The only way would be to do it all with a macro and that would mean that the results would not automatically update if the values in columns A &/or B changed as they do when a formula is used.

View attachment 39981

yes Sir i need in above format

 

[JackDanIce]

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

Posted without seeing Peter_SSs' reply - same problem, can't be done on a character by character level


Try:

Other.xlsm
	G
1	Suggestion
2	14(-39)
3	24(+2)
4	15(-6)
5	11(+1)
6
7
8	43(+25)
Sheet2

Cell Formulas
Range	Formula
G2:G8	G2	=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&")"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:G8	Expression	=NOT(ISERROR(FIND("-",G2)))	text	NO
G2:G8	Expression	=AND(G2<>"",ISERROR(FIND("-",G2)))	text	NO

 

[Peter_SSs]

if not work i fix the color manually

Then even to do it manually you would first need to Copy & Paste Special values to remove the formula, then you could format part of the result red or blue.
Either that or you have a macro do the whole thing.

 

[JackDanIce]

I'd argue what is the value of making the numbers look like the way you do and now needing a macro and other processes vs getting on with the rest of the work?

How important is this part against the entire reason for this spreadsheet?

 

[sksanjeev786]

Posted without seeing Peter_SSs' reply - same problem, can't be done on a character by character level


Try:

Other.xlsm
	G
1	Suggestion
2	14(-39)
3	24(+2)
4	15(-6)
5	11(+1)
6
7
8	43(+25)
Sheet2

Cell Formulas
Range	Formula
G2:G8	G2	=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&")"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:G8	Expression	=NOT(ISERROR(FIND("-",G2)))	text	NO
G2:G8	Expression	=AND(G2<>"",ISERROR(FIND("-",G2)))	text	NO

Hi Sir
Thanks for looking again on this i need data in below attached format

Thanks in advance
Sanjeev

 

[sksanjeev786]

Then even to do it manually you would first need to Copy & Paste Special values to remove the formula, then you could format part of the result red or blue.
Either that or you have a macro do the whole thing.

Yes you are correct sir

I will copy and past Special the value if we have macro for that that will work for me

Regards,
Sanjeev

 

[sksanjeev786]

I'd argue what is the value of making the numbers look like the way you do and now needing a macro and other processes vs getting on with the rest of the work?

How important is this part against the entire reason for this spreadsheet?

Hello sir i have posted you the data with Attached file

 

[Peter_SSs]

Hello sir i have posted you the data with Attached file

I trust not as that would be a breach of #4 of the Forum Rules

Here is a macro that will calculate the results and format the part in parentheses.
Please check the code for the column that the results should go in. The code current puts them in column F but is easily where where noted in the code.
One thing that you did not make clear is what should happen if the number part of both columns A & B are exactly the same?

Sub Get_Results()
  Dim rw As Range
  Dim a As Long, b As Long, P As Long, L As Long
  
  Application.ScreenUpdating = False
  For Each rw In Range("A2", Range("B" & Rows.Count).End(xlUp)).Rows
    If Not IsNumeric(rw.Cells(1).Value) Or Not IsNumeric(rw.Cells(2).Value) Then
      a = Val(rw.Cells(1).Value)
      b = Val(rw.Cells(2).Value)
      With Intersect(rw.EntireRow, Columns("F"))  '<- Check what column results are to go in
        .Value = b & " (" & IIf(b - a >= 0, "+", "") & b - a & ")"
        P = InStr(1, .Value, "(")
        L = InStr(1, .Value, ")") - P + 1
        .Font.ColorIndex = xlAutomatic
        .Characters(P, L).Font.Color = IIf(InStr(.Value, "-"), vbRed, vbBlue)
      End With
    End If
  Next rw
  Application.ScreenUpdating = True
End Sub

Here are my results