Excel - comparing two sheets

Resolved · High Priority · Version 2010

Tue 22 Nov 2016, 09:44 Delegate Cath said...

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.

Tue 22 Nov 2016, 10:12 Edited on Tue 22 Nov 2016, 10:13 Trainer Doug said...

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