Hi Excel Guru's,
Looking for some advice on the best way to approach what I'm trying to do... after attempting to google or search the forums I think I'm failing to accurately search/describe what I'm trying to do and have resorted to creating this thread.
1. I'm trying to first reference a parent SKU (a specific cell) and return from a list the total number of fields/child SKU's that are related to that parent SKU
2. I want to then take those child SKU's and reference an additional subset of child SKU's
Things to note;
- The first parent SKU will also be a specific cell and will be a manually entered value
- There could be anywhere from 1 to 100 values related to the parent SKU that are returned as part of the first set of child SKU's
- The second set could also feature anywhere from 1 to 100 values as part of the second set of SKU's
- I am open to any and all suggestions and am not fixed on the particular layout i've identified in my example below
ie:
Data Set 1 (Parent to Child):
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Parent SKU[/TD]
[TD]Child SKU1[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]A2-1[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]A2-2[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]A2-3[/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]B2-1[/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]B2-2[/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD]C1-1[/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD]C1-2[/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD]C1-3[/TD]
[/TR]
</tbody>[/TABLE]
Data Set 2 (Child to Child2):
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Child 1[/TD]
[TD]Child 2[/TD]
[/TR]
[TR]
[TD]A2-1[/TD]
[TD]A3-1[/TD]
[/TR]
[TR]
[TD]A2-1[/TD]
[TD]A3-2[/TD]
[/TR]
[TR]
[TD]A2-3[/TD]
[TD]A3-3[/TD]
[/TR]
[TR]
[TD]A2-3[/TD]
[TD]A3-4[/TD]
[/TR]
[TR]
[TD]A2-3[/TD]
[TD]A3-5[/TD]
[/TR]
</tbody>[/TABLE]
etcetc
Then I would have the results return as...
Parent: "A1" in lets call it Cell H1
Results returned in the H2, H3, H4, H5, etcetc would be:
A2-3
A3-1
A3-2
A2-2
A2-3
A3-3
A3-4
A3-5
I'm so far able to return the first set of child SKU's from the parent row by doing a countif on "A1" to get the total of unique vales to return, and then using the indirect and match function to create the cell reference for the first SKU, and I am using the row function to continue to add one and move down the list until the row() < countif.
Hope that makes sense... appreciate any and all input. Would like to try to keep this in basic excel but will use VBA if necessary.
Thanks all!
Looking for some advice on the best way to approach what I'm trying to do... after attempting to google or search the forums I think I'm failing to accurately search/describe what I'm trying to do and have resorted to creating this thread.
1. I'm trying to first reference a parent SKU (a specific cell) and return from a list the total number of fields/child SKU's that are related to that parent SKU
2. I want to then take those child SKU's and reference an additional subset of child SKU's
Things to note;
- The first parent SKU will also be a specific cell and will be a manually entered value
- There could be anywhere from 1 to 100 values related to the parent SKU that are returned as part of the first set of child SKU's
- The second set could also feature anywhere from 1 to 100 values as part of the second set of SKU's
- I am open to any and all suggestions and am not fixed on the particular layout i've identified in my example below
ie:
Data Set 1 (Parent to Child):
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Parent SKU[/TD]
[TD]Child SKU1[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]A2-1[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]A2-2[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]A2-3[/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]B2-1[/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]B2-2[/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD]C1-1[/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD]C1-2[/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD]C1-3[/TD]
[/TR]
</tbody>[/TABLE]
Data Set 2 (Child to Child2):
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Child 1[/TD]
[TD]Child 2[/TD]
[/TR]
[TR]
[TD]A2-1[/TD]
[TD]A3-1[/TD]
[/TR]
[TR]
[TD]A2-1[/TD]
[TD]A3-2[/TD]
[/TR]
[TR]
[TD]A2-3[/TD]
[TD]A3-3[/TD]
[/TR]
[TR]
[TD]A2-3[/TD]
[TD]A3-4[/TD]
[/TR]
[TR]
[TD]A2-3[/TD]
[TD]A3-5[/TD]
[/TR]
</tbody>[/TABLE]
etcetc
Then I would have the results return as...
Parent: "A1" in lets call it Cell H1
Results returned in the H2, H3, H4, H5, etcetc would be:
A2-3
A3-1
A3-2
A2-2
A2-3
A3-3
A3-4
A3-5
I'm so far able to return the first set of child SKU's from the parent row by doing a countif on "A1" to get the total of unique vales to return, and then using the indirect and match function to create the cell reference for the first SKU, and I am using the row function to continue to add one and move down the list until the row() < countif.
Hope that makes sense... appreciate any and all input. Would like to try to keep this in basic excel but will use VBA if necessary.
Thanks all!