Removing Duplicate Data
 

I see similar posts for this have already been put on but my question is a
bit different

I need to find a way to remove duplicate data in only certain columns

Below is an example (example 1) as you can see the third and fourth line are
duplicated except for the last column so I want to remove duplicate data in
the first three columns only and keep the stucture of the data to stay the
same so it would look like example 2

example 1
155200 21/01/2005 [Limerick]
155200 21/01/2005 [Limerick]
154597 20/01/2005 [Tipperary] 336628 &
154597 20/01/2005 [Tipperary] 336629
154569 20/01/2005 [Cork] 336633
154578 20/01/2005 [Cork] 336634 &

example 2
155200 21/01/2005 [Limerick]
154597 20/01/2005 [Tipperary] 336628 &
336629
154569 20/01/2005 [Cork] 336633
154578 20/01/2005 [Cork] 336634 &

any ideas??

Thanks
Jai Cutmore

This can be done using a pivot table. Sounds complicated,
but is quick and easy...

1) Select block of data, including headings
2) Data/Pivot Table
3) Next
4) Next
5) Layout button
6) Drag field buttons for the four columns you want into
the Rows area.
7) Drag another field into the Data region (doesn't matter
which one as long as it has data all the way down the
column
8) Click Finish
9) Right-click anywhere in the first column of the pivot
table and select Fields Settings from the shortcut menu
10) Select option None under Subtitles.
11) Repeat 9+10 for 2nd and 3rd columns

Should now look how you want so either leave as it is or
copy and paste into a blank sheet and then delete the
pivot table.

Cheers,
Dave

-----Original Message-----
I see similar posts for this have already been put on but
my question is a
bit different

I need to find a way to remove duplicate data in only
certain columns

Below is an example (example 1) as you can see the third
and fourth line are
duplicated except for the last column so I want to remove
duplicate data in
the first three columns only and keep the stucture of the
data to stay the
same so it would look like example 2

example 1
155200 21/01/2005 [Limerick]
155200 21/01/2005 [Limerick]
154597 20/01/2005 [Tipperary] 336628 &
154597 20/01/2005 [Tipperary] 336629
154569 20/01/2005 [Cork] 336633
154578 20/01/2005 [Cork] 336634 &

example 2
155200 21/01/2005 [Limerick]
154597 20/01/2005 [Tipperary] 336628 &
336629
154569 20/01/2005 [Cork] 336633
154578 20/01/2005 [Cork] 336634 &

any ideas??

Thanks
Jai Cutmore

.

Jai, I have a macro that can do just what you need. This macro assumes that
your data starts on A1 and is 4 columns wide. If the set-up is different,
then it is a simple fix to modify the number of columns involved.

Sub remove_dupes()
Range("A2").Select
x = 257
Do Until ActiveCell = ""
Cells(x).Select
If Cells(x) = Cells(x - 256) Then
If Cells(x + 1) = Cells(x - 255) Then
If Cells(x + 2) = Cells(x - 254) Then
Cells(x).Clear
Cells(x + 1).Clear
Cells(x + 2).Clear
End If
End If
End If
x = x + 256
Cells(x).Select
Loop

End Sub

"Jai Cutmore" wrote:

I see similar posts for this have already been put on but my question is a
bit different

I need to find a way to remove duplicate data in only certain columns

Below is an example (example 1) as you can see the third and fourth line are
duplicated except for the last column so I want to remove duplicate data in
the first three columns only and keep the stucture of the data to stay the
same so it would look like example 2

example 1
155200 21/01/2005 [Limerick]
155200 21/01/2005 [Limerick]
154597 20/01/2005 [Tipperary] 336628 &
154597 20/01/2005 [Tipperary] 336629
154569 20/01/2005 [Cork] 336633
154578 20/01/2005 [Cork] 336634 &

example 2
155200 21/01/2005 [Limerick]
154597 20/01/2005 [Tipperary] 336628 &
336629
154569 20/01/2005 [Cork] 336633
154578 20/01/2005 [Cork] 336634 &

any ideas??

Thanks
Jai Cutmore