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
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 250"]
<tbody>[TR]
[TD]Parent ID[/TD]
[TD]Asset ID[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]xyz[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]def[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]ghi[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]jkl[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]mno[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]pqr [/TD]
[/TR]
</tbody>[/TABLE]

Worksheet 2

[TABLE="width: 250"]
<tbody>[TR]
[TD]Child ID[/TD]
[TD]Parent ID[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]456[/TD]
[/TR]
</tbody>[/TABLE]


I would need worksheet 3 to look like this
[TABLE="width: 500"]
<tbody>[TR]
[TD]Child ID[/TD]
[TD]Parent ID[/TD]
[TD]Asset ID
[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]456[/TD]
[TD]jkl[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]456[/TD]
[TD]mno[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]456[/TD]
[TD]pqr[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]123[/TD]
[TD]xyz[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]123[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]123[/TD]
[TD]def[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]123[/TD]
[TD]ghi[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]123[/TD]
[TD]xyz[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]123[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]123[/TD]
[TD]def[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]123[/TD]
[TD]ghi
[/TD]
[/TR]
</tbody>[/TABLE]

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