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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

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 ?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

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