Hello, all my efforts have failed any ideas would be greatly appreciated.
here's an example:
...A.............B
Parts.........IND
0001...........1
0002...........2
0003...........2
0004...........2
0005...........3
0006...........3
0007...........4
0008...........3
0009...........3
0010...........2
Here's how to interpret this. All parts with an IND's 2 are components found on IND 1. All IND's 3 are found on IND 2....and so on. The trick to this is for example all IND's 3....I only need the last IND 2 that is directly above it.
Here is the result:
...A.............B............C
Parts.........IND........RES
0001...........1...........0001 (IND's of 1 belong to themselves)
0002...........2...........0001
0003...........2...........0001
0004...........2...........0001
0005...........3...........0004 (only need the part # for last listed IND)
0006...........3...........0004
0007...........4...........0006
0008...........3...........0004
0009...........3...........0004
0010...........2...........0001
Each part only belongs to the last listed one above it with a lower IND. I've tried various look up formulas to no avail.
I'm thinking this might call for some kind of offset formula but I cant get it to work.
Be advised this is a small sample. In the list there can be 100's of part number with for example IND 3, so a nested IF statement that I've also tried would not work.
Thanks for any ideas.
here's an example:
...A.............B
Parts.........IND
0001...........1
0002...........2
0003...........2
0004...........2
0005...........3
0006...........3
0007...........4
0008...........3
0009...........3
0010...........2
Here's how to interpret this. All parts with an IND's 2 are components found on IND 1. All IND's 3 are found on IND 2....and so on. The trick to this is for example all IND's 3....I only need the last IND 2 that is directly above it.
Here is the result:
...A.............B............C
Parts.........IND........RES
0001...........1...........0001 (IND's of 1 belong to themselves)
0002...........2...........0001
0003...........2...........0001
0004...........2...........0001
0005...........3...........0004 (only need the part # for last listed IND)
0006...........3...........0004
0007...........4...........0006
0008...........3...........0004
0009...........3...........0004
0010...........2...........0001
Each part only belongs to the last listed one above it with a lower IND. I've tried various look up formulas to no avail.
I'm thinking this might call for some kind of offset formula but I cant get it to work.
Be advised this is a small sample. In the list there can be 100's of part number with for example IND 3, so a nested IF statement that I've also tried would not work.
Thanks for any ideas.