mapping data dependencies with excel

valuechained

Board Regular
Joined
Oct 24, 2007
Messages
63
Hi experts,

I wonder whether you could help me with a particularly tricky problem that I havent been able to solve for a while now.

I have an excel index of various data base sets. The excel spreadsheet maps out

1) the name of the data set

2) the primary key of the data set (or sometime two primary keys that concatenate into a new one)

3) the secondary key of the data sets

Naturally, if I change the primary key of one data set, I would need to change another data set that contains this primary key as a secondary key. To complicate things, some data sets have identical primary keys.

I would like to build a functionality that looks through this table and displays in a cell next to the data set name, the names of the data sets that would need to be changed if I made change to its primary key.

I have build a short table. The column that I would like to generate is highlighted in blue (I did it manually).

Thank you guys, any thoughts would be highly appreciated! Best :)
question.xls
ABCDEFGHIJKLM
2NamePrimaryKey1PrimaryKey2SecondaryKey1Secondarykey2Achangeintheprimarykeyofthisdatasetaffectschangesinthesets:
3Dataset1KeyXKeyOKeyTDataset3,Dataset4
4Dataset2KeyYKeyXKeyPDataset1,Dataset3,Dataset4
5Dataset3KeyOKeyXKeyYDataset1
6Dataset4KeyOKeyYDataset1
Sheet1
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
can you clarify your example ?

reading through your post:

Naturally, if I change the primary key of one data set, I would need to change another data set that contains this primary key as a secondary key. To complicate things, some data sets have identical primary keys.

Your examples don't seem to follow the above rule... eg DataSet 1 has primary key's of X & O - you state that a change here would impact 3 & 4 whereas from what I can see only 3 has one of X/O as it's secondary key... 4 has O as it's primary key but that doesn't follow the above note.

It seems reading through your other examples you mean to highlight any sets that would be impacted either as a result of shared primary key or secondary key ?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,200
Messages
5,835,948
Members
430,396
Latest member
dzifna

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