Excel function help

  • Thread starter Thread starter Francesco
  • Start date Start date
F

Francesco

Hi 2 all.

I need help for a little problem.

I've 5 row with 6 record, for example:

|-----------------------------|-----------------------------|-----------------------------|-----------------------------|-----------------------------|-----------------------------|
| Albert White | Luke Black | Mark Red | Lee Yellow | Mark Red | Maurice Pink |
|-----------------------------|-----------------------------|-----------------------------|-----------------------------|-----------------------------|-----------------------------|
| Giovanni Rossi | Alberto bianchi | Mario Rossi | Carlo gialli | Patrizio Verdi | Maurizio Rosa |
|-----------------------------|-----------------------------|-----------------------------|-----------------------------|-----------------------------|-----------------------------|
| Albert White | Luke Black | Mark Red | Lee Yellow | Mark Red | Maurice Pink |
|-----------------------------|-----------------------------|-----------------------------|-----------------------------|-----------------------------|-----------------------------|
| Giovanni Rossi | Alberto bianchi | Mario Rossi | Carlo gialli | Patrizio Verdi | Maurizio Rosa |
|-----------------------------|-----------------------------|-----------------------------|-----------------------------|-----------------------------|-----------------------------|
| Albert White | Luke Black | Mark Red | Lee Yellow | Mark Red | Maurice Pink |
|-----------------------------|-----------------------------|-----------------------------|-----------------------------|-----------------------------|-----------------------------|
| Giovanni Rossi | Alberto bianchi | Mario Rossi | Carlo gialli | Patrizio Verdi | Maurizio Rosa |
|-----------------------------|-----------------------------|-----------------------------|-----------------------------|-----------------------------|-----------------------------|




Now, i want to create a function that compare every record in a row
and find 2 equals.

Then if the first row contain 2 times "Mark Red" like in example, i
want that the 2 equals become writes in BLU with background yellow.

Sorry for my poor english, i hope that U understand my problem and
help me to solve.

Thanks.

Francesco
 
Say the top left item is in cell A1
then select cell A1
From the menu bar, Format/Conditional Formatting
set condition 1 from "Cell Value is" to "Formula Is" using the dropdown
arrow, and enter the formula
COUNTIF("$A1:$F1,A1)>1
and choose format blue background

Finally copy cell A1 to the clipboard and PasteSpecial formats only to the
other cells.

Francesco said:
Hi 2 all.

I need help for a little problem.

I've 5 row with 6 record, for example:

|-----------------------------|-----------------------------|---------------
--------------|-----------------------------|-----------------------------|-
----------------------------|
| Albert White | Luke Black | Mark Red |
Click to expand...
Lee Yellow | Mark Red | Maurice Pink |
|-----------------------------|-----------------------------|---------------
--------------|-----------------------------|-----------------------------|-
----------------------------|
| Giovanni Rossi | Alberto bianchi | Mario Rossi |
Click to expand...
Carlo gialli | Patrizio Verdi | Maurizio Rosa |
|-----------------------------|-----------------------------|---------------
--------------|-----------------------------|-----------------------------|-
----------------------------|
| Albert White | Luke Black | Mark Red |
Click to expand...
Lee Yellow | Mark Red | Maurice Pink |
|-----------------------------|-----------------------------|---------------
--------------|-----------------------------|-----------------------------|-
----------------------------|
| Giovanni Rossi | Alberto bianchi | Mario Rossi |
Click to expand...
Carlo gialli | Patrizio Verdi | Maurizio Rosa |
|-----------------------------|-----------------------------|---------------
--------------|-----------------------------|-----------------------------|-
----------------------------|
| Albert White | Luke Black | Mark Red |
Click to expand...
Lee Yellow | Mark Red | Maurice Pink |
|-----------------------------|-----------------------------|---------------
--------------|-----------------------------|-----------------------------|-
----------------------------|
| Giovanni Rossi | Alberto bianchi | Mario Rossi |
Click to expand...
Carlo gialli | Patrizio Verdi | Maurizio Rosa |
 
Oops, formular should start with "="

Jack Schitt said:
Say the top left item is in cell A1
then select cell A1
From the menu bar, Format/Conditional Formatting
set condition 1 from "Cell Value is" to "Formula Is" using the dropdown
arrow, and enter the formula
COUNTIF("$A1:$F1,A1)>1
and choose format blue background

Finally copy cell A1 to the clipboard and PasteSpecial formats only to the
other cells.


|-----------------------------|-----------------------------|---------------
|-
Lee Yellow | Mark Red | Maurice Pink |
|-----------------------------|-----------------------------|---------------
|-
Carlo gialli | Patrizio Verdi | Maurizio Rosa |
|-----------------------------|-----------------------------|---------------
|-
Lee Yellow | Mark Red | Maurice Pink |
|-----------------------------|-----------------------------|---------------
|-
Carlo gialli | Patrizio Verdi | Maurizio Rosa |
|-----------------------------|-----------------------------|---------------
|-
Lee Yellow | Mark Red | Maurice Pink |
|-----------------------------|-----------------------------|---------------
|-
Carlo gialli | Patrizio Verdi | Maurizio Rosa |
|-----------------------------|-----------------------------|---------------
|-
Click to expand...
 
Say the top left item is in cell A1
then select cell A1
From the menu bar, Format/Conditional Formatting
set condition 1 from "Cell Value is" to "Formula Is" using the dropdown
arrow, and enter the formula
COUNTIF("$A1:$F1,A1)>1
and choose format blue background

Finally copy cell A1 to the clipboard and PasteSpecial formats only to the
other cells.
Click to expand...

Thanks, but something is wrong.

I want that every record in the row confronts the other records, non
only with A1.

I hope will be clear...

:: Francesco
 
Francesco said:
Thanks, but something is wrong.

I want that every record in the row confronts the other records, non
only with A1.

I hope will be clear...

:: Francesco
Click to expand...

It should still work, I think. If you enter "A1" without preceding dollar
signs (ie A1 not $A$1) then when you copy the format to other cells the
reference to A1 should change also to the reference of the destination
cells.
 
I did notice a typo in the original formula;

Should be
=COUNTIF($A1:$F1,A1)>1
not
COUNTIF("$A1:$F1,A1)>1

You may have already noticed that.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IF AND OR STATEMENT 3
Count function in excel with different conditions 1
Custom function to find top sellers 2
Help please with coding a macro 3
Conditional formatting excel 2003 1
LOOKUP 2
Help with INDEX/MATCH 2
Using INDEX and MATCH to find data in 2 different sheets 5

Back
Top