A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How to blank out cells with duplicate values?



 
 
Thread Tools Display Modes
  #1  
Old May 1st 06, 11:51 PM posted to microsoft.public.excel.misc
external usenet poster
 
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

Ads
  #2  
Old May 2nd 06, 12:11 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 2nd 06, 12:20 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 2nd 06, 12:23 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 2nd 06, 12:27 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 2nd 06, 12:44 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 2nd 06, 01:28 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 2nd 06, 08:10 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 2nd 06, 10:16 PM posted to microsoft.public.excel.misc
external usenet poster
 
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 07:56 AM.


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