MrExcel Publishing
Your One Stop for Excel Tips & Solutions

clearing duplicate data in multiple columns after sorting


Posted by Pete on August 23, 2000 8:22 PM

My original problem was that I needed to sort the columns a,b,c without moving everything up.

A B C
1|Beverages |Colas |Sprite
2| | |Pepsi
3| | |Coke
4| |Juice |Orange
5| | |Apple
6|Meats |Chicken|Friers
7| | |Cuts
8| |Beef |Steaks
9| | |Hamburger

The only way to sort is to fill in the blanks with repeating info. I found the handy function of selecting the range, then Go To/Special/Blanks then in a2 enter =a1 then CNTL ENTER. Copy the range and paste values to rid the formula. Then sort accordingly.

A B C
1|Beverages |Colas |Coke
2|Beverages |Colas |Pepsi
3|Beverages |Colas |Sprite
4|Beverages |Juice |Apple
5|Beverages |Juice |Orange
6|Meats |Beef |Hamburger
7|Meats |Beef |Steak

After sorting the range correctly, now I want to remove (actually 'clear') the repeating cells and restore the original format.

Is there a non-macro way to do this? If a macro is required, any ideas on a variable ranges?


Posted by Pete on August 30, 0100 4:46 PM

Celia,

Please see the above example as to what happens when you do leave the formulas in, then sort. If your precedent cells don't end up in the uppermost position for a data grouping, the formulas will copy the wrong information.

Pete

Posted by Pete on August 29, 0100 4:50 PM

________A________B______C


Ivan,
Thanks for the hint. I had actually tried this originally and the following results occurred:

List after applying formulas to blank cells:

Soups____|veg
Soups____|beef
Soups____|chicken
Soups____|chicken
Soups____|stars
Beverages|pop
Beverages|cola

After sorting with Soups|Veg and Beverages|pop as the precedent cells, the following results occur with a sort:

#REF!____|cola
Beverages|pop
Beverages|beef
Beverages|beef
Beverages|chicken
Beverages|stars
Soups____|veg

The result should have been:

Beverages|cola
Beverages|pop
Soups____|beef
Soups____|chicken
Soups____|chicken
Soups____|stars
Soups____|veg


I guess the only way around this is with a macro???

Pete

Posted by Ivan Moala on August 26, 0100 4:53 PM

________A________B______C

Pete
Instead of seleting the formulas you
could leave them in then sort, the
GOTO / Special / Formulas then DELETE.


Ivan

Posted by Celia on August 29, 0100 6:26 PM

But have you tried Ivan's suggestion? It looks like that should work.
Celia

Posted by Celia on August 30, 0100 7:06 PM

Pete
Here's a way without a macro :-
1.After you've filled the blanks, converted to values and sorted, copy cells A1:C1 to cells D1:F1
2.Enter the following formula in cell D2 and fill it across to cell F2 and down columns D:F
=IF(A2=A1,"",A2)
3.Convert columns D:F to values
4.Delete columns A:C

Celia

Posted by Pete on August 23, 0100 8:37 PM

Sorry, the text field didn't do my original example justice...

________A________B______C