98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel - comparing two sheets
Excel - comparing two sheets
Resolved · High Priority · Version 2010
Tue 22 Nov 2016, 09:44Delegate 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:13Trainer 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
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Date and timeCTRL+ SEMICOLON then SPACE then CTRL+SHIFT+ SEMICOLON |
