MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
I've got two spreadsheets, for June and July. They have a list of customer data and each customer has a unique agreement number. Some customers have had multiple agreements though. Both lists are 5000 rows long.
Somehow, 21 agreements are missing from the July data and I need to find which ones they are. Any ideas for a formula that will?
Quickest and dirtiest way if you are happy june is good would be to do a COUNTIF().
New column for June and then
countif(julycolumns",junefield)
If you get a zero its guilty.
So every line of data has a unique ie none repeated agreement number in the June list?
If so in a spare column on the June sheet
=VLOOKUP(B1,Sheet2!B:B,1,0)
B1 = agreement number June
Sheet2!B:B is the column of agreement numbers on the July sheet.
This should return a #N/A against any of the agreement numbers that are not appearing in the July data set
There is probably a better way, but the first that springs to mind is vlookup, which will throw an error for any in June that are not found in July.
the help button will explain it better than I can here.
edit: or indeed someone who can type faster will answer while I am writing!
number of different ways, as per above.
alternative way using conditional formatting, which make be easier if you aren't too confident with excel:
[url= https://www.extendoffice.com/documents/excel/3499-excel-highlight-cells-not-in-list.html#a1 ]link[/url]
What Funky Dunc says will do it, if you want something a bit smarter looking than an error, you can make the error into something else thusly:
=IF(ISERROR(VLOOKUP(B1,Sheet2!B:B,1,0))=TRUE,"This is the bugger",VLOOKUP(B1,Sheet2!B:B,1,0))
I think there's the right number of brackets but not got excel in front of me to check, soz
Aha, tried FunkyDunc's and I get the N/A errors. Brilliant, just brilliant. Thanks all of you. Will try yours edlong.
If using a recent version of Excel you can simplify Edlongs formula with =IFERROR(VLOOKUP(B1,Sheet2!B:B,1,0),"This is the bugger")