Identifying values that do not match in a common column

povictory

New Member
Joined
May 28, 2015
Messages
10
Hello - I'm very new to Power Pivot and I'm guessing there must be an easy solution to this that I'm just not aware of but I haven't had any luck finding out the best way to handle this.

I set up a relationship between two tables with a common column "Unit ID #".

The first table is the list of the unique unit Unit ID #'s with some additional descriptive data. There are about 250 rows for each unit and it is manually updated when new units are added.

The second table is a large data dump out of another database with multiple lines for each Unit ID #. There are 5,000+ rows in this table.

The pivot that I'm using basically merges the descriptive data from the first table to the data in the second table. The problem that I'm running into is that the second table could include brand new Unit ID #'s each month...usually there's not a lot but there could be 1-5 new ones each month. And since the first table with the unique Unit ID #'s is a table that needs to be manually updated when new units are added, the pivot isn't finding the unit ID #/descriptive data for the new units that have been added in the large table. They show up as blank.

So I guess my question is - is there anyway to some how highlight or quickly locate new unit numbers that been added in the large data table (second table) that *DO NOT* have a corresponding matching unit ID in the first table? Just looking for a relatively easy way to identify any new units from the large table to signal that they need to be manually added to the table with the unique list of units and from there I can refresh so that all are finding a match.

I hope my question makes sense! I appreciate any thoughts/input.

Chris
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,260
Yep. Create a pivot table.
place the iD from the unique table on the filter for the pivot
filter this for “blank”
place the id from the data table on rows in the pivot
place the id from the data into the values section too, and set it to count

keep this as an audit query. It will be empty when it is all good, and show the codes needing attention when there is an issue.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,812
Members
416,884
Latest member
leeshjay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top