need True =True data

[sksanjeev786]

Hi Team

Below is my data and i need True data for both A 4 having Color and E4 having arrow symbol..so I4 i need true data

Book1
	A	B	C	D	E	F	G	H	I	J	K
3	-0.6	0.8	1.7		-0.6	0.8	1.7		TRUE	TRUE	TRUE
4	-7.6	-7.2	-2.9		-7.6 q	-7.2 q	-2.9		FALSE	FALSE	TRUE
5	-6	-3.9	-3.6		-6	-3.9	-3.6		TRUE	TRUE	TRUE
6	3.6	3.9	4.6		3.6 r	3.9 r	4.6 p		FALSE	FALSE	FALSE
7	-5.9	-4.9	-3.4		-5.9	-4.9	-3.4		TRUE	TRUE	TRUE
8	-5.5	-4.9	-1.7		-5.5	-4.9	-1.7		TRUE	TRUE	TRUE
Sheet1

Cell Formulas
Range	Formula
I3:K8	I3	=A3=E3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I3:L8	Cell Value	contains "f"	text	NO

 

[Alex Blakenburg]

I am not sure if XL2BB is losing something in the translation.
You mention seeing a symbol in column E but all that is coming through is that E4 & E6 have a q and r at the end and are being seen as Text.

Your profile has 2 version on it. If you are using MS 365 and E4 and E6 are text at your end then would something like this work ?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2
3	-0.6	0.8	1.7		-0.6	0.8	1.7		TRUE	TRUE	TRUE
4	-7.6	-7.2	-2.9		-7.6 q	-7.2 q	-2.9		TRUE	TRUE	TRUE
5	-6	-3.9	-3.6		-6	-3.9	-3.6		TRUE	TRUE	TRUE
6	3.6	3.9	4.6		3.6 r	3.9 r	4.6 p		TRUE	TRUE	TRUE
7	-5.9	-4.9	-3.4		-5.9	-4.9	-3.4		TRUE	TRUE	TRUE
8	-5.5	-4.9	-1.7		-5.5	-4.9	-1.7		TRUE	TRUE	TRUE
Sheet1

Cell Formulas
Range	Formula
I3:K8	I3	=A3=VALUE(TEXTBEFORE(E3," ",,,1))

 

[sksanjeev786]

I am not sure if XL2BB is losing something in the translation.
You mention seeing a symbol in column E but all that is coming through is that E4 & E6 have a q and r at the end and are being seen as Text.

Your profile has 2 version on it. If you are using MS 365 and E4 and E6 are text at your end then would something like this work ?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2
3	-0.6	0.8	1.7		-0.6	0.8	1.7		TRUE	TRUE	TRUE
4	-7.6	-7.2	-2.9		-7.6 q	-7.2 q	-2.9		TRUE	TRUE	TRUE
5	-6	-3.9	-3.6		-6	-3.9	-3.6		TRUE	TRUE	TRUE
6	3.6	3.9	4.6		3.6 r	3.9 r	4.6 p		TRUE	TRUE	TRUE
7	-5.9	-4.9	-3.4		-5.9	-4.9	-3.4		TRUE	TRUE	TRUE
8	-5.5	-4.9	-1.7		-5.5	-4.9	-1.7		TRUE	TRUE	TRUE
Sheet1

Cell Formulas
Range	Formula
I3:K8	I3	=A3=VALUE(TEXTBEFORE(E3," ",,,1))

Thank you so much Alex,

But can we have data checked including Color on column A and letter column E (Basically it is a symbol Wingding font)
This is the interior color of cell color in column A

18​	Red
45​	Light Green
12​	Dark Green

 

[Alex Blakenburg]

To check colour you are going to need VBA.
Is that what you had in mind ?

If it is I am about to log off and can't do it tonight, if that is what you want and you don't get someone to help you before tomorrow my time, I can have a look at it then.
Also please confirm you want the code to be compared as follows.

.Interior.ColorIndex	Colour	Matching Code	Comment
18	Red	q
45	Light Green	r	Appearing as Orange to me
12	Dark Green	p

 

[sksanjeev786]

To check colour you are going to need VBA.
Is that what you had in mind ?

If it is I am about to log off and can't do it tonight, if that is what you want and you don't get someone to help you before tomorrow my time, I can have a look at it then.
Also please confirm you want the code to be compared as follows.

