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>
<tbody>
</tbody>
Worksheet 2
<tbody>
</tbody>
I would need worksheet 3 to look like this
<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
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