Data with Different score

sksanjeev786

Active Member
Joined
Aug 5, 2020
Messages
271
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
 

sksanjeev786

Active Member
Joined
Aug 5, 2020
Messages
271
Office Version
  1. 365
  2. 2016
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&")"))


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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,170
Office Version
  1. 365
Platform
  1. Windows
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.

1622715570168.png
 

sksanjeev786

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

Well-known Member
Joined
Feb 3, 2010
Messages
9,821
Office Version
  1. 365
Platform
  1. Windows
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
1Suggestion
214(-39)
324(+2)
415(-6)
511(+1)
6 
7 
843(+25)
Sheet2
Cell Formulas
RangeFormula
G2:G8G2=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
CellConditionCell FormatStop If True
G2:G8Expression=NOT(ISERROR(FIND("-",G2)))textNO
G2:G8Expression=AND(G2<>"",ISERROR(FIND("-",G2)))textNO
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,170
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

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

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

ADVERTISEMENT

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


Try:
Other.xlsm
G
1Suggestion
214(-39)
324(+2)
415(-6)
511(+1)
6 
7 
843(+25)
Sheet2
Cell Formulas
RangeFormula
G2:G8G2=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
CellConditionCell FormatStop If True
G2:G8Expression=NOT(ISERROR(FIND("-",G2)))textNO
G2:G8Expression=AND(G2<>"",ISERROR(FIND("-",G2)))textNO


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

Thanks in advance :)
Sanjeev
 

Attachments

  • 00.png
    00.png
    880 bytes · Views: 2

sksanjeev786

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

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,170
Office Version
  1. 365
Platform
  1. Windows
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?

VBA Code:
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

1622717760113.png
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,133,271
Messages
5,657,760
Members
418,411
Latest member
Excellency

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