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
