Have an issue- can this be solved with a formula or does it need coding?

[dframe44]

Heres my problem-
I have a sheet1 with 'parent ID's' in Column A and 'Asset IDs' in Column B, and a sheet2 with 'child IDs' In column A and 'Parent IDs' in column B.
I need to populate a new worksheet with 3 columns- one with the Child IDs, one with the matching parent ID's, and one with the matching Product ID's.
I thought an Index-Match would solve this, but the main issue is that there are multiple 'Assets' per 'Parent', and I need index-match to create multiple child entries. For example,

Worksheet 1

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

Parent ID	Asset ID
123	xyz
123	abc
123	def
123	ghi
456	jkl
456	mno
456	pqr

<tbody>
</tbody>

Worksheet 2

Child ID	Parent ID
789	123
111	123
222	456

<tbody>
</tbody>

I would need worksheet 3 to look like this

Child ID	Parent ID	Asset ID
222	456	jkl
222	456	mno
222	456	pqr
111	123	xyz
111	123	abc
111	123	def
111	123	ghi
789	123	xyz
789	123	abc
789	123	def
789	123	ghi

<tbody>
</tbody>

Does anyone have a macro or a formula suggestion for me to use? I've been struggling trying to get INDEX-MATCH to work all day, and am out of ideas. Help would be greatly appreciated!
-David

 

[C Moore]

I may be missing something, but it looks like you could copy sheet1 to a new sheet in column B and then use index-match in column a.

 

[dframe44]

Sheet 3 would be what I would be creating- if I copied sheet 1 into a new worksheet it would only give me 7 rows to index against, and the end product (sheet 3) would need 11 rows.
Just to note, i created sheet 3 manually here based off the information in sheet 1 and sheet 2; in practice, sheet 1 and sheet 2 would have thousands of rows

 

[C Moore]

Yup, missed it. I would start by formatting the two lists as tables, I named them Table_Child and Table_Assets.
Add a column to the Table_Child called Asset Count, formula =COUNTIF(Table_Assets[Parent ID],[@[Parent ID]])
Add another column to the table, Cum Assets, formula =SUM(OFFSET(Table_Child[[#Headers],[Asset Count]],1,0,ROW([@[Asset Count]])-1))
On the new sheet in:
A2 =INDEX(Table_Child[Child ID],IFERROR(MATCH(ROW()-ROW($A$2),Table_Child[Cum Assets],1)+1,1))
B2 =INDEX(Table_Child[Parent ID],MATCH(A2,Table_Child[Child ID],0))
C2 = OFFSET(Table_Assets[[#Headers],[Parent ID]],MATCH(B2,Table_Assets[Parent ID],0)+COUNTIFS($A$2:$A2,A2,$B$2:B2,B2)-1,1)
Copy down until you get errors.

 

[dframe44]

C Moore,

Thats impressive. A colleague & I came to the conclusion that using SQL was more suited to this task; it was a hammer, while Excel was a rock.
Still, I'm going to try to recreate the formula you just gave me when I have some free time.
Thank you so much for your time! It is most appreciated!

-Dave