Excel: Long list, how to group items and keep order

[hallux]

Hello,
I have a long list like this in excel in one column:
red
red
red
white
white
green
green
white

and I would like to reduce the list to
red
white
green
white

and of course keep the order. Is there a group function that will handle that?

Thanks
Hallux

 

[Zathras]

I don't know why this hasn't been answered yet. There are a couple of features you could exploit:

"Advanced Data Filter" and "Pivot Table"

Both can be found under the Data menu and in the help file.

 

[01Patrik]

Well, a pivot table would do that, but wouldn't keep the order.

You can get around that by adding a column with numbers -like this for B2 (assuming you column is in A, and there's no header row):

=if(A2=A1,B1,B1+1)

Fill down. This way you'll have a column that assignes a new number every time the value in A changes.

If values are re-appering further down in list, use countif instead:

=if(countif($A$1:A2,A2)=1,A1,A1+1)

Fill down.

Then, in a separate sheet / wb, use index/match in combination with row to get your function:


=index(Sheet1!A1:B10,match(row(1:1),B1:B10,false),2)

Fill down.

If preferred, nest an ISERROR or something to remove error messages when the number passes the no of unique values.


// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[Zathras]

Sorry, I missed that you had duplicates that you wanted to keep. You could do something like this:

Assuming your data are in A2:A9 (with a column heading in A1), you can put this formula in B2:
[tt]
=IF(A2=A1,0,1)
[/tt]
and copy down thru B9. Then set an automatic data filter and select where the value (for column B) is 1. You can either stop there and view the results as filtered, or you can select the data that is showing and copy/paste to put it somewhere else.