Excel Compare

[dayaptl]

Hi,

I want help to find and highlight cell having same value and/or text in the same sheet.
I know this could be done by lookup function but I can't apply it correctly.

Thanks

[mangesh_yadav]

Select the entire range (suppose it is A1:A10)
Go to Format > Conditional Formatting
Select 'formula is'
and enter the formula:
=COUNTIF($A$1:$A$10,A1)>1
apply some format by clicking on the format button.

Mangesh

[dayaptl]

Hi,

Thanks for help.
It serve my purpose.
I need some additional help .
If I have four columns each having 1000 rows .
I apply conditional format as you replied and O.K. Now I want copy and paste text of the fourth column which are similar to first three column in the fifth column.
let me explain . If out of 1000 only 25 text (fourth column) is similar to first three column. Then these 25 entries should copy and paste in fifth column.

Regards

[Dhruva101]

Hi Dayaptl,

If I understand you correctly.This is the way your spread sheet looks

1- 2- 3- 3
2- 2- 2- 2
3- 3- 3- 3
4- 4- 4- 4
5- 5- 5- 5
6- 5- 5- 5
6- 6- 6- 6
7- 6- 7- 7
8- 9- 8- 8
9- 9- 9- 9
12- 12- 12- 12
12- 12- 12- 12
11- 13- 14- 14

and in the above spread sheet, rows 2,3,4,5,7,10,11,12 have same values in all the four columns and if that is the case you want them to be populated on the fifth column.. Am I right ?

If yes, use the following forumla in your fourth column..
=IF((A2=B2),IF((A2=C2),IF((B2=C2),IF((A2=D2),IF((B2=D2),IF((C2=D2),A2))))))
The sheet looks as follows..

1- 2- 3- 3- FALSE
2- 2- 2- 2- 2
3- 3- 3- 3- 3
4- 4- 4- 4- 4
5- 5- 5- 5- 5
6- 5- 5- 5- FALSE
6- 6- 6- 6- 6
7- 6- 7- 7- FALSE
8- 9- 8- 8- FALSE
9- 9- 9- 9- 9
12- 12- 12- 12- 12
12- 12- 12- 12- 12
11- 13- 14- 14- FALSE

[dayaptl]

Hi,

You are almost correct but I attached my sheet for clarification.
Any data in forth column which are similar to data of first three column should copy and paste in fifth column.
In my sheet d2,d8,d9,d10,d13,and d17 are similar to data of any previous three column, which should be copy and paste in fifth column.(Similar data can be found by your formula =countif(.... ....).

Regards.

[Dhruva101]

Hi,

You are almost correct but I attached my sheet for clarification.
Any data in forth column which are similar to data of first three column should copy and paste in fifth column.
In my sheet d2,d8,d9,d10,d13,and d17 are similar to data of any previous three column, which should be copy and paste in fifth column.(Similar data can be found by your formula =countif(.... ....).

Regards.

I don't think I understand your question..

In D2 you have - YUIJ and I don't see this text in no other place except D2. Then how can it be similar to data of any previous three columns.

D8 - WER You have it only in A10,
D9 - EDC, You have it only in A12
D10 - UYV, You don't have this any where except on D10
D13 - XCVB, You don't have this any where except on D13
D17 - KIU, You have it only in A3.

Now let me know, so if the text of the any cell on D column appears in any of the three previous columns (A, B, C), You need that text on the Fifth column, Right ?

Please clarify..

[starguy]

Hi,

You are almost correct but I attached my sheet for clarification.
Any data in forth column which are similar to data of first three column should copy and paste in fifth column.
In my sheet d2,d8,d9,d10,d13,and d17 are similar to data of any previous three column, which should be copy and paste in fifth column.(Similar data can be found by your formula =countif(.... ....).

Regards.

here is file with formula that returns what you required