Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing columns in 2 different worksheets, IF Function
Hi there,
I have two seperate worksheets, one contains external data related to all installs of our product in our customers stores. The other spreadsheet is an internal worksheet with all the information we have on all the stores. I need to see if there are any stores listed on the external worksheet that are not currently on our internal worksheet so that I can then copy the information over. Both worksheets use the same identifier and this is what I am trying to do the comparison on. I was told that the IF function could be used to do this, however, I cant get it to work. Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing columns in 2 different worksheets, IF Function
Laura, Try this in your external data sheet. Insert a column to the right of your identifying column. In this case I used A as the Company identifier. =IF(ISERROR(INDEX(Internal!$A$1:$A$11,MATCH(extern al!A3,Internal!$A$1:$A$11,0))),"Not Found", "Found") This will put Found or Not Found based on your list of internal id's. If you want, you can then conditionally format the identifiers to color the row if Not Found is populated. Select the entire row and FormatConditional Format Formula is: =$B2="Not Found", select your formatting. Use the format painter to copy this down all of your rows making it easier to identify the missing data. HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=513377 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing columns in 2 different worksheets, IF Function
I would bet that the =match() portion is the part that's doing all the real
work. maybe just checking that would be sufficient: =if(iserror(match(external!a3,internal!$a$1:$A$11, 0)),"Not found","Found") SteveG wrote: Laura, Try this in your external data sheet. Insert a column to the right of your identifying column. In this case I used A as the Company identifier. =IF(ISERROR(INDEX(Internal!$A$1:$A$11,MATCH(extern al!A3,Internal!$A$1:$A$11,0))),"Not Found", "Found") This will put Found or Not Found based on your list of internal id's. If you want, you can then conditionally format the identifiers to color the row if Not Found is populated. Select the entire row and FormatConditional Format Formula is: =$B2="Not Found", select your formatting. Use the format painter to copy this down all of your rows making it easier to identify the missing data. HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=513377 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing two worksheets with two columns each for duplicates | Excel Discussion (Misc queries) | |||
Comparing worksheets in Excel and highlighting changes | Excel Worksheet Functions | |||
Comparing 2 columns if they are the same | Excel Worksheet Functions | |||
analysing data from alternate columns using the countif function | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions |