True and False including Bold data

S

sksanjeev786

Well-known Member
Joined
Aug 5, 2020
Messages
884
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Team,

I am comparing data from F to I and L to O

in H14 i have unbold data and N14 bold data so i should get false. thought hte number are same

FS.xlsm
FGHIJKLMNOPQRS
114653+7r46537rTRUETRUETRUETRUE
122234+12p223412pTRUETRUETRUETRUE
13816+8p8168pTRUETRUETRUETRUE
143028-2 3028-2TRUETRUETRUETRUE
155261+9p52619pTRUETRUETRUETRUE
163652+15p365215pTRUETRUETRUETRUE
Sheet1 (2)
Cell Formulas
RangeFormula
F11:H16F11=INDEX(INDIRECT("'"&$A11&$B11&"'!A"&$C11&":IV"&$D11&""),MATCH($E11,INDIRECT("'"&$A11&$B11&"'!A"&$C11&":A"&$D11&""),0),F$2)*100
I11:I16I11=IFERROR(IF(FIND($W$1,H44),$Y$1),"")&IFERROR(IF(FIND($W$2,H44),$Y$2),"")&IFERROR(IF(FIND($W$3,H44),$Y$3),"")
P11:R16P11=ROUND(F11,0)=L11
S11:S16S11=I11=O11
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H5:H20Expression=I5="p"textNO
H5:H20Expression=I5="q"textNO
H5:H20Expression=I5="r"textNO
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Most comparisons do not pay attention to cell format. One way to do it might be to use the GET.CELL Formula

Book3
ABCDE
17TRUE7TRUE
212TRUE14FALSE
38TRUE8TRUE
4-2FALSE-2FALSE
59TRUE9TRUE
69TRUE9FALSE
79TRUE9FALSE
815TRUE15TRUE
Sheet1
Cell Formulas
RangeFormula
B1:B8B1=IsBold
E1:E8E1=AND(B1,IsBold,A1=D1)

(IsBold formula =GET.CELL(20,Sheet1!A1)


But I find GET.CELL to be tricky to use, so another alternative would be a VBA UDF
VBA Code: 
Function FCompare(Cell1 As Range, Cell2 As Range) As Boolean
    FCompare = Cell1.Value = Cell2.Value And Cell1.Font.Bold = Cell2.Font.Bold
End Function

Book3
ABCDE
1477TRUE
151214FALSE
1688TRUE
17-2-2FALSE
1899TRUE
1999FALSE
2099TRUE
211515TRUE
221513FALSE
Sheet1
Cell Formulas
RangeFormula
E14:E22E14=fcompare(A14,D14)
 
Upvote 0
rlv01 said:
Most comparisons do not pay attention to cell format. One way to do it might be to use the GET.CELL Formula

Book3
ABCDE
17TRUE7TRUE
212TRUE14FALSE
38TRUE8TRUE
4-2FALSE-2FALSE
59TRUE9TRUE
69TRUE9FALSE
79TRUE9FALSE
815TRUE15TRUE
Sheet1
Cell Formulas
RangeFormula
B1:B8B1=IsBold
E1:E8E1=AND(B1,IsBold,A1=D1)

(IsBold formula =GET.CELL(20,Sheet1!A1)


But I find GET.CELL to be tricky to use, so another alternative would be a VBA UDF
VBA Code: 
Function FCompare(Cell1 As Range, Cell2 As Range) As Boolean
    FCompare = Cell1.Value = Cell2.Value And Cell1.Font.Bold = Cell2.Font.Bold
End Function

Book3
ABCDE
1477TRUE
151214FALSE
1688TRUE
17-2-2FALSE
1899TRUE
1999FALSE
2099TRUE
211515TRUE
221513FALSE
Sheet1
Cell Formulas
RangeFormula
E14:E22E14=fcompare(A14,D14)
Click to expand...

Thank you so much for your help :)
 
Upvote 0
Ca
rlv01 said:
Most comparisons do not pay attention to cell format. One way to do it might be to use the GET.CELL Formula

Book3
ABCDE
17TRUE7TRUE
212TRUE14FALSE
38TRUE8TRUE
4-2FALSE-2FALSE
59TRUE9TRUE
69TRUE9FALSE
79TRUE9FALSE
815TRUE15TRUE
Sheet1
Cell Formulas
RangeFormula
B1:B8B1=IsBold
E1:E8E1=AND(B1,IsBold,A1=D1)

(IsBold formula =GET.CELL(20,Sheet1!A1)


But I find GET.CELL to be tricky to use, so another alternative would be a VBA UDF
VBA Code: 
Function FCompare(Cell1 As Range, Cell2 As Range) As Boolean
    FCompare = Cell1.Value = Cell2.Value And Cell1.Font.Bold = Cell2.Font.Bold
End Function

Book3
ABCDE
1477TRUE
151214FALSE
1688TRUE
17-2-2FALSE
1899TRUE
1999FALSE
2099TRUE
211515TRUE
221513FALSE
Sheet1
Cell Formulas
RangeFormula
E14:E22E14=fcompare(A14,D14)
Click to expand...

Can i fit in the below formula... :)

FCompare(ROUND(F5,0)=L5,F5,L5)
 
Upvote 0
sksanjeev786 said:
Ca


Can i fit in the below formula... :)

