Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Bleu_808
 
Posts: n/a
Default How to blank out cells with duplicate values?

Okay - after an hour on trying to work on what I thought was an easy
task 0 I must ask for help!

My column A is a list of order numbers, each order is separated by a
blank row...

ex:
1234
1234
1234
1234

4321
4321
4321

6789
6789

What I would like to do is delete the duplicate order numbers, without
affecting the rest of the row. I thought this might be a simple if
statement, but no. Any ideas?

Thanks in advance - Bleu

  #2   Report Post  
Posted to microsoft.public.excel.misc
Gary''s Student
 
Posts: n/a
Default How to blank out cells with duplicate values?

Try:
1. select the column
2. pull-down:
Data Filter : Advanced Filter and check unique records only

This should eliminate the duplicates

--
Gary's Student


"Bleu_808" wrote:

Okay - after an hour on trying to work on what I thought was an easy
task 0 I must ask for help!

My column A is a list of order numbers, each order is separated by a
blank row...

ex:
1234
1234
1234
1234

4321
4321
4321

6789
6789

What I would like to do is delete the duplicate order numbers, without
affecting the rest of the row. I thought this might be a simple if
statement, but no. Any ideas?

Thanks in advance - Bleu


  #3   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default How to blank out cells with duplicate values?

Some VBA

Sub test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Application.CountIf(Columns(1), Cells(i, "A")) 1 Then
Cells(i, "A").Delete Shift:=xlUp
End If
Next i

End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bleu_808" wrote in message
oups.com...
Okay - after an hour on trying to work on what I thought was an easy
task 0 I must ask for help!

My column A is a list of order numbers, each order is separated by a
blank row...

ex:
1234
1234
1234
1234

4321
4321
4321

6789
6789

What I would like to do is delete the duplicate order numbers, without
affecting the rest of the row. I thought this might be a simple if
statement, but no. Any ideas?

Thanks in advance - Bleu



  #4   Report Post  
Posted to microsoft.public.excel.misc
Bleu_808
 
Posts: n/a
Default How to blank out cells with duplicate values?

Thanks, but this affects the rest of the rows. I need to be able to
view the info in the row, just don't want to see this cell duplicated.
Unless I am doing it wrong???

  #5   Report Post  
Posted to microsoft.public.excel.misc
Bleu_808
 
Posts: n/a
Default How to blank out cells with duplicate values?

Bob, Thanks - but this did not work for me. Let me explain a little
better...

ex:
1234 Part #1
1234 Part #2
1234 Part #2
1234 Part #4


4321 Part #1
4321 Part #2
4321 Part #3


6789 Part #1
6789 Part #2

I still want to view the informationon the rest of the row that has
the duplicate number, I just want to clear only the cell of the
duplicate.

Sorry if I wasn't clear :)

Bleu



  #6   Report Post  
Posted to microsoft.public.excel.misc
Dominic
 
Posts: n/a
Default How to blank out cells with duplicate values?

Bleu,

You could do this using a helper column I believe.

Insert a column next to your order number column. Then beginning with the
second row type the formula =IF(A2="","",IF(A2=A1,"",A2)). Copy this formula
down the column.
Where column A is the column your order numbers are in.

Then copy the column, the paste:special:values over the top.

Is that what you are looking for?

"Bleu_808" wrote:

Bob, Thanks - but this did not work for me. Let me explain a little
better...

ex:
1234 Part #1
1234 Part #2
1234 Part #2
1234 Part #4


4321 Part #1
4321 Part #2
4321 Part #3


6789 Part #1
6789 Part #2

I still want to view the informationon the rest of the row that has
the duplicate number, I just want to clear only the cell of the
duplicate.

Sorry if I wasn't clear :)

Bleu


  #7   Report Post  
Posted to microsoft.public.excel.misc
Bryan Hessey
 
Posts: n/a
Default How to blank out cells with duplicate values?


Select cell A1, then select the A column (check that A1 is the active
cell, the odd highlight)
Format, Conditional Format, Formula is
=COUNTIF(A$1:A1,A1)<1
and set font colour to White (on white)

duplicates are then hidden

Hope this helps

--


Dominic Wrote:
Bleu,

You could do this using a helper column I believe.

Insert a column next to your order number column. Then beginning with
the
second row type the formula =IF(A2="","",IF(A2=A1,"",A2)). Copy this
formula
down the column.
Where column A is the column your order numbers are in.

Then copy the column, the paste:special:values over the top.

Is that what you are looking for?

"Bleu_808" wrote:

Bob, Thanks - but this did not work for me. Let me explain a little
better...

ex:
1234 Part #1
1234 Part #2
1234 Part #2
1234 Part #4


4321 Part #1
4321 Part #2
4321 Part #3


6789 Part #1
6789 Part #2

I still want to view the informationon the rest of the row that has
the duplicate number, I just want to clear only the cell of the
duplicate.

Sorry if I wasn't clear :)

Bleu




--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=537895

  #8   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default How to blank out cells with duplicate values?

Perhaps you mean this

Sub test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Application.CountIf(Columns(1), Cells(i, "A")) 1 Then
Cells(i, "A").Clearcontents
End If
Next i

End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bleu_808" wrote in message
oups.com...
Bob, Thanks - but this did not work for me. Let me explain a little
better...

ex:
1234 Part #1
1234 Part #2
1234 Part #2
1234 Part #4


4321 Part #1
4321 Part #2
4321 Part #3


6789 Part #1
6789 Part #2

I still want to view the informationon the rest of the row that has
the duplicate number, I just want to clear only the cell of the
duplicate.

Sorry if I wasn't clear :)

Bleu



  #9   Report Post  
Posted to microsoft.public.excel.misc
Bleu_808
 
Posts: n/a
Default How to blank out cells with duplicate values?

Bob,

This is exactly what I was looking for! Thank you!

Thank you everyone for your help too! It's much appreciated!

Bleu

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
Imported Data creates blank cells that aren't really blank JackieD Excel Worksheet Functions 14 February 23rd 06 01:57 AM
Blank Rows from Merged Cells in Drop Down Menu Kati Excel Discussion (Misc queries) 1 February 20th 06 08:59 PM
Macro to hide blank cells in a range Dave Excel Discussion (Misc queries) 1 February 2nd 06 12:55 AM
enter numbers in blank cells bill gras Excel Worksheet Functions 2 September 21st 05 01:17 PM
Function to list values of last 3 non-blank cells in a vertical bl ANJ Excel Worksheet Functions 1 April 14th 05 12:53 AM


All times are GMT +1. The time now is 02:47 PM.

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"