|
|
|
|
|
|
|
|
|
|
| |
If the duplicates don't have to be contiguous, use
=IF(AND(COUNTIF(B:B,B3)>1,COUNTIF(C:C,C3)>1),"delete", "don't delete")
and copy down
this will delete both duplicate lines. If you want to delete subsequent
duplicates then use
=IF(AND(COUNTIF(B$3:B3,B3)>1,COUNTIF(C$3:C3,C3)>1),"delete", "don't delete")
and then copy down
--
HTH
RP
(remove nothere from the email address if mailing direct)
"JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
news:eq51C6xJFHA.732@TK2MSFTNGP12.phx.gbl...
> Hi Kelly
>
> something along the lines of
> =IF(AND(B3=B4,C3=C4),"delete","don't delete")
>
> Cheers
> JulieD
>
> "Kelly Lacey" <Kelly Lacey@discussions.microsoft.com> wrote in message
> news:30769C6D-0DC9-4670-BF95-BE79BC8201F7@microsoft.com...
> > Using Excel 2000, I need to identify rows that have matching cells. For
> > example, B3=B4 and C3=C4. I have used the and/if function in the past
and
> > been able to add something to show me what was duplicated but it has
been
> > so
> > long I can't remember how. I have a delete macro but I need to see what
> > is
> > duplicated instead of just getting rid of the duplicates.
>
>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
|
|
| |
I never like the solutions offered which delete or hide duplicates - how many
were there? You loose visibility. Why this is not a standard Excel operator
I don't know.
This is another way I worked out a while ago, not as elegant as Bob's but
shows another function:
In this case we want to mark duplicate cells from C8 to C999.
In a next door column enter this or something logically similar in row 8:
=OR(NOT(ISNA(MATCH(C8,C9:C$999,FALSE))),NOT(ISNA(MATCH(C8,C$7:C10,FALSE))))
Where the ISNA function checks for repeated values within a range, this does
the ISNA check above and then below the current value line. Then returns the
OR of the answer. The NOT function is because the OR is looknig for TRUE
values.
Note the ranges start 1 below and 1 above the current row. Copy the formula
down in the usual way.
This does work, make sure you fix the correct values with the $ sign.
"Bob Phillips" wrote:
> If the duplicates don't have to be contiguous, use
>
> =IF(AND(COUNTIF(B:B,B3)>1,COUNTIF(C:C,C3)>1),"delete", "don't delete")
>
> and copy down
>
> this will delete both duplicate lines. If you want to delete subsequent
> duplicates then use
>
> =IF(AND(COUNTIF(B$3:B3,B3)>1,COUNTIF(C$3:C3,C3)>1),"delete", "don't delete")
>
> and then copy down
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
> news:eq51C6xJFHA.732@TK2MSFTNGP12.phx.gbl...
> > Hi Kelly
> >
> > something along the lines of
> > =IF(AND(B3=B4,C3=C4),"delete","don't delete")
> >
> > Cheers
> > JulieD
> >
> > "Kelly Lacey" <Kelly Lacey@discussions.microsoft.com> wrote in message
> > news:30769C6D-0DC9-4670-BF95-BE79BC8201F7@microsoft.com...
> > > Using Excel 2000, I need to identify rows that have matching cells. For
> > > example, B3=B4 and C3=C4. I have used the and/if function in the past
> and
> > > been able to add something to show me what was duplicated but it has
> been
> > > so
> > > long I can't remember how. I have a delete macro but I need to see what
> > > is
> > > duplicated instead of just getting rid of the duplicates.
> >
> >
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
This is a great solution, thanks for posting it! I had over 7,700 rows to
examine. Using conditional formatting to hightlight the "TRUE" occurences
made it even easier to spot the dupes.
"Andrewac" wrote:
> I never like the solutions offered which delete or hide duplicates - how many
> were there? You loose visibility. Why this is not a standard Excel operator
> I don't know.
>
> This is another way I worked out a while ago, not as elegant as Bob's but
> shows another function:
>
> In this case we want to mark duplicate cells from C8 to C999.
>
> In a next door column enter this or something logically similar in row 8:
>
> =OR(NOT(ISNA(MATCH(C8,C9:C$999,FALSE))),NOT(ISNA(MATCH(C8,C$7:C10,FALSE))))
>
> Where the ISNA function checks for repeated values within a range, this does
> the ISNA check above and then below the current value line. Then returns the
> OR of the answer. The NOT function is because the OR is looknig for TRUE
> values.
>
> Note the ranges start 1 below and 1 above the current row. Copy the formula
> down in the usual way.
>
> This does work, make sure you fix the correct values with the $ sign.
>
>
> "Bob Phillips" wrote:
>
> > If the duplicates don't have to be contiguous, use
> >
> > =IF(AND(COUNTIF(B:B,B3)>1,COUNTIF(C:C,C3)>1),"delete", "don't delete")
> >
> > and copy down
> >
> > this will delete both duplicate lines. If you want to delete subsequent
> > duplicates then use
> >
> > =IF(AND(COUNTIF(B$3:B3,B3)>1,COUNTIF(C$3:C3,C3)>1),"delete", "don't delete")
> >
> > and then copy down
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
> > news:eq51C6xJFHA.732@TK2MSFTNGP12.phx.gbl...
> > > Hi Kelly
> > >
> > > something along the lines of
> > > =IF(AND(B3=B4,C3=C4),"delete","don't delete")
> > >
> > > Cheers
> > > JulieD
> > >
> > > "Kelly Lacey" <Kelly Lacey@discussions.microsoft.com> wrote in message
> > > news:30769C6D-0DC9-4670-BF95-BE79BC8201F7@microsoft.com...
> > > > Using Excel 2000, I need to identify rows that have matching cells. For
> > > > example, B3=B4 and C3=C4. I have used the and/if function in the past
> > and
> > > > been able to add something to show me what was duplicated but it has
> > been
> > > > so
> > > > long I can't remember how. I have a delete macro but I need to see what
> > > > is
> > > > duplicated instead of just getting rid of the duplicates.
> > >
> > >
> >
> >
> > |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
A much simpler method is to insert a new column (B) alongside the duplicated
data and enter this formula in each cell of column B: =IF(A2=A3,"DUP","").
Then you can sort or filter on column B to read or delete the duplicated
range as a whole.
"KTomk" wrote:
> This is a great solution, thanks for posting it! I had over 7,700 rows to
> examine. Using conditional formatting to hightlight the "TRUE" occurences
> made it even easier to spot the dupes.
>
> "Andrewac" wrote:
>
> > I never like the solutions offered which delete or hide duplicates - how many
> > were there? You loose visibility. Why this is not a standard Excel operator
> > I don't know.
> >
> > This is another way I worked out a while ago, not as elegant as Bob's but
> > shows another function:
> >
> > In this case we want to mark duplicate cells from C8 to C999.
> >
> > In a next door column enter this or something logically similar in row 8:
> >
> > =OR(NOT(ISNA(MATCH(C8,C9:C$999,FALSE))),NOT(ISNA(MATCH(C8,C$7:C10,FALSE))))
> >
> > Where the ISNA function checks for repeated values within a range, this does
> > the ISNA check above and then below the current value line. Then returns the
> > OR of the answer. The NOT function is because the OR is looknig for TRUE
> > values.
> >
> > Note the ranges start 1 below and 1 above the current row. Copy the formula
> > down in the usual way.
> >
> > This does work, make sure you fix the correct values with the $ sign.
> >
> >
> > "Bob Phillips" wrote:
> >
> > > If the duplicates don't have to be contiguous, use
> > >
> > > =IF(AND(COUNTIF(B:B,B3)>1,COUNTIF(C:C,C3)>1),"delete", "don't delete")
> > >
> > > and copy down
> > >
> > > this will delete both duplicate lines. If you want to delete subsequent
> > > duplicates then use
> > >
> > > =IF(AND(COUNTIF(B$3:B3,B3)>1,COUNTIF(C$3:C3,C3)>1),"delete", "don't delete")
> > >
> > > and then copy down
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
> > > news:eq51C6xJFHA.732@TK2MSFTNGP12.phx.gbl...
> > > > Hi Kelly
> > > >
> > > > something along the lines of
> > > > =IF(AND(B3=B4,C3=C4),"delete","don't delete")
> > > >
> > > > Cheers
> > > > JulieD
> > > >
> > > > "Kelly Lacey" <Kelly Lacey@discussions.microsoft.com> wrote in message
> > > > news:30769C6D-0DC9-4670-BF95-BE79BC8201F7@microsoft.com...
> > > > > Using Excel 2000, I need to identify rows that have matching cells. For
> > > > > example, B3=B4 and C3=C4. I have used the and/if function in the past
> > > and
> > > > > been able to add something to show me what was duplicated but it has
> > > been
> > > > > so
> > > > > long I can't remember how. I have a delete macro but I need to see what
> > > > > is
> > > > > duplicated instead of just getting rid of the duplicates.
> > > >
> > > >
> > >
> > >
> > > |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
"Krish"
Thanks you very much, it did solve my problem.
"JJH." wrote:
> A much simpler method is to insert a new column (B) alongside the duplicated
> data and enter this formula in each cell of column B: =IF(A2=A3,"DUP","").
> Then you can sort or filter on column B to read or delete the duplicated
> range as a whole.
>
> "KTomk" wrote:
>
> > This is a great solution, thanks for posting it! I had over 7,700 rows to
> > examine. Using conditional formatting to hightlight the "TRUE" occurences
> > made it even easier to spot the dupes.
> >
> > "Andrewac" wrote:
> >
> > > I never like the solutions offered which delete or hide duplicates - how many
> > > were there? You loose visibility. Why this is not a standard Excel operator
> > > I don't know.
> > >
> > > This is another way I worked out a while ago, not as elegant as Bob's but
> > > shows another function:
> > >
> > > In this case we want to mark duplicate cells from C8 to C999.
> > >
> > > In a next door column enter this or something logically similar in row 8:
> > >
> > > =OR(NOT(ISNA(MATCH(C8,C9:C$999,FALSE))),NOT(ISNA(MATCH(C8,C$7:C10,FALSE))))
> > >
> > > Where the ISNA function checks for repeated values within a range, this does
> > > the ISNA check above and then below the current value line. Then returns the
> > > OR of the answer. The NOT function is because the OR is looknig for TRUE
> > > values.
> > >
> > > Note the ranges start 1 below and 1 above the current row. Copy the formula
> > > down in the usual way.
> > >
> > > This does work, make sure you fix the correct values with the $ sign.
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > If the duplicates don't have to be contiguous, use
> > > >
> > > > =IF(AND(COUNTIF(B:B,B3)>1,COUNTIF(C:C,C3)>1),"delete", "don't delete")
> > > >
> > > > and copy down
> > > >
> > > > this will delete both duplicate lines. If you want to delete subsequent
> > > > duplicates then use
> > > >
> > > > =IF(AND(COUNTIF(B$3:B3,B3)>1,COUNTIF(C$3:C3,C3)>1),"delete", "don't delete")
> > > >
> > > > and then copy down
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
> > > > news:eq51C6xJFHA.732@TK2MSFTNGP12.phx.gbl...
> > > > > Hi Kelly
> > > > >
> > > > > something along the lines of
> > > > > =IF(AND(B3=B4,C3=C4),"delete","don't delete")
> > > > >
> > > > > Cheers
> > > > > JulieD
> > > > >
> > > > > "Kelly Lacey" <Kelly Lacey@discussions.microsoft.com> wrote in message
> > > > > news:30769C6D-0DC9-4670-BF95-BE79BC8201F7@microsoft.com...
> > > > > > Using Excel 2000, I need to identify rows that have matching cells. For
> > > > > > example, B3=B4 and C3=C4. I have used the and/if function in the past
> > > > and
> > > > > > been able to add something to show me what was duplicated but it has
> > > > been
> > > > > > so
> > > > > > long I can't remember how. I have a delete macro but I need to see what
> > > > > > is
> > > > > > duplicated instead of just getting rid of the duplicates.
> > > > >
> > > > >
> > > >
> > > >
> > > > |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
Question
Answer
Notify me of replies