+ Reply to Thread
Results 1 to 6 of 6

comparing two rows, then highting?

  1. #1
    johnh
    Guest

    comparing two rows, then highting?

    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 :o)

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



  2. #2
    Valued Forum Contributor
    Join Date
    09-23-2005
    Location
    Bristol, UK
    MS-Off Ver
    2007
    Posts
    664
    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

  3. #3
    johnh
    Guest

    Re: comparing two rows, then highting?


    "SamuelT" <SamuelT.1ybz9a_1131709800.7899@excelforum-nospam.com> wrote in
    message news:SamuelT.1ybz9a_1131709800.7899@excelforum-nospam.com...
    >
    > 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
    >
    >
    > --
    > SamuelT
    > ------------------------------------------------------------------------
    > SamuelT's Profile:

    http://www.excelforum.com/member.php...o&userid=27501
    > View this thread: http://www.excelforum.com/showthread...hreadid=484243


    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




  4. #4
    Valued Forum Contributor
    Join Date
    09-23-2005
    Location
    Bristol, UK
    MS-Off Ver
    2007
    Posts
    664
    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

  5. #5
    johnh
    Guest

    Re: comparing two rows, then highting?


    "SamuelT" <SamuelT.1yc6fm_1131719100.9953@excelforum-nospam.com> wrote in
    message news:SamuelT.1yc6fm_1131719100.9953@excelforum-nospam.com...
    >
    > 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




  6. #6
    johnh
    Guest

    Re: comparing two rows, then highting?

    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.




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1