Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Svea
 
Posts: n/a
Default How to get rid off duplicate items?


Hello!

I attached a xls file showing start and end of the project.

As you can see in END sheet there is no duplicate values in first
column.
Can I do it without help of pivot (or manually deleting every duplicate
:) )?

Thanks a lot!


+-------------------------------------------------------------------+
|Filename: EXAMPLE.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3911 |
+-------------------------------------------------------------------+

--
Svea
------------------------------------------------------------------------
Svea's Profile: http://www.excelforum.com/member.php...o&userid=28151
View this thread: http://www.excelforum.com/showthread...hreadid=476659

  #2   Report Post  
JMB
 
Posts: n/a
Default How to get rid off duplicate items?

Save your work before trying. This will delete the entire row of duplicate
items.
Select the range you want evaluated and run.

Sub DeleteDuplicates()
Dim Isect As Range
Dim x As Range
Dim RangeToDelete
Dim NoDupes As New Collection

On Error Resume Next
Set Isect = Application.Intersect(Selection, _
ActiveSheet.UsedRange)

For Each x In Isect
NoDupes.Add x.Value, CStr(x.Value)
If Err.Number < 0 Then
Err.Clear
If IsEmpty(RangeToDelete) Then
Set RangeToDelete = x
Else: Set RangeToDelete = Union(RangeToDelete, x)
End If
End If
Next x

If Not IsEmpty(RangeToDelete) Then _
RangeToDelete.EntireRow.Delete

End Sub

"Svea" wrote:


Hello!

I attached a xls file showing start and end of the project.

As you can see in END sheet there is no duplicate values in first
column.
Can I do it without help of pivot (or manually deleting every duplicate
:) )?

Thanks a lot!


+-------------------------------------------------------------------+
|Filename: EXAMPLE.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3911 |
+-------------------------------------------------------------------+

--
Svea
------------------------------------------------------------------------
Svea's Profile: http://www.excelforum.com/member.php...o&userid=28151
View this thread: http://www.excelforum.com/showthread...hreadid=476659


  #3   Report Post  
Svea
 
Posts: n/a
Default How to get rid off duplicate items?


Thank you a lot. But it seems that it didn't work. I put your program
under Macros and run it. Firstly I selected the range. Nothing
happend.

Any other solutions?

So I have something like:

A.......................B
x.......................15
x.......................25
x.......................22,5
y.......................30
y.......................32
z........................11
z........................12
z........................13

and would like to delete all duplicates from A column, but B column has
to stay completely same. Column A is for example name of the products,
and column B is the price. We have the same product under many
different prices. And would like to mention the name of the product
only when first mentioned in the table.

Please help.


--
Svea
------------------------------------------------------------------------
Svea's Profile: http://www.excelforum.com/member.php...o&userid=28151
View this thread: http://www.excelforum.com/showthread...hreadid=476659

  #4   Report Post  
JMB
 
Posts: n/a
Default How to get rid off duplicate items?

So you want something like:

A.......................B
x.......................15
.......................25
.......................22,5
y.......................30
.......................32
z........................11
........................12
........................13

You should be able to copy/paste this modified code into a VBA module,
select column A and run the macro.

Sub DeleteDuplicates()
Dim Isect As Range
Dim x As Range
Dim NoDupes As New Collection

On Error Resume Next
Set Isect = Application.Intersect(Selection, _
ActiveSheet.UsedRange)

For Each x In Isect
NoDupes.Add x.Value, CStr(x.Value)
If Err.Number < 0 Then
Err.Clear
x.Value = ""
End If
Next x

End Sub


Alternatively (and probably easier), you could insert a new column to the
left of Column A, and enter this formula in cell A2 (since your duplicate
data is next to each other) and copy down the length of your table. Then
select A2 through A(whatever your last row is) and Copy. Then select cell B2
and click Edit/Paste Special - Values to hardcode the data. Then delete Col
A as it is no longer needed.

=IF(B2=B1,"",B2)

OR, if it is possible your data has extra leading/trailing spaces (making B2
not equal B1) then:

=IF(TRIM(B2)=TRIM(B1),"",B2)


"Svea" wrote:


Thank you a lot. But it seems that it didn't work. I put your program
under Macros and run it. Firstly I selected the range. Nothing
happend.

Any other solutions?

So I have something like:

A.......................B
x.......................15
x.......................25
x.......................22,5
y.......................30
y.......................32
z........................11
z........................12
z........................13

and would like to delete all duplicates from A column, but B column has
to stay completely same. Column A is for example name of the products,
and column B is the price. We have the same product under many
different prices. And would like to mention the name of the product
only when first mentioned in the table.

Please help.


--
Svea
------------------------------------------------------------------------
Svea's Profile: http://www.excelforum.com/member.php...o&userid=28151
View this thread: http://www.excelforum.com/showthread...hreadid=476659


  #5   Report Post  
Svea
 
Posts: n/a
Default How to get rid off duplicate items?


Dear JMB,

Thanks a lot. Alternative metode solved my problem. :)

Regarding program you write, it also does its job, but with small bug.
It also deletes the values in column B - if it finds duplicate!

Thanks again!

Bye,
Svea


--
Svea
------------------------------------------------------------------------
Svea's Profile: http://www.excelforum.com/member.php...o&userid=28151
View this thread: http://www.excelforum.com/showthread...hreadid=476659



  #6   Report Post  
JMB
 
Posts: n/a
Default How to get rid off duplicate items?

Yes, the first version deleted then entire row if it found a duplicate in Col
A, but the second version does not - provided you only select ColA. If you
select more than one column, it will delete any duplicates found in those
columns as well.

"Svea" wrote:


Dear JMB,

Thanks a lot. Alternative metode solved my problem. :)

Regarding program you write, it also does its job, but with small bug.
It also deletes the values in column B - if it finds duplicate!

Thanks again!

Bye,
Svea


--
Svea
------------------------------------------------------------------------
Svea's Profile: http://www.excelforum.com/member.php...o&userid=28151
View this thread: http://www.excelforum.com/showthread...hreadid=476659


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Table: How can I organize data items in a row? [email protected] Excel Discussion (Misc queries) 2 October 7th 05 07:55 PM
How do I find duplicate rows in a list in Excel, and not delete it Matthew in FL Excel Discussion (Misc queries) 2 June 15th 05 09:11 PM
multiple items in database Peter Excel Worksheet Functions 2 June 15th 05 08:19 PM
Computing totals for tax and non-tax items Dan Wilson Excel Worksheet Functions 2 March 31st 05 01:05 AM
How do I do a count sum that ignores duplicate items Robin Faulkner Excel Worksheet Functions 2 February 17th 05 12:53 PM


All times are GMT +1. The time now is 11:21 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"