Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing on 2 worksheets
Hi,
I have 2 different Excel files with data. There are Numbers in Column 1 in both files, and names in Column 2. Other then writing VB script or buying a third party product is there an easier way to: Check to see what numbers in File 1 are not in File 2. If not, I can copy past the worksheet into the same file to have 2 worksheets in 1 file. If I do this, what would the formula be to check this and then output the numbers that are missing in column 1 between the 2 worksheets. To get fancy what if I wanted it to then tell me the name in Column B for the missing numbers. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing on 2 worksheets
See http://www.cpearson.com/excel/ListFunctions.aspx for ideas...
"Roman" wrote: Hi, I have 2 different Excel files with data. There are Numbers in Column 1 in both files, and names in Column 2. Other then writing VB script or buying a third party product is there an easier way to: Check to see what numbers in File 1 are not in File 2. If not, I can copy past the worksheet into the same file to have 2 worksheets in 1 file. If I do this, what would the formula be to check this and then output the numbers that are missing in column 1 between the 2 worksheets. To get fancy what if I wanted it to then tell me the name in Column B for the missing numbers. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing on 2 worksheets
Thanks but the information says the columns have to be the same length. The
2 columns I am trying to compare are different lengths.... Any other suggestions? "Sheeloo" wrote: See http://www.cpearson.com/excel/ListFunctions.aspx for ideas... "Roman" wrote: Hi, I have 2 different Excel files with data. There are Numbers in Column 1 in both files, and names in Column 2. Other then writing VB script or buying a third party product is there an easier way to: Check to see what numbers in File 1 are not in File 2. If not, I can copy past the worksheet into the same file to have 2 worksheets in 1 file. If I do this, what would the formula be to check this and then output the numbers that are missing in column 1 between the 2 worksheets. To get fancy what if I wanted it to then tell me the name in Column B for the missing numbers. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing on 2 worksheets
For simpilicity let us assume you have numbers and names in sheet 1 and sheet
2 of the same file. Enter this in C1 of Sheet2 and copy down =IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"Not in List","Found in List") This will put "Found in List" against those nos which are found in Sheet1 Col A. Assuming no is present in Sheet 1 then this will return the name against the number in Sheet 2 if entered in D1 and copied down =Vlookup(A1,Sheet1!A:B,2,False) Hope this gives you enough ideas to solve your problem... "Roman" wrote: Thanks but the information says the columns have to be the same length. The 2 columns I am trying to compare are different lengths.... Any other suggestions? "Sheeloo" wrote: See http://www.cpearson.com/excel/ListFunctions.aspx for ideas... "Roman" wrote: Hi, I have 2 different Excel files with data. There are Numbers in Column 1 in both files, and names in Column 2. Other then writing VB script or buying a third party product is there an easier way to: Check to see what numbers in File 1 are not in File 2. If not, I can copy past the worksheet into the same file to have 2 worksheets in 1 file. If I do this, what would the formula be to check this and then output the numbers that are missing in column 1 between the 2 worksheets. To get fancy what if I wanted it to then tell me the name in Column B for the missing numbers. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing on 2 worksheets
Hi,
Try this formula: =COUNTIF(Test.xls]Sheet1!$A$1:$A$6,A1) Copy it down. This formula returns 0 if the value in A1 is not found in the second sheet, otherwise it return the number of times the number is found. If this helps, please click the Yes button. -- Thanks, Shane Devenshire "Roman" wrote: Hi, I have 2 different Excel files with data. There are Numbers in Column 1 in both files, and names in Column 2. Other then writing VB script or buying a third party product is there an easier way to: Check to see what numbers in File 1 are not in File 2. If not, I can copy past the worksheet into the same file to have 2 worksheets in 1 file. If I do this, what would the formula be to check this and then output the numbers that are missing in column 1 between the 2 worksheets. To get fancy what if I wanted it to then tell me the name in Column B for the missing numbers. Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing on 2 worksheets
Thanks...
I added this line to the C1 cell on sheet 2, and it does not seem to work. All the cells are "not on list. I verified that on sheet 1 and sheet 2 A1 has 10001 as the number. =IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"not on list","found") What am I doing wrong? Thanks.... "Sheeloo" wrote: For simpilicity let us assume you have numbers and names in sheet 1 and sheet 2 of the same file. Enter this in C1 of Sheet2 and copy down =IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"Not in List","Found in List") This will put "Found in List" against those nos which are found in Sheet1 Col A. Assuming no is present in Sheet 1 then this will return the name against the number in Sheet 2 if entered in D1 and copied down =Vlookup(A1,Sheet1!A:B,2,False) Hope this gives you enough ideas to solve your problem... "Roman" wrote: Thanks but the information says the columns have to be the same length. The 2 columns I am trying to compare are different lengths.... Any other suggestions? "Sheeloo" wrote: See http://www.cpearson.com/excel/ListFunctions.aspx for ideas... "Roman" wrote: Hi, I have 2 different Excel files with data. There are Numbers in Column 1 in both files, and names in Column 2. Other then writing VB script or buying a third party product is there an easier way to: Check to see what numbers in File 1 are not in File 2. If not, I can copy past the worksheet into the same file to have 2 worksheets in 1 file. If I do this, what would the formula be to check this and then output the numbers that are missing in column 1 between the 2 worksheets. To get fancy what if I wanted it to then tell me the name in Column B for the missing numbers. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help in comparing two worksheets | Excel Discussion (Misc queries) | |||
Comparing two worksheets | Excel Discussion (Misc queries) | |||
Comparing two different Worksheets | Excel Worksheet Functions | |||
Comparing Two Worksheets for changes | Excel Discussion (Misc queries) | |||
Comparing 2 worksheets | Excel Worksheet Functions |