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.

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.

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.

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.