# 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

#### sksanjeev786

##### Active Member
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
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

##### Active Member
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
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

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
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

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

Sanjeev

#### Attachments

• 00.png
880 bytes · Views: 2

#### sksanjeev786

##### Active Member
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
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
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

Replies
14
Views
456
Replies
2
Views
138
Replies
3
Views
248
Replies
9
Views
374
Replies
22
Views
246

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.

### 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