Deleting specific records from a column
 

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

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

wrote in message
ups.com...
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

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