sorting stuff out

[Guest]

Hello
i have a sheet with lots of rows and columns ie
fruit no. in europe no. in usa etc....
apples 1
apples 1
bananas 2
bananas 2
orange 5
orange 5

what i'd ilke is to create a new sheet containing just the first row of each new fruit, i.

fruit no. in europe no. in usa etc
apple 1
banana 2
orange 5

any ideas

ta

 

[Frank Kabel]

Hi
one way (not automatic but it may help you)
- at a helper column (lets say D) and enter the following in D2:
=IF(A2<>A1,"new fruit","")
copy this formula for all rows

Now apply a filter for your sheet 1 and use the helper column as filter
criteria. Copy the filtered data to a new sheet

 

[Guest]

thanks again, you are an excel wizar

 

[Leo Heuser]

Hello Nathan

An option without a helper column.
Tested in Excel 2002.

1. Activate the sheet, where you want the list
to be copied to (Important!).
2. Choose Data > Filter > Advanced filter
3. Click "Copy to another place" (or similar)
4. Click "Unique records only" (or similar)
5. Click in "List Range"(or similar) and select
the list in the source sheet with the mouse.
6. Click in "Criteria Range" and select an empty
cell with the mouse.
7. Click in "Copy to" and click the cell, where you
want the list to start.