Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
LM LM is offline
external usenet poster
 
Posts: 21
Default Delete duplicates in a row

I have a list which contains seven digit numbers with around 30 columns and
3,000 rows approximately. Not all cells contain a number - some are blank.

I want to find duplicate numbers in each row and delete them.

For example, I have:

A B C D E
F
1234567 2345678 2584987 1234567 1234567
5896584 2584987 6598475 5896584 1234567 2584987
5897845 5897845 2584987

etc. ...

The numbers are in no particular order and may occur more than once in each
row. In some rows they may occur 20 or more times. They also recur
throughout the worksheet.

What I want to do is delete all the duplicates in each row only, i.e. in the
first row above, where the same number occurs three times, I want to get rid
of two of them and only leave one.

It doesn't matter how the cells end up in regard to the columns, as long as
the numbers stay in the same row, as each row will be concatenated into one
number eventually. Some rows may only have two numbers in them (in the first
two columns) but other rows may have anywhere up to 30 numbers. Each number
has seven digits.

I hope this makes sense and someone can come up with a formula to enable me
to do this easily.

Thank you in anticipation.

Lyn.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 44
Default Delete duplicates in a row

hi "LM"

this site is useful for you: www.cpearson.com/excel/duplicates.aspx

hope this helps.
nader

"LM" wrote:

I have a list which contains seven digit numbers with around 30 columns and
3,000 rows approximately. Not all cells contain a number - some are blank.

I want to find duplicate numbers in each row and delete them.

For example, I have:

A B C D E
F
1234567 2345678 2584987 1234567 1234567
5896584 2584987 6598475 5896584 1234567 2584987
5897845 5897845 2584987

etc. ...

The numbers are in no particular order and may occur more than once in each
row. In some rows they may occur 20 or more times. They also recur
throughout the worksheet.

What I want to do is delete all the duplicates in each row only, i.e. in the
first row above, where the same number occurs three times, I want to get rid
of two of them and only leave one.

It doesn't matter how the cells end up in regard to the columns, as long as
the numbers stay in the same row, as each row will be concatenated into one
number eventually. Some rows may only have two numbers in them (in the first
two columns) but other rows may have anywhere up to 30 numbers. Each number
has seven digits.

I hope this makes sense and someone can come up with a formula to enable me
to do this easily.

Thank you in anticipation.

Lyn.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,124
Default Delete duplicates in a row

try this idea
Sub nodupsinrow()
Application.ScreenUpdating = False
For r = 2 To cells(rows.Count,"a").end(xlup).row
On Error Resume Next
For mc = Cells(r, Columns.Count).End(xlToLeft).Column To 1 Step -1
With Range(Cells(r, 1), Cells(r, mc - 1))
Set c = .Find(Cells(r, mc), LookIn:=xlValues, lookat:=xlPrevious)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Cells(r, mc).Delete Shift:=xlToLeft
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Next mc
Next r
Application.ScreenUpdating = True
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"LM" wrote in message
...
I have a list which contains seven digit numbers with around 30 columns and
3,000 rows approximately. Not all cells contain a number - some are
blank.

I want to find duplicate numbers in each row and delete them.

For example, I have:

A B C D E
F
1234567 2345678 2584987 1234567 1234567
5896584 2584987 6598475 5896584 1234567 2584987
5897845 5897845 2584987

etc. ...

The numbers are in no particular order and may occur more than once in
each
row. In some rows they may occur 20 or more times. They also recur
throughout the worksheet.

What I want to do is delete all the duplicates in each row only, i.e. in
the
first row above, where the same number occurs three times, I want to get
rid
of two of them and only leave one.

It doesn't matter how the cells end up in regard to the columns, as long
as
the numbers stay in the same row, as each row will be concatenated into
one
number eventually. Some rows may only have two numbers in them (in the
first
two columns) but other rows may have anywhere up to 30 numbers. Each
number
has seven digits.

I hope this makes sense and someone can come up with a formula to enable
me
to do this easily.

Thank you in anticipation.

Lyn.


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 103
Default Delete duplicates in a row

Hi! LM

Think your data in A1 put this formula in B1

=if(A2=A1,"Deleted","ok")
If the data is shown three times two times it show DELETED AND ONE TIME OK

FILTER THE COLUMN A DELETE THE DATA OR ROW

"LM" wrote:

I have a list which contains seven digit numbers with around 30 columns and
3,000 rows approximately. Not all cells contain a number - some are blank.

I want to find duplicate numbers in each row and delete them.

For example, I have:

A B C D E
F
1234567 2345678 2584987 1234567 1234567
5896584 2584987 6598475 5896584 1234567 2584987
5897845 5897845 2584987

etc. ...

The numbers are in no particular order and may occur more than once in each
row. In some rows they may occur 20 or more times. They also recur
throughout the worksheet.

What I want to do is delete all the duplicates in each row only, i.e. in the
first row above, where the same number occurs three times, I want to get rid
of two of them and only leave one.

It doesn't matter how the cells end up in regard to the columns, as long as
the numbers stay in the same row, as each row will be concatenated into one
number eventually. Some rows may only have two numbers in them (in the first
two columns) but other rows may have anywhere up to 30 numbers. Each number
has seven digits.

I hope this makes sense and someone can come up with a formula to enable me
to do this easily.

Thank you in anticipation.

Lyn.

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
Delete duplicates? WH99 Excel Discussion (Misc queries) 2 April 16th 08 05:41 PM
Delete duplicates? kk Excel Discussion (Misc queries) 2 March 14th 08 03:22 PM
Delete Duplicates Lauren New Users to Excel 4 April 11th 06 06:46 AM
combine row and the delete duplicates bamamike Excel Discussion (Misc queries) 2 September 20th 05 05:16 PM
Delete duplicates Carter68 Excel Worksheet Functions 3 June 15th 05 01:01 AM


All times are GMT +1. The time now is 06:27 AM.

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

About Us

"It's about Microsoft Excel"