SOLVED

QUery related Excel formula

Super Contributor

Hello Everyone, 

 

Heads Data 2 shows the same information as the original Heads Data 1 however:

I have 3 question :

 

1 --  Two employees are missing. Who are they?

 

2 --  The job title of one employee has changed from 'CRA - 2' to 'Senior CRA - 1'. Who is it?

 

3 --  One employee has got married and their surname has changed. Who is it?

 

(Note - You can see all questions in TASK sheet)

 

Please help

 

 

Here is a attached file 

 

12 Replies

@Excel 

Shouldn't you do your homework yourself?

Sir, I have tried but not getting correct answer.

Please help.

@Excel 

What have you tried?

I have tried the pivot table but problem is that i have to always change manually data then refresh the pivot table. 


Can we do with the help of formula ?

Please help..

@Excel 

a) You can use a COUNTIFS formula to check whether the first name/last name combination on Heads Data 1 occurs on Heads Data 2. If this formula returns 0, the employee is missing on the second sheet.

b) You can use an INDEX/MATCH formula to return the Job Description from Heads Data 1 on the Heads Data 2 sheet. You can then check whether it has changed.

c) You can also use INDEX/MATCH or VLOOKUP to return the surname (last name) corresponding to the first name from Heads Data 1 on the Heads Data 2 sheet. You can then check whether the surname has changed.

Sir, i am not making it.
Can you please write formula in attached file ??
Please...

Then i will understood better.

 

 

Here is a attached file...

best response confirmed by Excel (Super Contributor)
Solution

@Excel 

You haven't created even one formula...

 

You cannot expect us to do all your homework for you.

I am learning Microsoft Excel and VBA.
It is not a homework question.
So i have learnt basic things about functions, but not in a advanced level.

Please give me solution with attached file, so i will learn new things.
Please...

If you help me, so it will good.

@Excel 

I am learning Microsoft Excel and VBA.
It is not a homework question.

 

What is it, if not a homework question? I.e., a question you're expected to work to answer as part of that "learning Excel and VBA" that you are doing?

 

What have you tried so far on your own? @Hans Vogelaar  gave some initial guidance on formulas/functions you could employ. We might be inclined to offer even further help, but you need to show SOME effort here beyond repeated pleas for help.

 

Please give me solution with attached file, so i will learn new things.

 

For what it's worth, I'm pretty good at Excel, but I can't just look at your spreadsheet and come up with a formula. I'd have to play around a bit, try this or that. That happens to be the way I learn how. It could  (and should) be the way you learn as well. You don't really learn--not REALLY--by having somebody else write the solution for you; you LEARN by working through trial and error. You're not going to break your computer or the Excel program by approaching this through trial and error, and you'll probably find that you learn even through things that don't work.

 

So have fun. Play with the functions @Hans Vogelaar has already given you.

I learnt more details of COUNTIFS, INDEX with MATCH function, then i got the result.
I am very sorry sir

Sir, i got it.
I learnt more details of COUNTIFS, INDEX with MATCH function, then i got the result.
I am very sorry sir

@Excel 

I learnt more details of COUNTIFS, INDEX with MATCH function, then i got the result.

 

And wasn't that--getting the result yourself--far more satisfying than if @Hans Vogelaar had written out the formula for you?

 

Well done! Congratulations. Learning by doing.