Subquery

D

dc

I have a sheet with many rows and i need to filter out old versions of the
row in questions,

example

Mr Smith blah blah aaa bbbb cccc 1
Mr Smith blah blah bbb ccccc 2
Mr Smith aaa bbbb ccc 3

In other words each row is a version on what ever im looking at, How do i
get rid of the old versions i.e. number 1 and number 2 and keep version 3 ??

Hope this explains it ok

regards
 
B

Bob Phillips

In an adjacent column, and assuming that the name is in column A, version in
F, add this formula

=F1=MAX(IF($A$1:$A$1000=A1,$F$1:$F$1000))

it is an array formula, so commit with Ctrl-Shift-Enter.

Copy the formula down for all relevant rows.

Filter Column F (Data>Filter>Autofilter), select a value of False, then
delete visible rows.
 
D

dc

Thank you for that, im not sure its working or I have done it correctly
could i send u some data to explain what I mean ?>

regards
 
B

Bob Phillips

Feel free. The addy is

bob dot phillips at tiscali dot co dot uk

do the obvious
 
B

Bob Phillips

Got it. Put this formula in some spare column, say G3

=D3=MAX(IF($A$3:$A$1000=A3,$D$3:$D$1000))

then as before, copy the formula down for all relevant rows.

Filter Column F (Data>Filter>Autofilter), click the dropdown arrow, select
the value of False, then delete visible rows.
 
D

dc

SED00001672 FALSE TRUE 1
SED00001672 FALSE FALSE 2
SED00001672 FALSE FALSE 3
SED00001672 FALSE FALSE 4

that's the results i was getting for the first query and the second one you
sent was everything false except the very last record in the whole sheet, If
poss I want the forth version in this list to be the true one....

Sorry Bob for being very confusing...
 
D

dc

Can anyone help pwith this pls >???

Bob has given me the query but it works in opp way i want it to ...
 
Top