• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

Three tables, need to use two to populate data in third

Hi there -
The attached file is mostly self explanatory, but I need help answering the question "Does the driver speak language of country s/he drove in?" by using a table that shows the drivers and where they drove, then another table that shows drivers and the language(s) each speaks.

Thank you in advance for any help you can offer! :)
Drivers.xlsx
0
AnalyticsTeam
Asked:
AnalyticsTeam
  • 4
  • 3
  • 2
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
Use this array formula in cell D2 and copy down:

=IF(SUM(($C$10:$C$14=TRANSPOSE($B$17:$B$20))*(A2=$A$10:$A$14)*(C2=TRANSPOSE($A$17:$A$20)))>0,"Yes","No")

Enter by pressing CTRL+SHIFT+ENTER.

See attached.

Kevin
Q-26945210.xlsx
0
 
Zack BarresseCEOCommented:
Can I ask you this, how much data do you have, or expect to have?  If you're looking at large amounts of data, I would recommend this go into an Access database.  You could write a simple query to return the results you're looking for there as well.

If you're not planning on moving this to Access, I would recommend separating the data a little more.  I don't know if this is your actual data or not.  I suspect it is not.  In any case, your data structure is going to become important, especially in larger quantities, so I recommend putting some thought into how you want this laid out.

@Kevin: heya!

Regards,
Zack
0
 
zorvek (Kevin Jones)ConsultantCommented:
Zack the firefighter paramedic dude!

Who needs Access when Excel can do anything Access can do and more? You don't like my array math? ;-)

Thank you again for the ride and lunch! Very much appreciated! Did you get to the car wash yet?

Smitty came by the other day with some wild stories about income sources and corporate greed. He also shared some of his recently harvested fruits and veggies.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Zack BarresseCEOCommented:
Haha!  I hear ya about Excel.  It's certainly my personal preference!  And I love your array math! :)

I did get the car washed, yes.  It needs it again though!  Hard to do with the twins these days.  They're both teething.  Oh the joys.

Smitty does have good harvests!
0
 
zorvek (Kevin Jones)ConsultantCommented:
You could have a rummage/garage sale and make enough enough money to hire a sitter, drive the 150 miles to civilization, and dine at a swanky restaurant with the wifey...just with the crap in that car!
0
 
Zack BarresseCEOCommented:
LOL!!  It's closer to 200 miles to civilization.  ;)  I'm working on those income sources anyway.  :)
0
 
AnalyticsTeamAuthor Commented:
Thanks guys, so very much.
There is a larger amount of data (you were right it's not real data :) but it's doubtful that it will be put into an Access database although I will recommend it.

I do have a follow up question though...if I have a driver who speaks two languages, and a country that has multiple languages, will your array math still return the correct result? I can send over another example if you'd like.
0
 
zorvek (Kevin Jones)ConsultantCommented:
The formula will work and you already have a case for that: James Profows speaks two languages and drove through Canada which speaks two languages.

Kevin
0
 
AnalyticsTeamAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now