Deleting specific records from a column

[[email protected]]

Hi All

I have a little problem. I have two sets of records - let's say for the
purpose of this exercise they are titled "a" and "b". The record sets
differ but do have many matching records. My objective is to extract
all the "b" records that match up with an equivalent record from the
"a" column. The final record set will have these "b" records and their
matching "a" records deleted from it. Example

Column a Column b Final column

earth earth sky
sky grain fire
fire metal air
rain rain
air heat

Hope I've explained it well enough

Any thoughts would be appreciated.

Cheers,
Mike

[Biff]

Hi!

Range of lists is A1:B5

In C1 enter this formula with the key combo of CTRL,SHIFT,ENTER

=INDEX(A$1:A$5,SMALL(IF(COUNTIF(B$1:B$5,A$1:A$5)=0,ROW(A$1:A$5)),ROW(1:1)))

Copy down until you get #NUM! errors meaning the data has been exhausted.

Biff

<[email protected]> wrote in message
news:[email protected]...
> Hi All
>
> I have a little problem. I have two sets of records - let's say for the
> purpose of this exercise they are titled "a" and "b". The record sets
> differ but do have many matching records. My objective is to extract
> all the "b" records that match up with an equivalent record from the
> "a" column. The final record set will have these "b" records and their
> matching "a" records deleted from it. Example
>
> Column a Column b Final column
>
> earth earth sky
> sky grain fire
> fire metal air
> rain rain
> air heat
>
> Hope I've explained it well enough
>
> Any thoughts would be appreciated.
>
> Cheers,
> Mike
>

[[email protected]]

Biff

Great formula and yes it worked well. However the processing effort is
enormous. I should have probably told you that the list wasn' that
simplistic and there were about 10,000 records to sort through. It got
there in the end though.

Much appreciated.

Mike