FCompare(ROUND(F5,0)=L5,F5,L5)
Click to expand...

FCompare requires access to the cell's range object, so no. But you could add the rounding function to the VBA code.
 
Upvote 0
rlv01 said:
FCompare requires access to the cell's range object, so no. But you could add the rounding function to the VBA code.
Click to expand...
Sure could you please provide and help on this :)

Regards
Sanjeev
 
Upvote 0
sksanjeev786 said:
Sure could you please provide and help on this :)

Regards
Sanjeev
Click to expand...
If you can provide a clear explanation of what you mean by "fit in". This FCompare(ROUND(F5,0)=L5,F5,L5) does not make sense to me.
 
Upvote 0
rlv01 said:
If you can provide a clear explanation of what you mean by "fit in". This FCompare(ROUND(F5,0)=L5,F5,L5) does not make sense to me.
Click to expand...

Sure, So my data in F to I are in Decimal so use the Rounded function in P to S data simantansaly i have to check Bold and unbold data so i would like to add the function you have given us :) Fcompare so i was trying with this

FCompare(ROUND(F5,0)=L5,F5,L5)

FS.xlsm
EFGHIJKLMNOPQRS
12Digital Ad Recall 2234+12p223412pTRUETRUETRUETRUE
13Social Ad Recall 816+8p8168pTRUETRUETRUETRUE
14TV Ad Recall 3028-2 3028-2TRUETRUETRUETRUE
15Brand Favorability 5261+9p52619pTRUETRUETRUETRUE
16Consideration Intent3652+15p365215pTRUETRUETRUETRUE
Sheet1 (2)
Cell Formulas
RangeFormula
F12:H16F12=INDEX(INDIRECT("'"&$A12&$B12&"'!A"&$C12&":IV"&$D12&""),MATCH($E12,INDIRECT("'"&$A12&$B12&"'!A"&$C12&":A"&$D12&""),0),F$2)*100
I12:I16I12=IFERROR(IF(FIND($W$1,H45),$Y$1),"")&IFERROR(IF(FIND($W$2,H45),$Y$2),"")&IFERROR(IF(FIND($W$3,H45),$Y$3),"")
P12:R16P12=ROUND(F12,0)=L12
S12:S16S12=I12=O12
Cells with Conditional Formatting
CellConditionCell FormatStop If True
P5:S20Cell Valuecontains "f"textNO
H5:H20Expression=I5="p"textNO
H5:H20Expression=I5="q"textNO
H5:H20Expression=I5="r"textNO
 
Upvote 0
Perhaps this
VBA Code: 
Function FCompare(Cell1 As Range, Cell2 As Range) As Boolean
    FCompare = Round(Cell1.Value, 0) = Cell2.Value And Cell1.Font.Bold = Cell2.Font.Bold
End Function

Book3
ABCDE
147.1502897TRUE
1512.2759714FALSE
168.7662948FALSE
17-2.28471-2FALSE
189.85637110TRUE
199.5848199FALSE
209.0259889TRUE
2115.9916115FALSE
2215.1334715TRUE
2315.961816TRUE
Sheet1
Cell Formulas
RangeFormula
E14:E23E14=fcompare(A14,D14)
 
Upvote 0
rlv01 said:
Perhaps this
VBA Code: 
Function FCompare(Cell1 As Range, Cell2 As Range) As Boolean
    FCompare = Round(Cell1.Value, 0) = Cell2.Value And Cell1.Font.Bold = Cell2.Font.Bold
End Function

Book3
ABCDE
147.1502897TRUE
1512.2759714FALSE
168.7662948FALSE
17-2.28471-2FALSE
189.85637110TRUE
199.5848199FALSE
209.0259889TRUE
2115.9916115FALSE
2215.1334715TRUE
2315.961816TRUE
Sheet1
Cell Formulas
RangeFormula
E14:E23E14=fcompare(A14,D14)
Click to expand...
Thanks you so much :):)

It works very well:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Sorting from the lowest to highest value
Replies
1
Views
80
dermie_72
D
P
  • Solved
Table Efficiency Cleanup
2
Replies
17
Views
149
Fluff
Fluff
GMC The Macro Man
  • Question
Clear Data without VBA
2
Replies
12
Views
753
mumps
mumps
J
  • Question
sumif function with delayed application
Replies
5
Views
140
jbrown021286
J
S
  • Question
VBA support true and false color
Replies
5
Views
423
sksanjeev786
S

Forum statistics

Threads
1,215,327
Messages
6,124,276
Members
449,149
Latest member
mwdbActuary

Share this page

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