* * * * E x c e l h e l p * * * *?

Question

"imagine" I have a spread sheet which has for example 5 lines of text exactly the same, then another five lines exactly the same, and so on.
I want to to show all the lines of text ONLY ONCE, for example I want to be able to take out all the duplicate lines of text. If there was 50 lines with only 10 lines different, which is the easiest way to revove all duplicate lines without having to delete them manualy. I thought there was some kind of formula which you could ask if rows apear the same then delete all of them leaving only one line,so I would end up with 10 lines all different.

I have been driving myself mad over this one, and I am sure someone will make my day and save me hours of work.

I hope you can help, if you resolve this for me I will be the happiest man allive (sad aint I LOL)
Cheers XX

Answer 1

I understand your frustration ,I cant help.

Answer 2

Here is a possibility.

For this method, you need a blank space at the top of the list. (I will explain why a little later.) Ok, highlight all of your list including the blank space at the top. Go to the "Data" menu. In the "Filter" option, there is an extended menu. In that menu go to the "Advanced Filter" option. A message window will pop up. It basically talks about your selection and that the top cell acts as the label for the filter (this is why you need the blank space at the top, or else anything that doubles as that line won't get filtered out). Click OK. There are some options in that window. Make sure that the "Copy to another location" and "Unique records only" are selected. Now make sure that the cell selection is in the top box. Also make a cell selection for a location where you want the result to be copied to (make it almost as big as your original list so that it doesn't complain if it is too big for the location that you indicated). This is done in the bottom box. Click OK. This has made a copy of the unique items in your list. Now you have the list of original items and a list with all of the filtered items. There are some cells that have been hidden in this process. To show these go back to the "Data" menu and go to the "Filter" sub-menu. Click "Show All". This will show all of the rows that were hidden.

And there you will have a list of your unique items.

Answer 3

I don't know of a formula, but if you hold down your control key, you can click on the first duplicate line and drag to highlight the other three of that set. Then you click on the first duplicate line in the next set and drag, etc. Once all the unwanted duplicate lines are highlighted, just hit delete.

Answer 4

I might be wrong (I usually am - about everything), but I don't think you can delete using a special menu. The only way is to highlight 4 of the five rows you want to delete (click on the numbers on the left hand side, hold your left mouse button down and drag down the four rows) then press alt E and alt D (or right click mouse button and select delete).