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

Re: unique list from two tables

The free "Professional Compare" workbook can list unique items from multiple selections.

The unique list is placed in the first blank area to the right of the data.

Any part of the unique list can be copied and pasted.
See image below.

The "Professional Compare" workbook looks like this...

Download from OneDrive... https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

.

'---

NLtL

Answer 2

Hi SallyAP,

Here are two possible approaches. The first, shown in rows 1:5 requires employing PowerPivot. The second, below the first, would require some sort of intermediate area (here A8:B16) which produces the output in E7:F10. The second method is formula driven.

If you are interested in the above, let us know.

Answer 3

Hi Again,

Here is a third way using Power Query

This uses the same data as in my previous examples (from Sheet1), it generates two queries (sheets Table1 and Table2) and then appends them. Again let me know if this looks like an approach you would like to try.

Answer 4

Hi,

In the Query Editor, simply append TableA and TableB. Then Group by the First column and add the numbers from the second column. Transfer the result to a worksheet.

Hope this helps.

Answer 5

Here is the formula approach:

The formula in I2 is =Table1

The formula in I11 is =Table2

The formula in C11 is =UNIQUE(FILTER(I1:K20,I1:I20<>""))

The titles in I1:J1 are just copied. But the titles in C11:E11 are handled by the formula.

By setting the ranges in the FILTER function to row 20 it will automatically handle new rows of data.