comparing values in two to four coumns

[codedreamer]

I am trying to compare values in 4 columns. I would like the equal values to be highlited and if the number on the left is les than the number on the right to higlit with another color. I know I can use count if to get the numbers I want, but how can I get them in contiguos cells?

thanks for your help,


A B C D

2 2 1 2

4 2 3 3

2 1 2 2

3 2 4 2

5 5 3 4

2 2 2 3

1 2 4 5

2 3 5 5


Codedreamer

 

[Hui]

Codedreamer


It sounds like either a complex set of Conditional Formats or some VBA will be required


Can you post a sample file with the data above highlighted as you want it highlighted so we can see what your after?

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook


Also what if the data does 2 2 4 4 ?

 

[codedreamer]

https://www.dropbox.com/home/excel?select=numbers.xlsx


Hi Hui,

Here is the link. I really need to know how many of each kind I have, but thought that if they are colored it would be easier to count.

The numbers should be adjacent to each other for example in column ab, cd, so the first set of 2,2 is yellow, but the one next to it is not because tha is 2,1

I hope I explain myself.

Thank you for your help,


codedreamer

 

[Sajan]

Hello @codedreamer,

Due to firewall restrictions on my end I am not able to see your sample file.


However, I interpreted your question as follows:

If two adjacent cells have the same value, color it yellow.

If two adjacent cells have ascending numeric values, color it another color.


To keep things simple, I have assumed that your data starts in column B (leaving col A empty). For the following formula, I have also assumed your data range starts in cell B1.


Select your entire data range (e.g. B1:E8), then add the following conditional format formulas.

=OR(B1=A1,B1=C1)

Set the fill for this rule to be "yellow" color.


For the same data range, add the following conditional format formula:

=AND(NOT(ISBLANK(A1)),A1<B1)

Set the fill for this rule to be "blue" (or some other color).


Apply the rules.


In your sample data (from your original post), I got the following results:

[pre]

Y	Y	1	B
4	2	Y	Y
2	1	Y	Y
3	2	B	2
Y	Y	3	B
Y	Y	Y	B
1	B	B	B
2	B	Y	Y

[/pre]
Where Y stands for yellow coloring, B stands for blue coloring, and a numeric value indicates no coloring.


In my setup, the Yellow rule was first, and Blue rule second. That controls how the cells are colored.


Cheers,

Sajan.


P.S. Re-reading your second post, you indicate that you actually want the counts for these. If you can confirm that the above approach is what you were after, we can come up with the formulas to count the cells.

 

[Sajan]

Hello,

Using the above approach, to count the pairs, you can use the following formula:

=SUMPRODUCT(N(B1:E1=C1:F1))


To count the numbers that are greater than the cell to the left, you can use the following formula:

=SUMPRODUCT(N(B1:E1<C1:F1))


For the above data in your original post, I got the following counts:

[pre]

B	C	D	E	Blank	Pairs	Larger
2	2	1	2		1	1
4	2	3	3		1	1
2	1	2	2		1	1
3	2	4	2		0	1
5	5	3	4		1	1
2	2	2	3		2	1
1	2	4	5		0	3
2	3	5	5		1	2

[/pre]
Again, if you were hoping for different results, feel free to describe the rules you wish to apply to the data.


Cheers,

Sajan.

 

[codedreamer]

Thank you so much Sajan. That's what I needed


Codedreamer

 

[Sajan]

Thanks for the feedback. Glad to help.


Cheers,

Sajan.