How to get a list of UNIQUE values from two different cell ranges

Question

Hi

I have a spreadsheet with two tables (and it needs to be in 2 separate tables). Both list subcontractors and employee hours. I have created a summary table where I want to list all the subcontractors from those two tables and sum the employee hours across both for each month. Subcontractors may appear in each table more than once. Here is an example of some data:

TABLE A

Subcontractor 1 5 hours

Subcontractor 12 5 hours

Subcontractor 1 2 hours

Subcontractor 1 3 hours

TABLE B

Subcontractor 12 5 hours

Subcontractor 3 11 hours

Subcontractor 1 10 hours

Subcontractor 12 20 hours

SUMMARY TABLE

Subcontractor 1 20 hours

Subcontractor 3 11 hours

Subcontractor 12 30 hours

Currently I have the full list of all subcontractors in the summary table and if the subcontractor is not used that particular month, their name still shows up in the summary table with 0 hours. However, I'd prefer to just show a list of the subcontractors that WERE used that month.

I know I could use the UNIQUE function on one of the tables (e.g. Table B) to bring back a list of the unique subcontractors from that table, but I can't figure out how to combine the subcontractors from both tables into the one range. I have tried naming the range and this doesn't work either.

Any suggestions would be gratefully appreciated!

Answer 1

Hi. Just to let you know, you can do this with just worksheet functions in 365.

For example, this one summarized 4 tables dynamically.

This new Excel is pretty neat.

Answer 2

Here are the detailed steps for a Power Query approach:

Select the first table and choose Data, Get Data, From Other Source, From Sheet. This will open Power Query with a screen like this:

Click Close & Load. This will add a sheet to the workbook called Table1. Repeat these steps for the second table.

Choose Data, Get Data, Combine Queries, Append. This will open the Append dialog box:

Choose a First table and a Second table, the order doesn't matter. Click OK. This opens Power Query with the two table appended.  Select the Name field and then click Group by.  This will bring up the following screen:

Name is the field you are grouping by.  Change the “New column name” to Total, change the “Operation” to Sum, change the “Column” to Hours and then click OK.

You are returned to the Power Query window where you can choose Close & Load. Excel adds another sheet, this time called Append with your desired results:

Answer 3

Hi,

May I know if above suggestions shared by community members can give any inspiration for your scenario?

Best Regards,

Mia