| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Keep non-duplicates only
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Keep non-duplicates only
Hi GMK,
Highlight your data then Dataadvance Filter check "Unique Records only" then hit OK HTH SImon GMK wrote: 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.a...tions/200610/1 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Keep non-duplicates only
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? "smw226 via OfficeKB.com" wrote: Hi GMK, Highlight your data then Dataadvance Filter check "Unique Records only" then hit OK HTH SImon GMK wrote: 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.a...tions/200610/1 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Keep non-duplicates only
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. "smw226 via OfficeKB.com" wrote: Hi GMK, Highlight your data then Dataadvance Filter check "Unique Records only" then hit OK HTH SImon GMK wrote: 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.a...tions/200610/1 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Keep non-duplicates only
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 GMK wrote: 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.a...tions/200610/1 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Keep non-duplicates only
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! "smw226 via OfficeKB.com" wrote: 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 GMK wrote: 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.a...tions/200610/1 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Keep non-duplicates only
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 GMK wrote: 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] Thanks. -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200610/1 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Keep non-duplicates only
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 smw226 wrote: 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] Thanks. -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200610/1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Checking for duplicates - think this is simple | Excel Discussion (Misc queries) | |||
| i need to find duplicates! ASAP | Excel Worksheet Functions | |||
| Please Help With Duplicates | Excel Discussion (Misc queries) | |||
| Help with Highlighting all duplicates in a row | Excel Discussion (Misc queries) | |||
| Finding Duplicates | Excel Worksheet Functions | |||
Linear Mode
