Excel rows

[lacmag3]

To explain the problem I'm having in Excel, imagine for example that I have a spreadsheet listing 1000 people and the cars they have owned (one row per person/make of car). Some will have owned more cars than others but I'm only intersted in those that ever owned Fords. I want to examine what other cars those Ford owners bought.
How can I easily identify and discard ALL rows for people who never owned a Ford?

[samak]

Try using filters

[China Doll]

Sort the spreadsheet by make of car and look along the rows. Assuming that informatin is available.

[chelle7272]

Is the make of car in it's own column? Then highlight all columns, go to data on the toolbar, then sort by column.

[lacmag3]

Sorting and filtering don't work (I think) since each will gather the "ford" rows together when in fact I want to see what other cars (rows) the ford owners had. I want to keep together all the rows of each person who owned a ford and dump the rows for those who never owned a ford.

p.s. The example I'm giving is a simplified one, for expalantion purposes. In reality there are many thousands of rows so eyeballing isn't really on.

[Prudie]

You keep saying rows but it's columns that you need to sort - assuming that you have name in first column and then a car in each subsequent column??

[chelle7272]

Surely sorting by column would give you a section of just Ford owners, with all other information relating to them still in place in the rows?

[samak]

What version of Excel are you using ?

[craft1948]

No it wouldn't be any good sorting, where's Chuck when you need a macro?

[Old_Geezer]

Control F, search for "ford", find all. Check them out by eye, one by one.

[lacmag3]

Only one name & car appears on each row e.g.
John, Mercedes
John, Ford
Mike, Renault
Mike, Peugeot
Ann, Peugeot
Ann, Ford
By bringing all the ford rows to the top, I will be left will lots of rows at the bottom (John 1, Mike 2 and Ann 1) and I can't tell which of these bottom rows I should keep or delete.

[craft1948]

Would you also need to know if the Ford was the first car they bought rather than say the third?

[lacmag3]

In answer to Craft1948.. having the sequence of ownership isn't vital but would be an added bonus (since a date is recorded for each)

[ChuckFickens]

Sorry..... Macros are not my thing, I'm not great with excel. But my first thought is you're using the wrong tool for the job, this sounds more suited to a database than a spreadsheet.

[samak]

Excel can deal with it perfectly. How it needs to be done depends on the version of Excel.

[samak]

If you don't like filters, try a pivot table.

[tamborine]

Find the Ford owners, color their space for easy recognition, delete t'others. Insert comment on remaining Ford peeps to record previous cars.

[samak]

Too manually intensive Tambo.

Try a pivot table, double-click on Ford and it should open a new tab with the Ford owners only on it. Max 1 minute.

[craft1948]

sort by ford, colour them red, re-sort by name

[tamborine]

would be pretty tho samak :)