comparing two rows, then highting?

[johnh]

I use excel to make up lists of keywords.

I enter a different word (or several words) to each row,
but as there can be hundreds of rows, I'm looking for a
quick way to identify (background colour perhaps) rows
with matching words (after doing an alphabetical sort,
so matching rows are together) so I can delete
the duplicates.

Am I being to ambitious?

Any help appreciated, oh.. KIS please )

Thanks in advance of any help and guidance you can give.

 

[SamuelT]

Use conditional formatting.

Format>Conditional Formatting

Choose 'Cell Value Is' Then 'Equal To' - put in the word you want
highlighted. Click the 'Format...' button and choose colours, etc.

Then use the Format Painter tool (paintbrush symbol) and drag the
formatting down the column with your words.

SamuelT

 

[johnh]

Use conditional formatting.

Format>Conditional Formatting

Choose 'Cell Value Is' Then 'Equal To' - put in the word you want
highlighted. Click the 'Format...' button and choose colours, etc.

Then use the Format Painter tool (paintbrush symbol) and drag the
formatting down the column with your words.

SamuelT

Thanks SamuelT,

Trouble is, I don't know what the words will be, as
they are random. If I knew what the words were I
could do a search.

Example

eggs
beans
beans chips
carrots
carrot
carrot
tomatoes

What I'm looking for, is the two lines (or at least
the one) of 'carrot' to be identified. There could be
many duplications

 

[SamuelT]

Hmmm.

So you have two columns of words and you want to find duplicates?

If this is the case, (and using A2 and B2 as examples) just do:

=IF(A2=B2,"Y","N")

Drag this formula down to the bottom of your list, then in this column
do the conditional formatting I mentioned earlier with anything that =
to go whatever colour you like.

That should do it.

Samuel

 

[johnh]

Hmmm.

So you have two columns of words and you want to find duplicates?

If this is the case, (and using A2 and B2 as examples) just do:

=IF(A2=B2,"Y","N")

Drag this formula down to the bottom of your list, then in this column,
do the conditional formatting I mentioned earlier with anything that =Y
to go whatever colour you like.

That should do it.

SamuelT

One column of words, each word on a separate row.
Only using column A

Example

eggs
beans
beans chips
carrots
carrot
carrot
tomatoes

What I'm looking for, is the two lines (or at least
the one) of 'carrot' to be identified. There could be
many duplications or pairs. So further down the
column, there could be cheese on one row, then
cheese again on the next row

 

[johnh]

The answer to my problem came from another group, and it is;


Assuming your keywords run from A1 downwards
In B1 enter =COUNTIF(A$1:A1,A1)>1
and fill this formula down column B.

Then use Data / Filter / AutoFilter and select TRUE in column B
The rows visible will all be duplicate entries.

Select the whole filtered table except for the first row.
Edit / Goto / Special / Visible Cells Only
Edit / Delete / Entire Row

Data / Filter / AutoFilter to reveal the remaining unique items.