compare two tables and find average

**bob112233** · 11-10-2022, 03:57 AM

Hi,

I have two tables which consists of columns titled last name, first name, department and rank as shown below.

Screenshot 2022-11-10 185217.png

As you can see from table 1 and 2, they include names of people with their department and rank. Some names appear on both tables, some only once.

What I would like to do is use the first name, last name and department from my result table to locate their rank from both tables and then find the average.

If the person only exists in one table then take the rank from that table.

For example, Alan John appears in both tables, so his average rank would be (10+9)/2 = 9.5.

I have attached my workbook as well.

Thank you

**sandy666** · 11-10-2022, 04:08 AM

with Power Query

Please Login or Register  to view this content.

**bob112233** · 11-10-2022, 04:17 AM

I'm not familiar with power query. where do I put the code?

**sandy666** · 11-10-2022, 04:23 AM

if structure of your real source tables is the same like in your example
Data - New Query - From Other Source - Blank Query
it will open PQ Editor - Advanced Editor - paste copied code there

but first you need to use Ctrl+T for each "Table" (make them Excel Tables)

if structure is different you need create M-code yourself

**HansDouwe** · 11-10-2022, 12:15 PM

For the whole table in 1 go, try this formula:

Formula:

Please Login or Register  to view this content.

I also tried a much simpler formula with AVERAGEIFS and even without BYROW and LAMBDA,
but that failed because I couldn't properly define the range parameters of the AVERAGEIFS.

As soon as I referred to an array instead of a range (with exactly the same size and values), the AVERAGEIFS started to spill and calculate incorrectly.

**alansidman** · 11-10-2022, 03:10 PM

Further to Sandy's directions.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

compare two tables and find average

LinkBack

Thread Tools

Rate This Thread

Display

compare two tables and find average

Re: compare two tables and find average

Re: compare two tables and find average

Re: compare two tables and find average

Re: compare two tables and find average

Re: compare two tables and find average

Thread Information

Users Browsing this Thread

Similar Threads

Compare two tables, find matches, and extract rows based on matches.

Compare 2 columns to find max date, then compare to 3rd column to find True or False

[SOLVED] Compare of 2 tables & find the differences

Compare two tables and find records without matches

Compare two tables and find only unmatching/remaining data

Compare two data tables and find a match, then return a column header from the first table

[SOLVED] Compare 2 Tables to find the PROFIT faster

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions