CF using MID formula

[Spraggs]

Hello All (Using Excel 2007)
In column A I have list of cells that I want to CF. (A2:A5000)
Example of cell entries....

2468-WST-HEN-HTG-BI032
2468-GSV-BLD-ADM-EC016S
2468-GSV-BLD-EFC-EC052S
2468-GSV-QSJ-L01-BI032
2468-QSE-MAN-17B-EC506E
2468-QSJ-MAN-12F-EC760J
2468-QSH-FIN-26F-WS011
2468-WST-PCK-26D-CR024
2468-QSH-PCK-26A-FC144
2468-WHP-MAN-L02-SC047
2468-QSH-PCK-MBP-WP109
2468-QSH-FIN-26F-CR024

I want to conditional format if...... =MID(A2,18,7) is = to another cell up to cell A5000 and the same for any of the other cells in that range.

Thank You

 

[prajul89]

Goto conditional formating>>New rules>>Use a formula to determine which cell to format

then add this formula in the bar =MID(A1,18,7)=F1 (assuming the value to be matched is in F column) and change the format as per requirement.
I hope this solves your problem

 

[Spraggs]

The value to be matched is any of the other 4998 resuts.

 

[prajul89]

oh Its quite difficult, though I will try.
But it would be easy IF you put the mid formula else where in the sheet(say F column) then the formula in conditional formating would be =COUNTIF(F:F,MID(A1,18,7))>1.

 

[Aladin Akyurek]

MID(A2,18,7) must be equal to what exactly?

 

[Spraggs]

MID(A3,18,7)
MID(A4,18,7)
MID(A5,18,7)
MID(A6,18,7)
MID(A7,18,7)
etc......
up to A5000

i.e. =MID(A2,18,7) in cell B2
=COUNTIF($B$2:$B$5000,B2) in cell C2

But encompassed in cell A2 as a CF (CF if result is>1)

 

[Aladin Akyurek]

MID(A3,18,7)
MID(A4,18,7)
MID(A5,18,7)
MID(A6,18,7)
MID(A7,18,7)
etc......
up to A5000

i.e. =MID(A2,18,7) in cell B2
=COUNTIF($B$2:$B$5000,B2) in cell C2

But encompassed in cell A2 as a CF (CF if result is>1)

A2: some string...

B2:

=MID(A2,18,7)

C2:

=COUNTIF($B$2:$B$5000,B2)

What cell do you want to conditionally format and what is the rule? is it

CF A2 when C2 > 1?

 

[Spraggs]

I want to condition all the cells A2:A5000
The condition is if the last part of the cell (18th digit onwards) is equal to any of the other 4999 cells (18th digit onwards) then format to suit.
i.e. The following cells would meet criterea.

2468-WST-HEN-HTG-BI032
2468-GSV-QSJ-L01-BI032
Hence both contain BI032 from the 18th digit.

Sorry for poor explanation.

 

[AdamL]

In A2 apply this conditional formatting:

=SUMPRODUCT(--(MID(A2,18,7)=MID(A$2:A$5000,18,7)))>1

and then copy this formatting down the column. Does that work?

HTH
Adam

 

[Aladin Akyurek]

I want to condition all the cells A2:A5000
The condition is if the last part of the cell (18th digit onwards) is equal to any of the other 4999 cells (18th digit onwards) then format to suit.
i.e. The following cells would meet criterea.

2468-WST-HEN-HTG-BI032
2468-GSV-QSJ-L01-BI032
Hence both contain BI032 from the 18th digit.

Sorry for poor explanation.

Select A2:A5000.
Fire up CF.
Choose Use a formula as Rule type.
Invoke the following formula in the white space:

=IF($A2<>"",COUNTIF($A$2:$A$500,"*"&RIGHT($A2,5))>1)

Activate the Format button.
Choose the formatting you want.
Click OK, OK.