Excel Compare

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

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

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

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

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.

Originally Posted by 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.

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..

Hi

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 ?

Actjectly right.


Regards

Hi,

Take a look at all solutions posted by Chip :

http://cpearson.com/excel/duplicat.htm

HTH
Cheers
Carim

Hi
Thanks for reply.
I checked it but couldn't find my answer.
My requirement is
"If the text of the any cell on D column appears in any of the three previous columns (A, B, C), I need that text on the Fifth column"

Please help on this.

Regards

Originally Posted by 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.

here is file with formula that returns what you required

Originally Posted by starguy

here is file with formula that returns what you required

Hello Starguy

"If the text of the any cell on D column appears in any of the three previous columns (A, B, C), I need that text on the Fifth column"

If he wants the text in D to be in E isn't it

=IF(AND(ISERROR(MATCH(D1,$A$1:$A$20,0)),ISERROR(MATCH(D1,$B$1:$B$20,0)),ISERROR(MATCH(D1,$C$1:$C$20,0))),D1,"")

Hi
Starguy

Thanks That is perfectly what I want.

How can I delete empty row from column E and arrange text alphabatically.

Thanks again for your help.

Originally Posted by dayaptl

Hi
Starguy

Thanks That is perfectly what I want.

How can I delete empty row from column E and arrange text alphabatically.

Thanks again for your help.

Select column E, copy > paste special > values, then select A1:E20, go to Data, Sort, Ascending, all the blanks will be at the top with the rest sorted alphabetically

Originally Posted by oldchippy

Hello Starguy

=IF(AND(ISERROR(MATCH(D1,$A$1:$A$20,0)),ISERROR(MATCH(D1,$B$1:$B$20,0)),ISERROR(MATCH(D1,$C$1:$C$20,0))),D1,"")

Can the above formula changed like this ?

=IF(AND(ISERROR(MATCH(D1,$A$1:$A$20,FALSE)),ISERROR(MATCH(D1,$B$1:$B$20,FALSE)),ISERROR(MATCH(D1,$C$1:$C$20,FALSE))),D1,"")

Should it provide the same result ? I have tried it but I was getting some problem in that? Can you please clarify??

Originally Posted by Dhruva101

Can the above formula changed like this ?



Should it provide the same result ? I have tried it but I was getting some problem in that? Can you please clarify??

Hi Dhruva101,

This is an extract from Excel Help on the MATCH function, the match_type must be a number as seen below not TRUE or FALSE

MATCH(lookup_value,lookup_array,match_type)

Lookup_value is the value you use to find the value you want in a table.

Lookup_value is the value you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want.

Lookup_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.

Lookup_array is a contiguous range of cells containing possible lookup values. Lookup_array must be an array or an array reference.

Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.

If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

If match_type is omitted, it is assumed to be 1.

Remarks

MATCH returns the position of the matched value within lookup_array, not the value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, the relative position of "b" within the array {"a","b","c"}.
MATCH does not distinguish between uppercase and lowercase letters when matching text values.
If MATCH is unsuccessful in finding a match, it returns the #N/A error value.
If match_type is 0 and lookup_value is text, you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

Hope this answers your question

oldchippy

Originally Posted by Dhruva101

Can the above formula changed like this ?



Should it provide the same result ? I have tried it but I was getting some problem in that? Can you please clarify??

=IF(AND(ISERROR(MATCH(D1,$A$1:$A$20,0)),ISERROR(MATCH(D1,$B$1:$B$20,0)),ISERROR(MATCH(D1,$C$1:$C$20,0))),"",D1)

I have coloured formula to understand its different parts
part 1 (blue) finds value of D1 in A1:A20 and if value is not found ISERROR will return TRUE, if value is found ISERROR will return FALSE.

in the same way second part (red) finds value of D1 in B1:B20 an if not found ISERROR will return TRUE, if value is found ISERROR will return FALSE.

third part (green) finds value in C1:C20 if not found ISERROR will return TRUE, if value is found ISERROR will return FALSE.

AND function will return TRUE if above all three parts return TRUE and AND function will return FALSE if any of above parts returns FALSE.

finally IF function will return "" (blank) if AND function returns TRUE and value of D1 if AND function will return FALSE.

if you change the formula as you mentioned the resulting column will show those values which are found in any of previous ranges.

hope this will help you understand what that formula is.

Regards.