Keep non-duplicates only

[GMK]

I have two multicolumn lists that contain a great amount of duplicate data. I
want to extract only rows that do not match any other row. Is there a
"simple" way to do this?

Thanks.

 

[smw226 via OfficeKB.com]

Hi GMK,

Highlight your data then Data>advance Filter

check "Unique Records only" then hit OK

HTH

SImon

I have two multicolumn lists that contain a great amount of duplicate data. I
want to extract only rows that do not match any other row. Is there a
"simple" way to do this?

Thanks.

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-functions/200610/1

 

[GMK]

Using the Advanced Filter retains one of the matching (duplicate) rows. I
want to eliminate both of these rows keeping only the new (truly unique) data
that do not match any other data.

Any suggestions?

Hi GMK,

Highlight your data then Data>advance Filter

check "Unique Records only" then hit OK

HTH

SImon

I have two multicolumn lists that contain a great amount of duplicate data. I
want to extract only rows that do not match any other row. Is there a
"simple" way to do this?

Thanks.

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-functions/200610/1

 

[GMK]

Using "Advanced Filter — Unique Records Only" does not accomplish my job.
"Unique Records Only" keeps one of the matching rows but I want only totally
new (unique) rows, discarding both of the matching rows. The resultant list
would contain only rows of new data and none of the matching rows from either
of the lists.

I hope this clarifies my request. Thanks, again.

Hi GMK,

Highlight your data then Data>advance Filter

check "Unique Records only" then hit OK

HTH

SImon

I have two multicolumn lists that contain a great amount of duplicate data. I
want to extract only rows that do not match any other row. Is there a
"simple" way to do this?

Thanks.

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-functions/200610/1

 

[smw226 via OfficeKB.com]

Hi GMK,

Sorry about that....try this.
I am not sure how many columns you have but you can, in cell D5,

=CONCATENATE(A5,B5,C5) ' This will get all of your rows into 1 cell.

then in cell E5

=COUNTIF($D$5:$D$10,D5) 'This will count how many times the result in D5
appears in your list

If you drag those down to the bottom of your report if there is a 1 in E5 you
have a unique record, it is more than 1, you have a duplicate.

HTH

SImon

Using "Advanced Filter — Unique Records Only" does not accomplish my job.
"Unique Records Only" keeps one of the matching rows but I want only totally
new (unique) rows, discarding both of the matching rows. The resultant list
would contain only rows of new data and none of the matching rows from either
of the lists.

I hope this clarifies my request. Thanks, again.

Hi GMK,

[quoted text clipped - 11 lines]

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-functions/200610/1

 

[GMK]

I still get far too many records, most of which are not the ones I want to
retain.

To clarify, I have a week old personnel list (±600) and a current personnel
list (±600). I am looking for a way to cull out all but personnel hired since
the last list. Since the majority of the employees appear on both lists, I
want to eliminate them or extract the new hires from the latest list. This
concatenation does not pull out only those records that have no match in
either list.

Thanks for trying!

Hi GMK,

Sorry about that....try this.
I am not sure how many columns you have but you can, in cell D5,

=CONCATENATE(A5,B5,C5) ' This will get all of your rows into 1 cell.

then in cell E5

=COUNTIF($D$5:$D$10,D5) 'This will count how many times the result in D5
appears in your list

If you drag those down to the bottom of your report if there is a 1 in E5 you
have a unique record, it is more than 1, you have a duplicate.

HTH

SImon

Using "Advanced Filter — Unique Records Only" does not accomplish my job.
"Unique Records Only" keeps one of the matching rows but I want only totally
new (unique) rows, discarding both of the matching rows. The resultant list
would contain only rows of new data and none of the matching rows from either
of the lists.

I hope this clarifies my request. Thanks, again.

Hi GMK,

[quoted text clipped - 11 lines]

Thanks.

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-functions/200610/1

 

[smw226 via OfficeKB.com]

Hi GMK,

In theory it should work but, lets have another go....

Try doing a Vlookup between the 2 sheets and any N/A's will be new ones

or

Pop your data into access and run a query on it where tbl1.name<>tbl2.name

Thanks,

Simon

I still get far too many records, most of which are not the ones I want to
retain.

To clarify, I have a week old personnel list (±600) and a current personnel
list (±600). I am looking for a way to cull out all but personnel hired since
the last list. Since the majority of the employees appear on both lists, I
want to eliminate them or extract the new hires from the latest list. This
concatenation does not pull out only those records that have no match in
either list.

Thanks for trying!

Hi GMK,

[quoted text clipped - 28 lines]

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-functions/200610/1

 

[smw226 via OfficeKB.com]

Forgot to ask...

Is there any unique fields (ie Personell number) because if there is, use
this as it is possible for 2 people to have the same name

Thanks,

Simon

Hi GMK,

In theory it should work but, lets have another go....

Try doing a Vlookup between the 2 sheets and any N/A's will be new ones

or

Pop your data into access and run a query on it where tbl1.name<>tbl2.name

Thanks,

Simon

I still get far too many records, most of which are not the ones I want to
retain.

[quoted text clipped - 13 lines]

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-functions/200610/1