VBA Dependent Picklists Coordinate "Grid"

[lonewolfe2015]

My data is in Table 1. My theoretical solution is in Table 2.

I've gone through it all by hand for my current data and developed a multi-tiered dependent picklist system using the coordinate "ID" tags and now I'm trying to reverse engineer a VBA script which will auto-populate my table full of the data to build the picklists.

And so far I've managed to accomplish that up to the dependent ID tags for Doc2 and Doc3.

What I need: A way for my script to identify that "D" is associated with "A" and therefore label "01" under "ID1" next to it. And subsequently perform the same process with E-I. G & H are associated with D (01 under ID2) and A (01 under ID1)... etc etc.

My thought process was to use the number of blank cells as a basic counting method. The data can extend to hundreds of rows and there's always the same repeating blank cells pattern to indicate the dependent picklist structure. D is dependent upon A; G and H are dependent upon D byway of A. The result of the picklists is that choosing A shows D and E, choosing D shows G and H. That works... now I just need my VBA to populate the tables for the formulas to take over from there.

I'm drawing a pretty big blank (no pun intended) on how is best to do this. Anyone have any suggestions on how I could auto populate each of the ID fields? I have an original datasheet that looks like Table 1, I have a modified datasheet that looks like Table 2 to work with.

Rough ideas/solutions are welcome, anything at this point is helpful.

Doc1	Doc2	Doc3
A	D	G
		H
	E
B	F	I
C

Doc1	ID1	Doc2	ID1	ID2	Doc3	ID1	ID2	ID3
A	01	D	01	01	G	01	01	01
B	02	E	01	02	H	01	01	02
C	03	F	02	03	I	02	03	03