Highlight the knock off cells

[Ranganayakulu]

Hi,

Situation is like this:

Having Excel sheet of 1000 rows and Contains both -ve and +ve values.

Data will be like this :In one column there are cells with values -100,+200,+100,-1000,+1000...

Requirement: Highlight the cells which are knocking off.Means it should highlight -100,+100,-1000,+1000....


Suggest the wayyyy


Thank you in advance

 

[sgmpatnaik]

@Ranganayakulu


Hi


Welcome to Chandoo.org forums, glad you are here


your subject is not clear can you give some details other wise try to upload a sample workbook with information


Thanks


SP

 

[sgmpatnaik]

@Ranganayak


Hi


Welcome to Chandoo.org Forum, glad you are here


your subject is not clear please try to upload a sample workbook with manual result


Thanks


SP

 

[Faseeh]

Hi Ranganayakulu,


Welcome to the forums!! and this was an interesting problem


If your data looks like this in Col A of the sheet:

[pre]

100
200
200
300
1500
100000
1200
1000
-1500
352
125
2330
1230
10
1000
1010
100
100[/pre]

Go to Conditioanl Formatting, Manage Rule, New Rule and paste this formula: 


=AND(ISNUMBER(BIN2DEC(ABS(A1)))=TRUE,MOD(BIN2DEC(ABS(A1)),4)=0)

Apply to entire range.


Regards,

 

[Sajan]

Hello Ranganayakulu,


One approach is to identify each matching pair, and then highlight the pairs. To simplify things, I have used a helper column to identify the location of the matching pair. (If you need to eliminate the helper column, we can look for options. Personally, I like the idea of seeing where the matching pair is located.)


Assuming your data range is in A2:A13, enter the following formula into C2, and copy down to C13:

=IFERROR(MATCH(ROW(A2), $C$1:C1, 0), (MATCH(IF(ISNA(MATCH(ROW(A2), $C$1:C1,0)), -A2,9999999), IF(ISNA(MATCH(ROW(A3:$A$14), $C$1:C1, 0)), A3:$A$14),0)+ROWS($A$1:A2)))


entered with Ctrl + Shift + Enter


To conditionally highlight your data in column A, you can check if column C contains a number:

=ISNUMBER($C2)

Entered as a rule after selecting A2:A13


The sample data I used is as follows, with the results from the formula in the 3rd column:

[pre]

List	Row#	Matching Pair Location
-100	2	12
-100	3	13
-200	4	#N/A
-100	5	#N/A
800	6	8
-400	7	10
-800	8	6
700	9	#N/A
400	10	7
-600	11	#N/A
100	12	2
100	13	3

[/pre]
For example, in the above sample data, the match for row #2 is row #12. (Correspondingly, matching pair for row #12 is row #2.)


Hope this helps.


Cheers,

Sajan.

 

[Faseeh]

@ Sajan,


I missed the pairing criteria!! :D


Faseeh

 

[Sajan]

Hi Faseeh,

No problem! Glad we were able to offer a solution.


Regards,

Sajan.

 

[NARAYANK991]

Hi Sajan ,


I think the last segment of your formula , ROWS($A$1:A2) , could be replaced by ROW(A2).


With ROWS($A$1:A2) , inserting a row at the top most row creates problems.


Narayan

 

[Sajan]

Thanks Narayan!