.Interior.ColorIndex	Colour	Matching Code	Comment
18	Red	q
45	Light Green	r	Appearing as Orange to me
12	Dark Green	p

Yes! I need VBA code as well as i need to compare both the data and final checking with true data, cell color = data,pqr in other column

Thanks in advance…
Regards
Sanjeev

 

[Alex Blakenburg]

I am not a huge fan of using UDFs and this is a bit ugly, but give it a try.

Called as:

=CompValues(A3,E3)

Function CompValues(rColour As Range, rSymbol As Range) As Boolean

    Dim arrColour As Variant, arrSymbol As Variant
    Dim valColour As Currency, arrvalSymbol As Variant
    Dim i As Long
    
    CompValues = False
    arrColour = Array(18, 45, 12)
    arrSymbol = Array("q", "r", "p")
    
    valColour = rColour.Value
    arrvalSymbol = Split(rSymbol.Value, " ")
    
    If valColour = CCur(arrvalSymbol(0)) Then
        If rColour.Interior.ColorIndex = -4142 And UBound(arrvalSymbol) = 0 Then
            CompValues = True
            Exit Function
        Else
            For i = LBound(arrColour) To UBound(arrColour)
                If rColour.Interior.ColorIndex = arrColour(i) And _
                    arrvalSymbol(UBound(arrvalSymbol)) = arrSymbol(i) Then
                        CompValues = True
                        Exit Function
                End If
            Next i
        End If
    End If

End Function

20231022 VBA Text to Value TextBefore sksaVnjeev786.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2
3	-0.6	0.8	1.7		-0.6	0.8	1.7		TRUE	TRUE	TRUE
4	-7.6	-7.2	-2.9		-7.6 q	-7.2 q	-2.9		TRUE	TRUE	TRUE
5	-6	-3.9	-3.6		-6	-3.9	-3.6		TRUE	TRUE	TRUE
6	3.6	3.9	4.6		3.6 r	3.9 r	4.6 p		TRUE	TRUE	TRUE
7	-5.9	-4.9	-3.4		-5.9	-4.9	-3.4		TRUE	TRUE	TRUE
8	-5.5	-4.9	-1.7		-5.5	-4.9	-1.7		TRUE	TRUE	TRUE
Sheet1

Cell Formulas
Range	Formula
I3:K8	I3	=CompValues(A3,E3)

 

[sksanjeev786]

I am not a huge fan of using UDFs and this is a bit ugly, but give it a try.

Called as:

=CompValues(A3,E3)

Function CompValues(rColour As Range, rSymbol As Range) As Boolean

    Dim arrColour As Variant, arrSymbol As Variant
    Dim valColour As Currency, arrvalSymbol As Variant
    Dim i As Long
   
    CompValues = False
    arrColour = Array(18, 45, 12)
    arrSymbol = Array("q", "r", "p")
   
    valColour = rColour.Value
    arrvalSymbol = Split(rSymbol.Value, " ")
   
    If valColour = CCur(arrvalSymbol(0)) Then
        If rColour.Interior.ColorIndex = -4142 And UBound(arrvalSymbol) = 0 Then
            CompValues = True
            Exit Function
        Else
            For i = LBound(arrColour) To UBound(arrColour)
                If rColour.Interior.ColorIndex = arrColour(i) And _
                    arrvalSymbol(UBound(arrvalSymbol)) = arrSymbol(i) Then
                        CompValues = True
                        Exit Function
                End If
            Next i
        End If
    End If

End Function

20231022 VBA Text to Value TextBefore sksaVnjeev786.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2
3	-0.6	0.8	1.7		-0.6	0.8	1.7		TRUE	TRUE	TRUE
4	-7.6	-7.2	-2.9		-7.6 q	-7.2 q	-2.9		TRUE	TRUE	TRUE
5	-6	-3.9	-3.6		-6	-3.9	-3.6		TRUE	TRUE	TRUE
6	3.6	3.9	4.6		3.6 r	3.9 r	4.6 p		TRUE	TRUE	TRUE
7	-5.9	-4.9	-3.4		-5.9	-4.9	-3.4		TRUE	TRUE	TRUE
8	-5.5	-4.9	-1.7		-5.5	-4.9	-1.7		TRUE	TRUE	TRUE
Sheet1

Cell Formulas
Range	Formula
I3:K8	I3	=CompValues(A3,E3)

Thank you so much....Alex

This works perfectly!!!

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.