+ Reply to Thread
Results 1 to 5 of 5

Help with Highlighting all duplicates in a row

  1. #1
    Jimv
    Guest

    Help with Highlighting all duplicates in a row

    I am trying to highlight duplicates in a row and am using the explanation:

    Highlighting Duplicate Entries

    Our first task is to highlight the cells in Range1 that are duplicates. We
    use Excel's Conditional
    Formatting tool to accomplish this. First, highlight the entire Range1.
    Then, select the
    Conditional Formatting tool from the Format menu: Format->Conditional
    Formatting.
    Change the "Cell Value Is" option to "Formula Is" and enter the following
    formula in the
    formula text box:

    =IF(COUNTIF(Range1, A5)>1,TRUE,FALSE)

    Where A5 is the first cell in Range1. Then, click the Format button and
    select the font or
    color you want your cell formatted with. Finally, click OK. Duplicate
    entries in Range1 will be
    formatted as you selected. For example, if "Able" occurs twice in Range1,
    both
    occurrences of "Able" will appear highlighted.

    I am changing "A5" to the first cell in the row and still nothing happens.
    what am I doing wrong?

    thanks

    Jim


  2. #2
    Bob Phillips
    Guest

    Re: Help with Highlighting all duplicates in a row

    Select the range as stated, and assuming that we are highlighting duplicates
    in row 1 starting at a1, just use the formula

    =COUNTIF($1:$1,A1)>1

    this shows all the duplicates, first and subsequent times.

    If you just want to show subsequent times, use

    =COUNTIF($A1:A1,A1)>1

    I would use the latter

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Jimv" <Jimv@discussions.microsoft.com> wrote in message
    news:0CCAA585-F8FB-4CE6-80FF-141D914DE748@microsoft.com...
    > I am trying to highlight duplicates in a row and am using the explanation:
    >
    > Highlighting Duplicate Entries
    >
    > Our first task is to highlight the cells in Range1 that are duplicates. We
    > use Excel's Conditional
    > Formatting tool to accomplish this. First, highlight the entire Range1.
    > Then, select the
    > Conditional Formatting tool from the Format menu: Format->Conditional
    > Formatting.
    > Change the "Cell Value Is" option to "Formula Is" and enter the following
    > formula in the
    > formula text box:
    >
    > =IF(COUNTIF(Range1, A5)>1,TRUE,FALSE)
    >
    > Where A5 is the first cell in Range1. Then, click the Format button and
    > select the font or
    > color you want your cell formatted with. Finally, click OK. Duplicate
    > entries in Range1 will be
    > formatted as you selected. For example, if "Able" occurs twice in Range1,
    > both
    > occurrences of "Able" will appear highlighted.
    >
    > I am changing "A5" to the first cell in the row and still nothing happens.
    > what am I doing wrong?
    >
    > thanks
    >
    > Jim
    >




  3. #3
    Jimv
    Guest

    Re: Help with Highlighting all duplicates in a row

    Bob,

    What if I am working in a colum?

    "Bob Phillips" wrote:

    > Select the range as stated, and assuming that we are highlighting duplicates
    > in row 1 starting at a1, just use the formula
    >
    > =COUNTIF($1:$1,A1)>1
    >
    > this shows all the duplicates, first and subsequent times.
    >
    > If you just want to show subsequent times, use
    >
    > =COUNTIF($A1:A1,A1)>1
    >
    > I would use the latter
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Jimv" <Jimv@discussions.microsoft.com> wrote in message
    > news:0CCAA585-F8FB-4CE6-80FF-141D914DE748@microsoft.com...
    > > I am trying to highlight duplicates in a row and am using the explanation:
    > >
    > > Highlighting Duplicate Entries
    > >
    > > Our first task is to highlight the cells in Range1 that are duplicates. We
    > > use Excel's Conditional
    > > Formatting tool to accomplish this. First, highlight the entire Range1.
    > > Then, select the
    > > Conditional Formatting tool from the Format menu: Format->Conditional
    > > Formatting.
    > > Change the "Cell Value Is" option to "Formula Is" and enter the following
    > > formula in the
    > > formula text box:
    > >
    > > =IF(COUNTIF(Range1, A5)>1,TRUE,FALSE)
    > >
    > > Where A5 is the first cell in Range1. Then, click the Format button and
    > > select the font or
    > > color you want your cell formatted with. Finally, click OK. Duplicate
    > > entries in Range1 will be
    > > formatted as you selected. For example, if "Able" occurs twice in Range1,
    > > both
    > > occurrences of "Able" will appear highlighted.
    > >
    > > I am changing "A5" to the first cell in the row and still nothing happens.
    > > what am I doing wrong?
    > >
    > > thanks
    > >
    > > Jim
    > >

    >
    >
    >


  4. #4
    Bob Phillips
    Guest

    Re: Help with Highlighting all duplicates in a row

    Then use

    =COUNTIF($A$1:A1,A1)>1

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Jimv" <Jimv@discussions.microsoft.com> wrote in message
    news:34009024-1233-4CEF-B5B7-5405D1A200AA@microsoft.com...
    > Bob,
    >
    > What if I am working in a colum?
    >
    > "Bob Phillips" wrote:
    >
    > > Select the range as stated, and assuming that we are highlighting

    duplicates
    > > in row 1 starting at a1, just use the formula
    > >
    > > =COUNTIF($1:$1,A1)>1
    > >
    > > this shows all the duplicates, first and subsequent times.
    > >
    > > If you just want to show subsequent times, use
    > >
    > > =COUNTIF($A1:A1,A1)>1
    > >
    > > I would use the latter
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > >
    > > "Jimv" <Jimv@discussions.microsoft.com> wrote in message
    > > news:0CCAA585-F8FB-4CE6-80FF-141D914DE748@microsoft.com...
    > > > I am trying to highlight duplicates in a row and am using the

    explanation:
    > > >
    > > > Highlighting Duplicate Entries
    > > >
    > > > Our first task is to highlight the cells in Range1 that are

    duplicates. We
    > > > use Excel's Conditional
    > > > Formatting tool to accomplish this. First, highlight the entire

    Range1.
    > > > Then, select the
    > > > Conditional Formatting tool from the Format menu: Format->Conditional
    > > > Formatting.
    > > > Change the "Cell Value Is" option to "Formula Is" and enter the

    following
    > > > formula in the
    > > > formula text box:
    > > >
    > > > =IF(COUNTIF(Range1, A5)>1,TRUE,FALSE)
    > > >
    > > > Where A5 is the first cell in Range1. Then, click the Format button

    and
    > > > select the font or
    > > > color you want your cell formatted with. Finally, click OK. Duplicate
    > > > entries in Range1 will be
    > > > formatted as you selected. For example, if "Able" occurs twice in

    Range1,
    > > > both
    > > > occurrences of "Able" will appear highlighted.
    > > >
    > > > I am changing "A5" to the first cell in the row and still nothing

    happens.
    > > > what am I doing wrong?
    > > >
    > > > thanks
    > > >
    > > > Jim
    > > >

    > >
    > >
    > >




  5. #5
    Jimv
    Guest

    Re: Help with Highlighting all duplicates in a row

    Thanks Bob, that worked!

    "Bob Phillips" wrote:

    > Then use
    >
    > =COUNTIF($A$1:A1,A1)>1
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Jimv" <Jimv@discussions.microsoft.com> wrote in message
    > news:34009024-1233-4CEF-B5B7-5405D1A200AA@microsoft.com...
    > > Bob,
    > >
    > > What if I am working in a colum?
    > >
    > > "Bob Phillips" wrote:
    > >
    > > > Select the range as stated, and assuming that we are highlighting

    > duplicates
    > > > in row 1 starting at a1, just use the formula
    > > >
    > > > =COUNTIF($1:$1,A1)>1
    > > >
    > > > this shows all the duplicates, first and subsequent times.
    > > >
    > > > If you just want to show subsequent times, use
    > > >
    > > > =COUNTIF($A1:A1,A1)>1
    > > >
    > > > I would use the latter
    > > >
    > > > --
    > > >
    > > > HTH
    > > >
    > > > RP
    > > > (remove nothere from the email address if mailing direct)
    > > >
    > > >
    > > > "Jimv" <Jimv@discussions.microsoft.com> wrote in message
    > > > news:0CCAA585-F8FB-4CE6-80FF-141D914DE748@microsoft.com...
    > > > > I am trying to highlight duplicates in a row and am using the

    > explanation:
    > > > >
    > > > > Highlighting Duplicate Entries
    > > > >
    > > > > Our first task is to highlight the cells in Range1 that are

    > duplicates. We
    > > > > use Excel's Conditional
    > > > > Formatting tool to accomplish this. First, highlight the entire

    > Range1.
    > > > > Then, select the
    > > > > Conditional Formatting tool from the Format menu: Format->Conditional
    > > > > Formatting.
    > > > > Change the "Cell Value Is" option to "Formula Is" and enter the

    > following
    > > > > formula in the
    > > > > formula text box:
    > > > >
    > > > > =IF(COUNTIF(Range1, A5)>1,TRUE,FALSE)
    > > > >
    > > > > Where A5 is the first cell in Range1. Then, click the Format button

    > and
    > > > > select the font or
    > > > > color you want your cell formatted with. Finally, click OK. Duplicate
    > > > > entries in Range1 will be
    > > > > formatted as you selected. For example, if "Able" occurs twice in

    > Range1,
    > > > > both
    > > > > occurrences of "Able" will appear highlighted.
    > > > >
    > > > > I am changing "A5" to the first cell in the row and still nothing

    > happens.
    > > > > what am I doing wrong?
    > > > >
    > > > > thanks
    > > > >
    > > > > Jim
    > > > >
    > > >
    > > >
    > > >

    >
    >
    >


+ 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