Hello Everyone,
I have been scouring the forum for an answer to this question and have found some close topics but not quite what I am looking for.
My questions goes like this:
I have two tables (Literature and Reference)
Literature has two columns (Serial Number(unique) and Title)
Reference has three columns (Index(unique), SerialNumber and Reference)
Some sample data looks like this:
Serial Number Title
HD456KH45, Rome Zoning Plans for 180.
RTHS1964, Aquaducts: Who Needs'em?
SADA5464, Lessons From Alexandria
444642SSG, Roman Warhorses Added to Batallion
FAAG44, Senate Optimistic About Roman Construction Timeline
FDSKJH452, Labor Strikes in Rome Delays Aquaduct
002, Rome Built in a Day
003, Roman Construction Unlikely to Meet Deadline
456, Carthage Sends Envoy to Maximus
And:
Index Serial Number Ref001
1, HD456KH45, RTHS1964
2, HD456KH45, SADA5464
3, HD456KH45, FAAG44
4, SADA5464, FDSKJH452
5, RTHS1964, 002
6, 444642SSG, 456
7, FAAG44, 002
8, FAAG44, 003
9, FDSKJH452, 003
Please note the one to many relationship between Literature.Serial Number and Reference.SerialNumber.
After I create a one to many relationship my power pivot table returns all the Reference.SerialNumber values for each Literature.Serial Number record. My expectation was that I would only have the values returned that had a found record in the joined table. For example the power pivot yeilds:
+/- HD456KH45
FAAG44
FDSKJH452
IIR002
IIR003
IIR456
RTHS1964
SADA5464
Where I want it to yeild:
+/- HD456KH45
RTHS1964
SADA5464
FAAG44
I will say optimally it would yield multiple layers also but I am not sure if excel is capable of this idea:
+/- HD456KH45
- RTHS1964
002
+ SADA5464
+ FAAG44
Any information on whether or not excel is capable of this type of multilayer, value returned one to many relationship organization would be greatly appreciated.
PS I also posted this in an access forum as I can create the same type of setup in access but can't figure out the multiple layer drill down.
Bookmarks