I have a large spreadsheet that I need to clean up. My data is kept by row,
so some rows have the same information in two adjacent cells. An example is
below in rows 2 through 5.

1 DB A HF SR SCR PI
2 SAW DB SR DR DB DB
3 SAW DB SR DR DB DB
4 SAW DB SR DR DB DB
5 SAW DB SR DR DB DB

Is there a function that can automatically delete the 2nd duplicate cell
contents? I can delete them manually one at a time, but I'd like
to be able to clean up the entire sheet at once.

Hi,

In 2007 choose the command Data, Remove Duplicates

In 2003 enter this formula in G1

=IF(SUMPRODUCT(--(B2=B$1:B2),--(C2=C$1:C2),--(D2=D$12),--(E2=E$1:E2),--(F2=F$1:F2))>1,TRUE,0)

and copy it down as far as your data. Then highlight this column and press
F5, Special, Formulas, and uncheck all except Logicals, click OK. Press
Ctrl+- (control minus) and respond Entire Row.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"PointerMan" wrote:

> I have a large spreadsheet that I need to clean up. My data is kept by row,
> so some rows have the same information in two adjacent cells. An example is
> below in rows 2 through 5.
>
> 1 DB A HF SR SCR PI
> 2 SAW DB SR DR DB DB
> 3 SAW DB SR DR DB DB
> 4 SAW DB SR DR DB DB
> 5 SAW DB SR DR DB DB
>
> Is there a function that can automatically delete the 2nd duplicate cell
> contents? I can delete them manually one at a time, but I'd like
> to be able to clean up the entire sheet at once.
>
>

Hi,

If I understand correctly then your sample data should end up looking like
this

DB A HF SR SCR PI
SAW DB SR DR DB DB

Try this sub

Sub Please_Delete_me()
Dim y As Long
Dim x As Variant
Dim MyRange As Range
Set MyRange = Application.Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Columns(ActiveCell.Column))
For y = MyRange.Rows.Count To 2 Step -1
x = MyRange.Cells(y, 1).Value
If Application.WorksheetFunction.CountIf(MyRange.Columns(1), x) > 1 Then
MyRange.Rows(y).EntireRow.Delete
End If
Next y
End Sub


Mike

"PointerMan" wrote:

> I have a large spreadsheet that I need to clean up. My data is kept by row,
> so some rows have the same information in two adjacent cells. An example is
> below in rows 2 through 5.
>
> 1 DB A HF SR SCR PI
> 2 SAW DB SR DR DB DB
> 3 SAW DB SR DR DB DB
> 4 SAW DB SR DR DB DB
> 5 SAW DB SR DR DB DB
>
> Is there a function that can automatically delete the 2nd duplicate cell
> contents? I can delete them manually one at a time, but I'd like
> to be able to clean up the entire sheet at once.
>
>

I think I was too vague in what I was wanting. Let me try to explain better.
I'll use the following data set as my example:

1 SCR SAW DB DR DB DB PI PI DB
2 OSC OSC PI PI DR DR PI DB DR

I'd like to remove the duplicates (except for the OSC cells) that are next
to each other so that these two rows would look like this:

1 SCR SAW DB DR DB PI DB
2 OSC OSC PI DR PI DB DR

I need to keep the OSC cell duplicates intact, but everything else would
delete one of the two adjacent cells.



"Mike H" wrote:

> Hi,
>
> If I understand correctly then your sample data should end up looking like
> this
>
> DB A HF SR SCR PI
> SAW DB SR DR DB DB
>
> Try this sub
>
> Sub Please_Delete_me()
> Dim y As Long
> Dim x As Variant
> Dim MyRange As Range
> Set MyRange = Application.Intersect(ActiveSheet.UsedRange, _
> ActiveSheet.Columns(ActiveCell.Column))
> For y = MyRange.Rows.Count To 2 Step -1
> x = MyRange.Cells(y, 1).Value
> If Application.WorksheetFunction.CountIf(MyRange.Columns(1), x) > 1 Then
> MyRange.Rows(y).EntireRow.Delete
> End If
> Next y
> End Sub
>
>
> Mike
>
> "PointerMan" wrote:
>
> > I have a large spreadsheet that I need to clean up. My data is kept by row,
> > so some rows have the same information in two adjacent cells. An example is
> > below in rows 2 through 5.
> >
> > 1 DB A HF SR SCR PI
> > 2 SAW DB SR DR DB DB
> > 3 SAW DB SR DR DB DB
> > 4 SAW DB SR DR DB DB
> > 5 SAW DB SR DR DB DB
> >
> > Is there a function that can automatically delete the 2nd duplicate cell
> > contents? I can delete them manually one at a time, but I'd like
> > to be able to clean up the entire sheet at once.
> >
> >

Pointerman,

just a hint ; i guess you may need to be more specific by having a subject
like *delete adjacent-duplicate inside each cell*

--
regards,

"PointerMan" wrote:

> I think I was too vague in what I was wanting. Let me try to explain better.
> I'll use the following data set as my example:
>
> 1 SCR SAW DB DR DB DB PI PI DB
> 2 OSC OSC PI PI DR DR PI DB DR
>
> I'd like to remove the duplicates (except for the OSC cells) that are next
> to each other so that these two rows would look like this:
>
> 1 SCR SAW DB DR DB PI DB
> 2 OSC OSC PI DR PI DB DR
>
> I need to keep the OSC cell duplicates intact, but everything else would
> delete one of the two adjacent cells.
>
>
>
> "Mike H" wrote:
>
> > Hi,
> >
> > If I understand correctly then your sample data should end up looking like
> > this
> >
> > DB A HF SR SCR PI
> > SAW DB SR DR DB DB
> >
> > Try this sub
> >
> > Sub Please_Delete_me()
> > Dim y As Long
> > Dim x As Variant
> > Dim MyRange As Range
> > Set MyRange = Application.Intersect(ActiveSheet.UsedRange, _
> > ActiveSheet.Columns(ActiveCell.Column))
> > For y = MyRange.Rows.Count To 2 Step -1
> > x = MyRange.Cells(y, 1).Value
> > If Application.WorksheetFunction.CountIf(MyRange.Columns(1), x) > 1 Then
> > MyRange.Rows(y).EntireRow.Delete
> > End If
> > Next y
> > End Sub
> >
> >
> > Mike
> >
> > "PointerMan" wrote:
> >
> > > I have a large spreadsheet that I need to clean up. My data is kept by row,
> > > so some rows have the same information in two adjacent cells. An example is
> > > below in rows 2 through 5.
> > >
> > > 1 DB A HF SR SCR PI
> > > 2 SAW DB SR DR DB DB
> > > 3 SAW DB SR DR DB DB
> > > 4 SAW DB SR DR DB DB
> > > 5 SAW DB SR DR DB DB
> > >
> > > Is there a function that can automatically delete the 2nd duplicate cell
> > > contents? I can delete them manually one at a time, but I'd like
> > > to be able to clean up the entire sheet at once.
> > >
> > >