Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Imported Data creates blank cells that aren't really blank | Excel Worksheet Functions | |||
Blank Rows from Merged Cells in Drop Down Menu | Excel Discussion (Misc queries) | |||
Macro to hide blank cells in a range | Excel Discussion (Misc queries) | |||
enter numbers in blank cells | Excel Worksheet Functions | |||
Function to list values of last 3 non-blank cells in a vertical bl | Excel Worksheet Functions |