SOLVED

Iron Contributor

# QUery related Excel formula

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)

Here is a attached file

12 Replies

# Re: QUery related Excel formula

Shouldn't you do your homework yourself?

# Re: QUery related Excel formula

Sir, I have tried but not getting correct answer.

# Re: QUery related Excel formula

What have you tried?

# Re: QUery related Excel formula

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 ?

# Re: QUery related Excel formula

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.

# Re: QUery related Excel formula

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

Then i will understood better.

Here is a attached file...

best response confirmed by Excel (Iron Contributor)
Solution

# Re: QUery related Excel formula

You haven't created even one formula...

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

# Re: QUery related Excel formula

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.

If you help me, so it will good.

# Re: QUery related Excel formula

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.

# Re: QUery related Excel formula

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

# Re: QUery related Excel formula

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

# Re: QUery related Excel formula

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.

1 best response

Accepted Solutions
best response confirmed by Excel (Iron Contributor)
Solution

# Re: QUery related Excel formula

You haven't created even one formula...

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