Home |
Search |
Today's Posts |
#1
|
|||
|
|||
using VLOOKUP to compare 2 columns to find new information
I have 2 worksheets, one of them contains a large number of accounts and then
the second one has new figures that I can add to the first worksheet. Sometimes the second sheet will have new accounts on them but it is mixed in with the other 4,000 so its very difficult to find the new accounts. Right now Im copying and pasting the account numbers and comparing them to the ones on the first sheet. Is there anything I can do to make searching for new accounts easier? Possibly using a VLOOKUP function to compare the account numbers to the ones on the master sheet to find new accounts? |
#2
|
|||
|
|||
In the second worksheet, assuming the accounts are in column A, in column B,
add =IF(COUNTIF(Sheet1!$A:$A,B1)=0,"New","") and copy down will show the new accounts -- HTH RP (remove nothere from the email address if mailing direct) "Jessica" wrote in message ... I have 2 worksheets, one of them contains a large number of accounts and then the second one has new figures that I can add to the first worksheet. Sometimes the second sheet will have new accounts on them but it is mixed in with the other 4,000 so its very difficult to find the new accounts. Right now I'm copying and pasting the account numbers and comparing them to the ones on the first sheet. Is there anything I can do to make searching for new accounts easier? Possibly using a VLOOKUP function to compare the account numbers to the ones on the master sheet to find new accounts? |
#3
|
|||
|
|||
Make a helper column at the end of your second sheet (the one that contains new accounts) and do this: =IF(ISERROR(MATCH(A1,Sheet1!A$1:K$4000,0)),"new"," ") (Copy it down) Hope it helps. Jessica Wrote: I have 2 worksheets, one of them contains a large number of accounts and then the second one has new figures that I can add to the first worksheet. Sometimes the second sheet will have new accounts on them but it is mixed in with the other 4,000 so its very difficult to find the new accounts. Right now Im copying and pasting the account numbers and comparing them to the ones on the first sheet. Is there anything I can do to make searching for new accounts easier? Possibly using a VLOOKUP function to compare the account numbers to the ones on the master sheet to find new accounts? -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=388133 |
#4
|
|||
|
|||
Hi,
Assuming data is in A3:A5 in sheet 1 and in A3:A5 of sheet 2 (Names1). In B1 of sheet 1, enter the following array formula (Ctrl+Shift+Enter) =OR(EXACT(A3,Names1!$A$3:A5)) The repeated ones will be called TRUE else FALSE Regards "Jessica" wrote: I have 2 worksheets, one of them contains a large number of accounts and then the second one has new figures that I can add to the first worksheet. Sometimes the second sheet will have new accounts on them but it is mixed in with the other 4,000 so its very difficult to find the new accounts. Right now Im copying and pasting the account numbers and comparing them to the ones on the first sheet. Is there anything I can do to make searching for new accounts easier? Possibly using a VLOOKUP function to compare the account numbers to the ones on the master sheet to find new accounts? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to compare the lists in two columns & separate odd one ? | Excel Discussion (Misc queries) | |||
compare two columns and remove duplicates | Excel Worksheet Functions | |||
Excel Compare values in columns & display missing values in a new | Excel Discussion (Misc queries) | |||
using vlookup to find exact match | Excel Discussion (Misc queries) | |||
How do i use vlookup to find more than 1 entry | Excel Worksheet Functions |