Cath has attended:
Excel Intermediate course
Excel - comparing two sheets
More detail on the previous question: a. How can I best compare two lists of organisations to find out which appear on one sheet (called 2017)and which appear on the other (called 2016 funding? b. the lists of organisations are both part of slightly larger data sets showing around 5 different funding streams (in columns)- how do I compare funding streams across the two years of data to find out which organisations got and didn't get funding each year for which funding stream? I asked and showed the sheet to the trainer at Excel intermediate yesterday but we couldn't solve it.
RE: Excel - comparing two sheets
Hi Cath
THanks for giving more detail o your question.
Question a.
Suppose you have data as follows
On Sheet 2016 Funding
Organisation Funding
Org 1 1000
Org 2 1000
Org 3 3000
Org 4 1000
Org 5 1000
Org 6 1000
Org 7 2000
Org 8 1000
Org 9 1000
Org 10 1000
Org 11 2500
Org 12 1000
Org 13 1000
Org 14 1000
On Sheet 2017
Organisation Funding Existing
Org 4 3000 Yes
Org 5 3000 Yes
Org 6 3000 Yes
Org 21 3000 No
Org 8 3000 Yes
Org 1 3000 Yes
Org 20 2000 No
The following formula evaluates whether the organization name on 2017 exists on the 2016 Funding sheet:
=IF(ISNA(VLOOKUP(A2,'2016 funding'!A:A,1,FALSE)),"No","Yes")
Your question b. is more involved so I've sent this for now and will reply again.
Regards
Doug
STL
