MrExcel Publishing
Your One Stop for Excel Tips & Solutions

REPLACE DUPLICATE CELLS WITH BLANKS


Posted by YANECKC on December 11, 2001 10:31 AM

I HAVE A SHEET WHERE THE FIRST COLUMN PRODUCT ID ARE
DUPLICATED. I JUST WANT TO PAD THE DUPLICATES WITH BLANKS OR SPACES.
========
BEFORE PADDED SPACES ON DUPLICATE CELLS

ID ACCOUNT
1123456 0991234519
1123456 0991234527
1123456 1234567890
1123456 2345678901
1123456 3456789012
2222222 0000000711
2222222 0000000810
3333333 0000000710
3333333 0991234519
3333333 0991234527
3333333 1236890646
3333333 2234567890
5555555 7678790754
6666666 0000000071
6666666 0000000711
6666666 0991234519
6666666 0991234527
7777777 0000000710
7777777 0000000810
7777777 0991234519
7777777 0991234527
7777777 1234567890
7777777 3456789098
9999999 5678799808

===============================
AFTER PADDED SPACES ON DUPLICATE CELLS

ID ACCOUNT
1123456 0991234519
0991234527
1234567890
2345678901
3456789012
2222222 0000000711
0000000810
3333333 0000000710
0991234519
0991234527
1236890646
2234567890
5555555 7678790754
6666666 0000000071
0000000711
0991234519
0991234527
7777777 0000000710
0000000810
0991234519
0991234527
1234567890
3456789098
9999999 5678799808

=================================

YANECKC


Posted by Aladin Akyurek on December 11, 2001 10:57 AM

Yaneckc --

Insert a new column after A and

in B2 enter and copy down: =IF(COUNTIF($A$1:A1,A2),"",A2)

Select all the cells of B from B2 on, copy it, activate A2, and activate Edit|Paste Special -> Values. Delete column B.

Aladin

Posted by YANECKC on December 11, 2001 5:34 PM

THANK YOU ALADIN THAT IS EXACTLY WHAT I WAS LOOKING FOR.