Merging variables from two separate tables into a separate table

Good day,

My First posting on this forum!

I have a table with driver details, and a separate table with vehicle details. The vehicles aren't always driven by the same driver.

I need to issue Trip authorities to drivers, and I was wondering how to go about getting a result table showing collated data from the driver and vehicles tables into a new table.

Greatly appreciate any help in solving this for me.

Thanks in advance.

Quintin

Sorry, not sure how to attach file.

Newby can't post dropbox link to file either.

Basically: One table has vehicle details, second has driver details, third links driver with vehicle.

Need forth table to populate combing details from first two as a result of third table.

Thanks again.

Driver and Vehicle Plate Number are unique in first two tables.

Go Advanced (bottom of thread)
Additional Options Box (bottom of post reply box)
Attachments, click manage attachments
Upload from computer or wherever, click Upload
Close window,
Then Submit Reply

I've attached file now.

Top section is Vehicle details. KZN Reg unique for vehicle
Middle section link driver with vehicle
Bottom section Driver details- Surname unique.

Hope this makes sense.

Thanks in advance.

Quintin

Originally Posted by AMoreno

Go Advanced (bottom of thread)
Additional Options Box (bottom of post reply box)
Attachments, click manage attachments
Upload from computer or wherever, click Upload
Close window,
Then Submit Reply

Thanks. Great help.

Originally Posted by Zer0Cool

Generally, you need to create or establish some sort of relationship between the 2 tables/data sets. Without a sample file its hard to say exactly how you should do this as we have no reference to work from.

Hopefully, you have a unique identifier or can combine multiple fields to produce one. This would give a way to relate the data on both sheets and consolidate them.

I usually explain the concept this way;

You can make Excel practically do anything in which a measurable pattern is present. If x and y are here do z...If however the logic is that you've worked there for 20 years and you "just know" that x matches z "because", thats not something you can usually get Excel (or a computer in general) to follow and understand.

So we need something on each sheet that makes a record on one sheet and a record on another sheet match, or be related. Without that its arbitrary. It can be as simple as a single column with unique ID's or it can be a complex relationship based on various criteria.

I've attached a sample file now! Three sections: Driver; Vehicle and link for consolidation.

Please assist me.

Thank you

Quintin

Originally Posted by Zer0Cool

Generally, you need to create or establish some sort of relationship between the 2 tables/data sets. Without a sample file its hard to say exactly how you should do this as we have no reference to work from.

Hopefully, you have a unique identifier or can combine multiple fields to produce one. This would give a way to relate the data on both sheets and consolidate them.

I usually explain the concept this way;

You can make Excel practically do anything in which a measurable pattern is present. If x and y are here do z...If however the logic is that you've worked there for 20 years and you "just know" that x matches z "because", thats not something you can usually get Excel (or a computer in general) to follow and understand.

So we need something on each sheet that makes a record on one sheet and a record on another sheet match, or be related. Without that its arbitrary. It can be as simple as a single column with unique ID's or it can be a complex relationship based on various criteria.

I've attached a file now, Please assist.

Thanks in advance.

Quintin

Originally Posted by Zer0Cool

Can you please elaborate on what you have and what you want to accomplish? I looked at the sample and its not clear if this is the format you work with or the result you want to accomplish or both.

Ideally, a sample file should:

• Replicate the structure and types of data in your actual file
• Illustrate what you have and what your goal is
• Include any notes/comments that may help others understand the file

It would also be helpful to describe things here as it relates to the sample file, IE:

" I have a table for drivers in A1:B10 on Sheet1..."

vs

"I have a table of drivers and want..."

In doing the above we can more easily follow your description and understand exactly what needs to be manipulated in the file.

Ok. The first table (Vehicle Details) is A1:C8
Second table (Links Driver to Vehicle) is A10:B17
Third Table (Driver Details) is A19:F26

The output result (a fourth table) should have the drivers details from the Third table, and populate the Vehicle details according to the vehicle allocated in the second table. When I open the document, I wish to allocate vehicles to drivers, and the Output result "Does the rest"

Not the best at explaining, hopefully this makes sense.

Thanks.

Quintin

Originally Posted by Zer0Cool

Can you please elaborate on what you have and what you want to accomplish? I looked at the sample and its not clear if this is the format you work with or the result you want to accomplish or both.

Ideally, a sample file should:

• Replicate the structure and types of data in your actual file
• Illustrate what you have and what your goal is
• Include any notes/comments that may help others understand the file

It would also be helpful to describe things here as it relates to the sample file, IE:

" I have a table for drivers in A1:B10 on Sheet1..."

vs

"I have a table of drivers and want..."

In doing the above we can more easily follow your description and understand exactly what needs to be manipulated in the file.

I've named the First Table Vehicles
The second table Link
Third Table Drivers.

I've also added an example of the desired outcome!

you can do that very quickly using PowerQuery (aka Get&Transform built-in to Excel 2016 Pro)

Originally Posted by Zer0Cool

See attached.

Its just 3 VLOOKUPS. We do a VLOOKUP on the Surname and pull the KZN Reg number from the link table (A10:B17), then use the resulting KZN Reg number as the 1st arguement of other VLOOKUPs to pull data from the Vehicles table (A1:C8).

I have placed the results in H19:J26. The easiest method is to just append these formulas to your Drivers table (A19:F26) or copy/paste that table and add the formulas to it. I placed them next to it.

Perfect. Thanks a million.