Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate data in multiple spreadsheets
I have 2 worksheets with rows of data. I need to compare the data in column
1, worksheet 1, to the data in column 1, worksheet 2. I need to find out if there is any of the data in column 1, worksheet 1 that is duplicate to column 1, worksheet 2. Can someone assist with this formula? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate data in multiple spreadsheets
In a helper column of sheet 1 (eg in H1), put this formula:
=IF(ISNA(MATCH(A1,Sheet2!A:A,0)),"Not present","Duplicated") then copy it down the full extent of the data in sheet 1. You can also put this formula in the helper column of sheet 2: =IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"Not present","Duplicated") and copy down. Now if you apply autofilter to the helper column and select "Duplicated" from the filter pull-down, you will see all the records that exist in both sheets. Alternatively, by selecting "Not present" from the filter pull-down you will see those records which are unique to that sheet. Hope this helps. Pete On Jun 27, 10:47*am, L wrote: I have 2 worksheets with rows of data. *I need to compare the data in column 1, worksheet 1, to the data in column 1, worksheet 2. *I need to find out if there is any of the data in column 1, worksheet 1 that is duplicate to column 1, worksheet 2. *Can someone assist with this formula? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate data in multiple spreadsheets
Thanks Paul. I tried the formula, but when I pull it down, it responds as
all is Duplicated. Can you see anything wrong with my formula? =IF(ISNA(MATCH(A$2:A$496,Sheet2!!A$2:A$2801,0)),"N ot present","Duplicated". I have 496 Rows in Sheet 1 and 2801 Rows in Sheet 2. Thanks. Linda "Pete_UK" wrote: In a helper column of sheet 1 (eg in H1), put this formula: =IF(ISNA(MATCH(A1,Sheet2!A:A,0)),"Not present","Duplicated") then copy it down the full extent of the data in sheet 1. You can also put this formula in the helper column of sheet 2: =IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"Not present","Duplicated") and copy down. Now if you apply autofilter to the helper column and select "Duplicated" from the filter pull-down, you will see all the records that exist in both sheets. Alternatively, by selecting "Not present" from the filter pull-down you will see those records which are unique to that sheet. Hope this helps. Pete On Jun 27, 10:47 am, L wrote: I have 2 worksheets with rows of data. I need to compare the data in column 1, worksheet 1, to the data in column 1, worksheet 2. I need to find out if there is any of the data in column 1, worksheet 1 that is duplicate to column 1, worksheet 2. Can someone assist with this formula? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate data in multiple spreadsheets
I tried it again...it works great! Thank you so much.
"Pete_UK" wrote: In a helper column of sheet 1 (eg in H1), put this formula: =IF(ISNA(MATCH(A1,Sheet2!A:A,0)),"Not present","Duplicated") then copy it down the full extent of the data in sheet 1. You can also put this formula in the helper column of sheet 2: =IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"Not present","Duplicated") and copy down. Now if you apply autofilter to the helper column and select "Duplicated" from the filter pull-down, you will see all the records that exist in both sheets. Alternatively, by selecting "Not present" from the filter pull-down you will see those records which are unique to that sheet. Hope this helps. Pete On Jun 27, 10:47 am, L wrote: I have 2 worksheets with rows of data. I need to compare the data in column 1, worksheet 1, to the data in column 1, worksheet 2. I need to find out if there is any of the data in column 1, worksheet 1 that is duplicate to column 1, worksheet 2. Can someone assist with this formula? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate data in multiple spreadsheets
I think you meant me, not Paul !!
Your formula should be: =IF(ISNA(MATCH(A2,Sheet2!!A$2:A$2801,0)),"Not present","Duplicated") You had missed a bracket off the end, and you want the first A2 to change to A3, A4 etc as you copy down - you want to see if the cell on that row matches ANY of the cells in Sheet2. If you used the full column reference Sheet2!A:A, you would not have to concern yourself with how many rows you had in each sheet. Hope this helps. Pete On Jun 27, 11:35*am, L wrote: Thanks Paul. *I tried the formula, but when I pull it down, it responds as all is Duplicated. *Can you see anything wrong with my formula? =IF(ISNA(MATCH(A$2:A$496,Sheet2!!A$2:A$2801,0)),"N ot present","Duplicated". I have 496 Rows in Sheet 1 and 2801 Rows in Sheet 2. Thanks. Linda "Pete_UK" wrote: In a helper column of sheet 1 (eg in H1), put this formula: =IF(ISNA(MATCH(A1,Sheet2!A:A,0)),"Not present","Duplicated") then copy it down the full extent of the data in sheet 1. You can also put this formula in the helper column of sheet 2: =IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"Not present","Duplicated") and copy down. Now if you apply autofilter to the helper column and select "Duplicated" from the filter pull-down, you will see all the records that exist in both sheets. Alternatively, by selecting "Not present" from the filter pull-down you will see those records which are unique to that sheet. Hope this helps. Pete On Jun 27, 10:47 am, L wrote: I have 2 worksheets with rows of data. *I need to compare the data in column 1, worksheet 1, to the data in column 1, worksheet 2. *I need to find out if there is any of the data in column 1, worksheet 1 that is duplicate to column 1, worksheet 2. *Can someone assist with this formula?- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate data in multiple spreadsheets
Our posts have crossed, but I'm happy that you got it to work. Thanks
for feeding back. Pete On Jun 27, 11:53*am, L wrote: I tried it again...it works great! *Thank you so much. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Data from Multiple Spreadsheets | Excel Worksheet Functions | |||
Consolidating data from multiple spreadsheets... | Excel Discussion (Misc queries) | |||
Linking data from multiple spreadsheets into one | Excel Worksheet Functions | |||
synchronizing data on multiple spreadsheets | Excel Worksheet Functions | |||
Merge Data from Multiple Spreadsheets | Excel Worksheet Functions |