|
Extract Rows Unique to Two Sheets
I have 2 spreadsheets with matching column headings. Each sheet has mutiple
rows that must be kept together. I need to find the rows where the value in the first column is unique to both sheets. The uniqueness must only be between the 2 sheets not within each sheet. Here's an example... SHEET #1: ID Last Name First Name Product BKGN5040 Jones Tom HM BKGN5040 Jones Tom UO CAV29010 Smith Kris OC DG742010 Lawson Sue MC EPC47010 Crowley Frank UO SHEET #2: ID Last Name First Name Product GZMPM020 King Carol MC JWSJ9020 White Robert MC LSVKV010 Aronson John UO LSVKV010 Aronson John OC CAV29010 Smith Kris OC LVVVJ020 Sibley Patty HM BKGN5040 Jones Tom HM FILTERED SHEET: ID Last Name First Name Product DG742010 Lawson Sue MC EPC47010 Crowley Frank UO GZMPM020 King Carol MC JWSJ9020 White Robert MC LSVKV010 Aronson John UO LSVKV010 Aronson John OC LVVVJ020 Sibley Patty HM I did a search on this site but couldn't find anything for this type of situation. Thanks. |
Extract Rows Unique to Two Sheets
I did check that site before coming here. Could only find instructions for
"lists" in one column, nothing for complete rows. Thanks anyway. Hopefully someone else reading this will know how to solve. "Gary L Brown" wrote: Take a look at Chip Pearson's website. Specifically... http://www.cpearson.com/excel/duplic...tractingUnique Not sure if this is what you want but worth a try. HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "Noreen" wrote: I have 2 spreadsheets with matching column headings. Each sheet has mutiple rows that must be kept together. I need to find the rows where the value in the first column is unique to both sheets. The uniqueness must only be between the 2 sheets not within each sheet. Here's an example... SHEET #1: ID Last Name First Name Product BKGN5040 Jones Tom HM BKGN5040 Jones Tom UO CAV29010 Smith Kris OC DG742010 Lawson Sue MC EPC47010 Crowley Frank UO SHEET #2: ID Last Name First Name Product GZMPM020 King Carol MC JWSJ9020 White Robert MC LSVKV010 Aronson John UO LSVKV010 Aronson John OC CAV29010 Smith Kris OC LVVVJ020 Sibley Patty HM BKGN5040 Jones Tom HM FILTERED SHEET: ID Last Name First Name Product DG742010 Lawson Sue MC EPC47010 Crowley Frank UO GZMPM020 King Carol MC JWSJ9020 White Robert MC LSVKV010 Aronson John UO LSVKV010 Aronson John OC LVVVJ020 Sibley Patty HM I did a search on this site but couldn't find anything for this type of situation. Thanks. |
Extract Rows Unique to Two Sheets
Use a "helper" column, say E,
In Sheet1!E2 put the fomrmula =COUNTIF(Sheet2!$A$2:$A$100,A2) and copy it down. Adjust the last row number as needed to include all data on Sheet2. In Sheet2!E2 put the formula =COUNTIF(Sheet1!$A$2:$A$100,A2) and again copy down. The rows where the formula shows a 0 are the ones that are unique (based on column A only, which is what you said) to the sheet containing the formula. You can use Autofilter on this column to see the rows with 0's and possible paste them to another location to create your "filtered" list. On Thu, 29 Dec 2005 09:35:06 -0800, Noreen wrote: I have 2 spreadsheets with matching column headings. Each sheet has mutiple rows that must be kept together. I need to find the rows where the value in the first column is unique to both sheets. The uniqueness must only be between the 2 sheets not within each sheet. Here's an example... SHEET #1: ID Last Name First Name Product BKGN5040 Jones Tom HM BKGN5040 Jones Tom UO CAV29010 Smith Kris OC DG742010 Lawson Sue MC EPC47010 Crowley Frank UO SHEET #2: ID Last Name First Name Product GZMPM020 King Carol MC JWSJ9020 White Robert MC LSVKV010 Aronson John UO LSVKV010 Aronson John OC CAV29010 Smith Kris OC LVVVJ020 Sibley Patty HM BKGN5040 Jones Tom HM FILTERED SHEET: ID Last Name First Name Product DG742010 Lawson Sue MC EPC47010 Crowley Frank UO GZMPM020 King Carol MC JWSJ9020 White Robert MC LSVKV010 Aronson John UO LSVKV010 Aronson John OC LVVVJ020 Sibley Patty HM I did a search on this site but couldn't find anything for this type of situation. Thanks. |
| All times are GMT +1. The time now is 07:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com